Criteria in a query

M

Martin Hopkins

Could anyone help on the following query:

I have a query based on 2 tables joined by ID numbers, where the Id number
relates to Staff Number.

The query is:

Staff ID, Mon, Tues, Wed, Thurs, Fri,


Where the values in Mon - Fri could be Null, Cashier, Stock or Hols.

I want to use the query to return all staff who are on Cashier or Stock from
Mon to Fri.

If I leave criteria blank then I get all staff. The underlying query would
be used in a Staff report to show only Staff on Cashier or Stcok duty.

I have tried In ("Cashier","Stock") in several combinations in fields Mon -
Fri, to no avail. If I put the criteria under Mon, then I get those staff on
Mon but not those on Tues etc.

There is probadly a very simple solution, but my mind is blank.

Any help would be grateful.


Martin Hopkins
 
D

Duane Hookom

Is there any way that you could normalize your table structure? You have set
up your tables to match what might be viewed on a piece of paper and not how
it should be organized in a relational database.

A better table structure might be
tblStaffAssignments
======================
StaffID
AssignDate
Assignment
You could easily query by assigment and date range.
 
Top