I have one table. In that table are two fields

PMCProducts_PKID
1
2
3
4
5

PMCConnected
4 (on the same row as 2)
5(on the same row as 3)

I want to run a If statement in a query that says something like this.

IIF [PMCProducts_PKID] exists in [PMCConnected], make "Yes"

Now, next to the 4 and 5 record on the PMCProducts_PKID field, must be a "Yes"

If anyone can help with a formula, I will be very happy.

Thank you

Marlie

Not sure if I understand your description correctly but it sounds like you want a Self (Outer) Join Query.

Try a Query with the SQL String:

I tried your SQL, but it is not what I want.

I want to know if a number in the PMCProducts_PKID field appears in the PMCConnected field (anywhere) and then next tot he PMCProducts_PKID I want it to say Yes if it appears.

Is there a findIf formula somewhere?

My table name is tblPMCProducts

Your help is greatly appreciated.

Marlie

There is no FindIf in JET SQL and I am still not sure what you want from your description??? The SQL String I posted should create a "Yes" in the condition you described.

Please post your sample data as Columns for Fields and Rows for Records (with the first row showing Field names) and then post the results you want in the same format if the Query is run on the sample data.

Sorry for only replying now, i don't often have access to the internet.

I have attached a sample in excel for you to have a look at. It is a bit difficult in Utter access's format to show rows and columns.

Please reply..

thanks

Marlie

I can't hardly see the graphics. If you want post the database file AND the Excel file. Compact the database first and then zip the 2 files into one zip archive which must be less than 500 kB. If it is more than 500 kB, find a host site that allow you to upload and then post the link here for us to download.

From what I see of the graphic, I still think my previously-posted SQL Query is correct. What was your actual attempted SQL and the result of the attempt?

My guess is that OP's table is a recursively joined table. Such as:


create table products (

PMCProducts_PKID autonumber primary key,
PMCConnected integer references products( PMCProducts_PKID),
...

);


This table/tree structure allows one to easily determine who is the child and who is the parent. However, finding all of the descendants or ancestors N-levels deep is not possible with a simple select query. Other tree models (i.e. nested set model) can handle this kind of query, however there is more maintenance involved.



Edited by: rabroersma on Mon Feb 5 16:56:42 EST 2007.