A conditional Report in Access

[Sabre056]

Hi all,

I'm in a bit of a bind and i'm hoping someone will be able to help me. Right now I have two tables:

Orders
ServiceOrders

They are linked by JobNumber. The 'JobNumber' is unique in the Orders table, but can appear numerous times in the ServiceOrders table (or not at all). In the ServiceOrders table there is a field called 'Completed' which tells if the service has been done. Now what I want to do is have a report that shows any order with a non-complete Service order attached to it.

Example:

Orders Table:
Job# Name State
123 Bob Florida
367 Jane California
372 Joe New York

ServiceOrders Table:
Job# Date Completed
123 9/12/03 Yes
123 3/2/03 Yes
123 5/5/03 No
367 6/1/03 No
374 6/2/03 Yes

So I would want the report to return:

123 Bob Floria
367 Jane California

Any help would be greatly appreciated. I've tried several things, but I keep seeming to fall short.

 

[gwendon]

First you create a query using your two tables.
Then in the field, under completed, in the criteria, type in yes for all completed, or no for all that are not completed.
Then create your report off your query.
This does work on yes/no data types that are set in the table as such.

Also you may run into some problems between your two tables. It is risky business having your second table linked to your first table by Job Number but sometimes you don't enter a job number in the ServiceOrders table.

Don

 

[Sabre056]

That wouldn't work. It's possible that 1 JobNumber might have 2 open service orders.