Extract data for a date plus tomorrows date

[Rick]

In my table named tblSchedule I have the fields EID
(Employee ID), Date and Assnmt (shift to be worked). A new
aggreement between management and labor dictates that 8
hrs must be provided between shifts. Each employee has one
record for each day, even if off, on vacation, etc. The
query I created to find people working late shift is
simple; Name, Date, Assnmt (criteria "2C" (our late
shift)). All people working "2C" are returned with proper
date. Now I need to display the next days schedule for
those returned records. i.e. If employee1 is
working "2C" on 9/5/04 I need his Assnmt for 9/6/04 to
display also. Sounds simple but we have over 200 employees
to track. Any help is appreciated, Thanks - RP

 

[Nikos Yannacopoulos]

Rick,

Here's atrick to do it:

Make a query on the table, including EID, Assnmt and a calculated field
like:
NextDay: [Date] + 1

Save this query as, say, qryNextDay

Then make a new query on the table and query qryNextDay, joining field Date
from the table on field NextDay from the query, and EID from both. Following
that, the only thing left to do is apply criteria (to pick the night shift,
specific date etc).

Note: Having a field called Date is not a good idea, Access uses the same
word for a built-in function that returns the current date, and this might
give you problems. I suggest you change the field name in your table.

HTH,
Nikos

 

[Rick]

Nikos,

Thank you for the quick reply. The problem arises from the
fact that the schedule runs 200+ people for up to 21 days
in advance (over 4,000 records in table). A person may
work "2C" on just one day, let's say 9/5/04. The query
pulls that just fine. The next day he works 2A (Building 2
at 5am). The first query you suggested will show the "2C"
for the next day; in other words it displays "2C" 9/6/04.
This is not correct since on 9/6/04 he works "2A". This is
how I'd like it to display:
EID Date Assnmt NDay Assnmt

Joe 9/5/04 2C 9/6/04 2A
Rick 9/12/04 2C 9/13/04 3A
Kim 9/14/04 2C 9/15/04 2A
etc....

The next day has to tied to the [Date] and extract the
correct Assnmt.

Sounded simple but......

Thanks again, Rick

-----Original Message-----
Rick,

Here's atrick to do it:

Make a query on the table, including EID, Assnmt and a calculated field
like:
NextDay: [Date] + 1

Save this query as, say, qryNextDay

Then make a new query on the table and query qryNextDay, joining field Date
from the table on field NextDay from the query, and EID from both. Following
that, the only thing left to do is apply criteria (to pick the night shift,
specific date etc).

Note: Having a field called Date is not a good idea, Access uses the same
word for a built-in function that returns the current date, and this might
give you problems. I suggest you change the field name in your table.

HTH,
Nikos

In my table named tblSchedule I have the fields EID
(Employee ID), Date and Assnmt (shift to be worked). A new
aggreement between management and labor dictates that 8
hrs must be provided between shifts. Each employee has one
record for each day, even if off, on vacation, etc. The
query I created to find people working late shift is
simple; Name, Date, Assnmt (criteria "2C" (our late
shift)). All people working "2C" are returned with proper
date. Now I need to display the next days schedule for
those returned records. i.e. If employee1 is
working "2C" on 9/5/04 I need his Assnmt for 9/6/04 to
display also. Sounds simple but we have over 200 employees
to track. Any help is appreciated, Thanks - RP

.

 

[John Spencer (MVP)]

Here is an SQL statement that might work for you.

SELECT T.EID, T.[Date], T.Assnmt, N.[Date], N.Assnmt
FROM YourTable as T LEFT JOIN YourTable as N
ON T.EID = N.EID AND T.[Date] = (N.[Date] - 1)
WHERE T.Assnmt = "2C"

You can't do this completely in the query grid, but you can build most of it
with the query grid and then switch to the SQL view to modify the join clause.

Open a new query, include your table twice (Access will name the second instance
with an "_1" at the end.

Join the two tables on the EID and Date field. (By the way, it would be a good
idea to rename that field to WorkDate or something else. Date is a reserved
word in Access and using it as a field name can cause unexpected behavior).

Switch to design view and change the join expression to resemble the one above.

Nikos,

Thank you for the quick reply. The problem arises from the
fact that the schedule runs 200+ people for up to 21 days
in advance (over 4,000 records in table). A person may
work "2C" on just one day, let's say 9/5/04. The query
pulls that just fine. The next day he works 2A (Building 2
at 5am). The first query you suggested will show the "2C"
for the next day; in other words it displays "2C" 9/6/04.
This is not correct since on 9/6/04 he works "2A". This is
how I'd like it to display:
EID Date Assnmt NDay Assnmt

Joe 9/5/04 2C 9/6/04 2A
Rick 9/12/04 2C 9/13/04 3A
Kim 9/14/04 2C 9/15/04 2A
etc....

The next day has to tied to the [Date] and extract the
correct Assnmt.

Sounded simple but......

Thanks again, Rick

-----Original Message-----
Rick,

Here's atrick to do it:

Make a query on the table, including EID, Assnmt and a calculated field
like:
NextDay: [Date] + 1

Save this query as, say, qryNextDay

Then make a new query on the table and query qryNextDay, joining field Date
from the table on field NextDay from the query, and EID from both. Following
that, the only thing left to do is apply criteria (to pick the night shift,
specific date etc).

Note: Having a field called Date is not a good idea, Access uses the same
word for a built-in function that returns the current date, and this might
give you problems. I suggest you change the field name in your table.

HTH,
Nikos

In my table named tblSchedule I have the fields EID
(Employee ID), Date and Assnmt (shift to be worked). A new
aggreement between management and labor dictates that 8
hrs must be provided between shifts. Each employee has one
record for each day, even if off, on vacation, etc. The
query I created to find people working late shift is
simple; Name, Date, Assnmt (criteria "2C" (our late
shift)). All people working "2C" are returned with proper
date. Now I need to display the next days schedule for
those returned records. i.e. If employee1 is
working "2C" on 9/5/04 I need his Assnmt for 9/6/04 to
display also. Sounds simple but we have over 200 employees
to track. Any help is appreciated, Thanks - RP

.