Select query across two tables, one with many rows.

[Scotbot]

Hi and thanks for looking at my question.

I'm using Access 2010 and have question about queries, apologies if it's really basic! I manage an SQL server and have a reasonable knowledge of Access, I can make tables, create select/update/delete queries and create linked tables using SQL data. I have a basic knowledge of SQL select statements etc.

I have two tables, linked by a field called 'intcallageid' which is the primary key in the first table. In the second table there are many records with the same value for 'intcallageid'.

I have a query that selects data from the first table and this runs fine. I would like to add a criteria so that the query returns only the rows from the first table where there is at least one entry for 'intcallageid' in the second table. However it should not return any data from the second table, just check to see if entries exist in there for the 'intcallageid' value.

When I make the second table visible but don't add any of its fields to the query I get one row returned for every entry in the second table.

What's the best way to get one row for every entry in the first table IF there is at least one entry for the 'intcallageid' in the second table? can it be done in the query design view, or does it require SQL?

Thanks for your help!

 

[jzwp22]

If I understand your question, if you do an inner join between the two tables, the query will not return any records that don't have at least one related record in the second table. So in SQL view (just so I can represent it here), the query would look something like this:

SELECT table1.field1, table1.field2 etc
FROM table1 INNER JOIN table 2 ON table1.intcallageid=table2.intcallageid

You can do the above in Design view by adding both tables and creating the relationship (if it has not already been done in the relationship window).


Now if you only want to show 1 record only, you will need show only unique records. You can accomplish this by switching from the design view to SQL view and adding the word DISTINCT as shown below:

SELECT DISTINCT table1.field1, table1.field2 etc
FROM table1 INNER JOIN table 2 ON table1.intcallageid=table2.intcallageid

You can also do this in Design view by right clicking in the upper part (where the tables are displayed) of the design view and going to the query property sheet and change the unique values property to Yes. There is also a unique records property (DISTINCTROW) which might also work in your case.

 

[Scotbot]

Thanks for your help, I've done it in the Properties window and was about to post when I saw your reply so good to know that's the right solution, I wasn't exactly sure what it was doing to reduce the number of records returned.

It's very useful to know about the DISTINCT SQL command, I wasn't aware of that and it's going to be very useful.

Thanks!

 

[jzwp22]

You're welcome. Glad you were able to sort it out.