Query help

[DanWallace]

Hi, I'm a little new at this stuff, so pardon me if this is a dumb question.

I have two tables, table1 and table2. Table1 has a key field ACCOUNT. Table2 has an ACCOUNT field and also an ARCODE field. So one record in Table1 could have many records in Table2. What I'm trying to do is write a SELECT statement that will select all records in Table1 that DO NOT have a matching record in Table2 with an arcode of 'WA'. Can anyone give me an idea of what this would look like? Do I need to use COUNT?

 

[Rabbie]

Just use the "Find unmatched Query Wizard" in the New tab of the Query tab.

 

[DanWallace]

Just use the "Find unmatched Query Wizard" in the New tab of the Query tab.

That's what I tried, except they can still have matches in Table2. I just want to find the ones with no "WA" matches in Table2.ARCODE.

For example, Table1 has one record where ACCOUNT is 15. Table2 could have 3 different records for ACCOUNT 15. One where ARCODE is DA, one where ARCODE is TT, etc. I just want to get all the records in Table1 that don't have a "WA" record in Table2.

 

[Rabbie]

Then I would do it in two stages

1. Write a query that selects only the "WA" records from Table2
2. Then use the "Find unmatched Query" for Table 1 and the new Query.

 

[DanWallace]

Then I would do it in two stages

1. Write a query that selects only the "WA" records from Table2
2. Then use the "Find unmatched Query" for Table 1 and the new Query.

Ahhh that'll do perfectly. I just couldn't wrap my mind around it this morning. haha... thanks!

 

[Rabbie]

Glad to have been of help

 

[MSAccessRookie]

That's what I tried, except they can still have matches in Table2. I just want to find the ones with no "WA" matches in Table2.ARCODE.

For example, Table1 has one record where ACCOUNT is 15. Table2 could have 3 different records for ACCOUNT 15. One where ARCODE is DA, one where ARCODE is TT, etc. I just want to get all the records in Table1 that don't have a "WA" record in Table2.

Using a LEFT JOIN with a SubQuery is one option.

[/FONT]
[FONT=Courier New]Select Table1.Account [/FONT]
[FONT=Courier New]From Table1 LEFT JOIN Table2[/FONT]
[FONT=Courier New]Where Table2.ARCODE Is Null Or[/FONT]
[FONT=Courier New][FONT=Courier New]      Table2.Account Not In[/FONT]
[FONT=Courier New]      (Select Distinct(Account) [/FONT]
[FONT=Courier New]       From Table2[/FONT]
       Where Table2.ARCODE = "WA") As A;
{/code]
[FONT=Courier New][/FONT] 
[/FONT]Note that this code is "Air Code" and as such has not been tested, but it should return any Records from Table1 that have no matching Record in Table2 at all, as well as any Records from Table1 that have no matching Record in Table2 that contains "WA".

 

[MSAccessRookie]

Rabbie's solution is much simpler than mine. The one thing that I am not sure of is whether any records in Table1 that have no matching records in table2 will be included as well.

 

[DanWallace]

Rabbie's solution is much simpler than mine. The one thing that I am not sure of is whether any records in Table1 that have no matching records in table2 will be included as well.

Thanks.

Rabbie's solution seems to work perfectly. I was really just after the final number to put into a spreadsheet and it seems to match up with what I was hoping for.

Thanks for the help.