Query Criteria excluding table records

[steve1111]

Hello,

I want to run a query on a table that holds all speed information for our trucks imported from a third party. Some of the speed alerts in that table are not correct so we set up a second table managed by the users to enter a speed exception. So if we know that Main St in Dallas TX generates false alerts for speeding we know not to call the driver, the third party db speed limit is not up to date.

So I want my query to pull all the speed data from tblSpeedData, except leave out the records where the street and zip are listed in the tblSpeedExceptions.

I am still working on learning to write my queries in SQL so hopefully this can be explained using QBE.

Thanks in advance!

 

[plog]

What you want is a LEFT JOIN (http://www.w3schools.com/sql/sql_join_left.asp). Meaning you want all records from one table (tblSpeedData) and only matching records from another (tblSpeedExceptions).

In the QBE you do it like this:

Bring in both tables, link them on the fields you described.
Bring down all the fields from tblSpeedData you want to show in the query.
Right click on both those linking lines and choose 'Join Properties'
Then select the option which shows all from tblSpeedData and just matching from tblSpeedExceptions
Do that for both linking lines.
Bring down the zip field from tblSpeedExceptions
Under that put this criteria: Is Null

Run that and you have your data.

 

[steve1111]

thanks Plog, I think i was over thinking that. Maybe one day i can figure out how to do a delete query and just delete them weekly when i upload the table. this has been a great help for now.

 

[plog]

I advise against deleting data--its so permanent. Easier to set up queries to route around the data you don't want to see. You're using the right method with this.