Find data present, return yes/no, not DATA VALUE

[natalie99]

Hey people!

Being so lacking in access knowhow I have turned to good old 'asking the smart people' again!

I need to create a query or lookup table (although I have had zero success with lookups for this particular problem) that will do the following:

1. From the main data source (query result called qryUnmatchID) compare the ID field to the ID field in 8 other data sources

then

2. Return a yes or no into 8 columns named after the 8 data sources depending on whether or not the ID is present in that table, along side the ID that has been looked up

then

3. For each possible factorial option (i.e. every combination of yes' and no's, which for 8 options is 255 send the ID, and all it's relevant qry data to another table.

(sum(8_c_1 to 8_c_8) = 255)

E.G.

ID - "yes" "no" "yes" "no" goes to Table 1

ID..............source 1........source 2.......source 3.......source 4
8745.............yes................no............ ...yes.................no
64GG8...........yes................no............. ..yes.................no



where as

ID - "yes" "yes" "no" "no" goes to Table 2

ID..............source 1........source 2.......source 3.......source 4
811.............yes................yes............ ...no.................no
000F-1........yes................yes...............no.. ...............no




I have created a table detailing which yes/no combinations go to which reports, called tblCategorialActions. The options are referenced by an autonumber ID.

Now that I have done that I have no idea how to start the look up to return a yes or no for present IDs, and not to discount IDs when they don't match, as with an internal join.

Can I join the 8 data sources externally to the ID in the qry source qryUnmatchID? How do I then return a yes or no instead of the values? I had thought about populating null fields with "NO" but I still don't have the "YES" and I need to label each column to it's data source as all fields have the same name, imaginatively called ID.

Please help!

I don't know where to start!

:D

Nat