Omission Query

[bibbyd01]

Hi all

I'm a little lost on where I even begin on this one.

I have list of vehicles that need to get checked every week, which gets added to a database. The table the vehicles gets added to is called TblCheck. This has the reg, week, year and a couple of yes/no fields called check and mileage. All of the vehicles belong to a depot, which identifies who is responsible to enter the data. The depot is identified by looking up against TblDriver.

I need a report that lists all of the vehicles that haven't had an entry on a specific week. I can build a query that lists all active vehicles by depot, but I don't know how to build a query to list all vehicles without an entry without entering all of the info into the TblCheck Table for every single week for the next 5 years.

 

[DCrake]

You need to build a unmatched query via the wizard using the specfied criteria explained earlier. This will show you all vehicles without checks. However this is to many as it covers all dates. What you need to do then is to place a date range or a week number in the condtion line stating Is Null.

David

 

[bibbyd01]

Ok, the omission bit works fine, but I don't know how to go about the second bit?

I've added the week and year fields, but now I'm a little stuck!

 

[DCrake]

Put a number in the week column to first filter the weeks and view the results. Do the same for the Year. You should now see records in the child table that have null values. Under the column that has the null values enter Is Null.

If you are still having issues if you can send a copy of the mdb (pre 2007) I will take a look at it.

David

 

[bibbyd01]

I stillcan't seem to get this to work. I've created a basic database with the same general fields.

 

[DCrake]

I get the idea of what you want. Does this offer a solution for you

 

[bibbyd01]

I think this is almost what I need, but the end game is to transfer this to a report, so not sure how that would work? Also, how could I search for a particular week?