create a query for form to display certain records

[jprochelle]

I have two tables: In House and Commercial. In both tables there is a CUSIP field. I want the form to only display those CUSIPs in the "In house" table that have a certain criteria for a field in the "In house" table (i.e. only show those that have "yes" in the COOP field). At the same time, I want the form to populate with the details from the "Commercial" table. So, one entry would be the CUSIP from the In house table, and the corresponding 'Description', 'ID', 'Location' from the "Commercial" table.

Is the only way to set this up with unique relationships? This is a problem because there are duplicate values. Is there any good way to query/append query this information into the form..

Thanks,

 

[PHV]

Something like this ?
SELECT DISTINCT I.CUSIP, C.Description, C.ID, C.Location
FROM [In house] I INNER JOIN Commercial C ON I.CUSIP=C.CUSIP
WHERE C.COOP="yes"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[jprochelle]

Yea, this didn't work. Is there any way you could tell me how to join in design view. Because doing it in SQL is giving me errors.

Thanks,

 

[PHV]

Which errors ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[jprochelle]

This is what I have:
SELECT DISTINCT [In house].CUSIP, [In house].[Idssg code], [In house].[Street Address], [Commercial Loans].Description
FROM [In house], [Idssg Code Query], [Commercial Loans]
WHERE ((([In house].[Idssg code])="COOP"));


when i perform the query, it lists the CUSIPs from In house several times...it is listing it with each possible description instead of matching it up with the descritption that is in the commercial loans table.

 

[PHV]

You may try this:
SELECT DISTINCT [In house].CUSIP, [In house].[Idssg code], [In house].[Street Address], [Commercial Loans].Description
FROM [In house] INNER JOIN [Commercial Loans] ON [In house].CUSIP = [Commercial Loans].CUSIP
WHERE ((([In house].[Idssg code])="COOP"));


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244