Criteria in a query

  • Thread starter Thread starter Martin Hopkins
  • Start date Start date
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
 
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.
 
Back
Top