extracting certain shift data from Query

[Mark]

Hello all,

I want to run queries for a particular shift over a variable data range.

Select * from MyTable
Where MyDate between 07/01/06 and 07/08/06
A shift time is on Monday, Tuesday, Wednesday from 6:00 AM to 600 PM
B shift time is on Thursday, Friday, Saturday from 6:00 AM to 600 PM
etc..
The shifts work 3 days a week. So if the data range was over 2 weeks then I
would need to run the query 6 times to get all my data. Is there a way to
get this with either looping, or some function that access has? What would
be the best way to approach this?
One way I can think of is creating a table (below) with the Shift, start
time, end time, and day. Then loop thru it until I get all the data range.
Help me with the best approach.
Examples:
VCnt = 0
Do
VCnt = Vcnt + 1
VSql = “My SQL statement with the variable between statementâ€
docmd.runSql(vsql)
Loop while vCnt <= vCnt1
MyShiftTable
Shift StartTime EndTime Day
A 06:00:00 17:59:00 Monday
A 06:00:00 17:59:00 Tuesday
A 06:00:00 17:59:00 Wednesday
B 06:00:00 17:59:00 Thursday
B 06:00:00 17:59:00 Friday
B 06:00:00 17:59:00 Saturday
C 18:00:00 05:99:00 Monday
Etc..

 

[SteveS]

Hi Mark,

If I understand, you have 4 shifts per week, with Sundays off.

(Days)
Shift A (always) works 6A - 6P Mon, Tue, Wed
Shift B (always) works 6A - 6P Thu, Fri, Sat

(Nights)
Shift C (always) works 6P - 6A Mon, Tue, Wed
Shift D (always) works 6P - 6A Thu, Fri, Sat

The shifts work 3 days a week. So if the data range was over 2 weeks then I
would need to run the query 6 times to get all my data.

I don't understand this.... could you give an example of the expected results?

If you have a field that stores the shift letter and the work date, couldn't
you have queries like

to get A shift over two weeks:
Select * from MyTable Where WkShift = "A" And MyDate between 07/01/06 and
07/14/06;

to get day shifts over two weeks:
Select * from MyTable Where (WkShift = "A" Or WkShift = "B") And MyDate
between 07/01/06 and 07/14/06;

to get only B shift on Mon over two weeks:
Select field 1, field2, ...,WeekdayName(MyDate) as WkDay From MyTable Where
WkShift = "B" And WkDay = "Monday" And MyDate between 07/01/06 and 07/14/06;

If the query is for a report, you can use Sorting & Grouping to group by
shift or by MyDate/Shift, etc.

Note: The name of the day (Mon,...) doesn't need to be stored because it can
be calculated using the WeekdayName() function.
And if A shift *always* works 6A - 6P, there is no need to store
the work start & end time.

HTH