Access qurery using fields from separate tables

[keith_shoaf]

Hello,
I am trying to build a query in Access 2010 that will return all AVAILABLE ID's. What I have so far: Two tables (tblTID and tbUsers). tblTID contains 2 fields: ID (primary key, auto assigned, 1 through 2285) and TID (Terminal ID values, DODPBAS01). tblUsers contains 27 fields including STATUS, Name, TID, and various admin fields. I need a query that will list the unused TIDs from tblTID based on the STATUS field on tblUsers.

For reference:

tblTID

| ID | Terminal ID |
| 1 | DODP8671 |
| 2 | DODP5879 |
| 3 | DODP4857 |
|and so on..... 2285 total available Terminal IDs

tblUsers

| STATUS | Name | ID | TID |
| ACTIVE | Doe, John | ABCD | DODP5879 |
| DELETED | Doe, Jane | EFGH | DODP4857 |
and let's say there are 25 users listed

I need a list that will show me all Terminal IDs from the first table that do not have an ACTIVE status on the second table.

Thank you in advance for any assistance!!

 

[Joe4]

Here is an easy two-step way:

1. Create a query of your Active tblUsers. Simple do this by creating a new query based on the tblUsers table, and place the word "ACTIVE" under the Criteria row under the STATUS field (in the Query Builder).

2. There is a Query Wizard for "Find Unmatched Query Wizard". Follow the Wizard steps there, looking all records in the tblTID table that are not found in the Query you created above. It will walk you through each step.

 

[keith_shoaf]

Thank you Joe4!! Another quick question....

Is there a way to add a calculated field to the first table using IIF statement to return true if STATUS of second table = ACTIVE?

tblTID

| ID | Terminal ID | USED |
| 1 | DODP8671 | Y | -this result is based off the STATUS field of the other table
| 2 | DODP5879 | N |
| 3 | DODP4857 | N |

Thanks again for the prompt resolution to my first problem.

 

[Joe4]

All calculations should be done at the Query level (not at the Table level).

Newer versions of Access do allow table-level calculations, but they are limited (don't believe you can look at other tables) and they are generally a bad idea. Other more robust database programs do not allow it, as it generally violates the basic rules of database normalization.

So do your calculations on the query level. There really isn't any reason to try to do it at the table level versus the query level anyway (no real advantage to it).

 

[keith_shoaf]

Thanks Joe4!! Really appreciate your help and explanations!

 

[Joe4]

You are welcome. Glad to help!