Query Subquery?

[ebbo]

Hello,

Please see the attached image.

there are 3 tables (simplified below for those that cant see image).

Company (id{PK}, name, address, etc)
Web (company_account{fK},module1, module2, module3)
CD (company_account{fK},module1, module2, module3)

I need a query that displays the following

name, address, take Web?, take CD?

example results
mcdonalds, mcdonald way, YES, NO

The web and cd columns are the ones I cant figure out. I just need the query to return a "TRUE" if the company takes the Web product and a "TRUE" if they take the CD (its also possible the take both). If the company is listed in the Web or CD tables then you can take that as they subscribe to the service otherwise they do not and its a "FALSE".

What do i need to enter in the Expr1 and Expr2 columns in order to find out if the company is taking either of those products?

 

[plog]

First you need to change the links between to include all records from Companies and only matching records in the other tables. Do this for both links you have extablished by right-clicking on the links, then clicking 'Join Properties' and selecting the appropriate option in the dialog that appears.

After that add these two fields to your query:

TakeWeb: IIF(isnull([PCTax Web].[Company_account]), "No", "Yes")

TakeCD: IIF(isnull([PCTax CD].[Company_account]), "No", "Yes")


I couldn't see what the actual field name was that you were linking to, so if its not called 'Company_account' change it to the right name.

 

[ebbo]

thanks plog this worked perfectly, very much appreciate your help