display week dates between to different dates

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello! I have a database where I track and report locations for equipment
and paperwork received (reports that are completed by employees daily)
weekly. The dates for the paperwork are grouped into week ending dates (I
used the function DateOfSpecificWeekDay = DateAdd("d", -DatePart("w",
OriginalDate, 6, 1) + intWeekDay, OriginalDate) the weeks are Friday -
Thursday). The problem I'm having is the dates for the equipment location do
not change every week. So, when I use the DateOfSpecificWeekDay function, it
only gives the weekending date for that date, not the dates that the equpment
is on a particular location. Here's a sample what my imput for paperwork
looks like:

ID Date Foreman Amount
2 5/8/2006 0151 1
306 4/21/2006 0151 1
356 4/25/2006 0151 1
407 4/28/2006 0151 1
444 4/24/2006 0151 1
483 5/3/2006 7451 1
492 5/5/2006 7451 1
2581 4/26/2006 0151 1

This is how I report it:

Foreman 1/2/2006 1/9/2006 1/16/2006 1/23/2006 1/30/2006
0187 5 5 4 5 5
10108 3 5 5 5 5

The table for the equipment history looks like this:

FleetNumber ReqDate ReqNumber ReqShort Job
AB0301 1/30/2006 1472 Assigned - Issue 1900
AB0301 4/1/2006 6306 Unassigned - Return 1900
AB0301 4/1/2006 6542 Assigned - Issue 1001
AB0302 2/4/2006 1689 Assigned - Issue 99998
AB0302 4/8/2006 6695 Unassigned - Return 99998
AB0307 2/4/2006 1553 Assigned - Issue 9045
AB0307 2/18/2006 2929 Unassigned - Return 9045
AB0307 2/18/2006 2936 Assigned - Issue 9995
AB0307 2/25/2006 2969 Unassigned - Return 9995
AB0307 2/25/2006 2982 Assigned - Issue 9045

All equpment is issued, then returned. I need to find the weekend dates
between the 2. Help! Any ideas? I hope I gave info info... lol
 
Karen, there are several facets to this problem.

1. Get all Fridays
=============
You need to have a table of week-starting dates, with a record for each
Friday. You will then be able to identify all the Fridays between when the
item was booked out and when it was returned. Create a table with just one
field named WeekStartDate, date/time type field. Mark it primary key. Save
the table with the name tblWeekStart. Enter the Fridays for the year.

2. Match the return date to the issue date
===============================
The next issue is to determine the return date associated with each issue
date in your equipment history table. You could use a subquery to do that.
Type something like this into the Field column in query design:
ReturnDate: (SELECT Min(ReqDate)
FROM EquipmentHistory AS Dupe
WHERE (Dupe.Job = EquipmentHistory.Job)
AND (Dupe.ReqDate > EquipmentHistory.ReqDate)
AND (ReqShort = 'Unassigned - Return')
ORDER BY Dupe.ReqDate, Dupe.ID)
If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066
A couple of alternative ways to do this:
Referring to a Field in the Previous Record or Next Record
at:
http://support.microsoft.com/kb/210504/en-us

3. Generate a record for each week
===========================
Once that's working, add tblWeekStart to the query. If Access joins the
tables, delete the join line, as you want no join. Add the WeekStartDate
field to the query grid. In the Criteria row under this field, enter:
Between [ReqDate] And [ReturnDate]
This generates a record for each Friday between the 2 dates.
You will probably find that ReturnDate doesn't work and you have to repeat
the entire subquery expression.

4. The goal
=========
Once you have that working, save the query.
Now build another query that uses that one as a source "table."
In this new query, depress the Total button on the toolbar, so you can group
by WeekStartDate and Foreman, and count the number of records.

To get the layout you want, you would need to turn this new query into a
crosstab (Crosstab on Query menu).
Foreman Row Heading
WeekStartDate Column Heading
CountOfID Value

The query will have to be your report. Since the column headings keep
changing every week, you cannot simply use that query as the source for a
report without another stack of manipulations.
 
I'm sorry, but there were a couple of things I did not mention. The
equipment history doesn't go by actual dates - it goes by the requisition #.
This makes a big difference, so that I actual have to sort by req #, and the
Max(ReqNumber) is the current job that a piece of equipment is on. It's
annoying, but if something needs to be changed in the location history, it
can easily be done; it cancels all other reqs out. So, the sorting by the
date first won't work. The reason I need to figure the location for the week
is, the workers complete a driver report each day for a vehicle. Each week, I
append their employee id, weekend date (thursday), and the job# they were on
for that week to a table. I have a tblJob that has the job #s and the id for
the foreman of that job. I report how many of the driver reports were done
each week for each employee. I have another query that shows each driver
report done for that piece of equipment (sorted by week end), the date
reported, the employee completing, the job # the employee was on, and the
foreman. I want to match up the equipment location to this - I need to be
able to check if a piece of equipment is not getting reported correctly to a
job. I want to be able to compare the job number a unit was being reported
on, and the job# the employee was on durring the dates the driver report was
done. The only problem is, I have to go by the req# first!! Am I making any
sense?! I think I'm confusing myself. Help!

Allen Browne said:
Karen, there are several facets to this problem.

1. Get all Fridays
=============
You need to have a table of week-starting dates, with a record for each
Friday. You will then be able to identify all the Fridays between when the
item was booked out and when it was returned. Create a table with just one
field named WeekStartDate, date/time type field. Mark it primary key. Save
the table with the name tblWeekStart. Enter the Fridays for the year.

2. Match the return date to the issue date
===============================
The next issue is to determine the return date associated with each issue
date in your equipment history table. You could use a subquery to do that.
Type something like this into the Field column in query design:
ReturnDate: (SELECT Min(ReqDate)
FROM EquipmentHistory AS Dupe
WHERE (Dupe.Job = EquipmentHistory.Job)
AND (Dupe.ReqDate > EquipmentHistory.ReqDate)
AND (ReqShort = 'Unassigned - Return')
ORDER BY Dupe.ReqDate, Dupe.ID)
If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066
A couple of alternative ways to do this:
Referring to a Field in the Previous Record or Next Record
at:
http://support.microsoft.com/kb/210504/en-us

3. Generate a record for each week
===========================
Once that's working, add tblWeekStart to the query. If Access joins the
tables, delete the join line, as you want no join. Add the WeekStartDate
field to the query grid. In the Criteria row under this field, enter:
Between [ReqDate] And [ReturnDate]
This generates a record for each Friday between the 2 dates.
You will probably find that ReturnDate doesn't work and you have to repeat
the entire subquery expression.

4. The goal
=========
Once you have that working, save the query.
Now build another query that uses that one as a source "table."
In this new query, depress the Total button on the toolbar, so you can group
by WeekStartDate and Foreman, and count the number of records.

To get the layout you want, you would need to turn this new query into a
crosstab (Crosstab on Query menu).
Foreman Row Heading
WeekStartDate Column Heading
CountOfID Value

The query will have to be your report. Since the column headings keep
changing every week, you cannot simply use that query as the source for a
report without another stack of manipulations.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Karen0927 said:
Hello! I have a database where I track and report locations for equipment
and paperwork received (reports that are completed by employees daily)
weekly. The dates for the paperwork are grouped into week ending dates (I
used the function DateOfSpecificWeekDay = DateAdd("d", -DatePart("w",
OriginalDate, 6, 1) + intWeekDay, OriginalDate) the weeks are Friday -
Thursday). The problem I'm having is the dates for the equipment location
do
not change every week. So, when I use the DateOfSpecificWeekDay function,
it
only gives the weekending date for that date, not the dates that the
equpment
is on a particular location. Here's a sample what my imput for paperwork
looks like:

ID Date Foreman Amount
2 5/8/2006 0151 1
306 4/21/2006 0151 1
356 4/25/2006 0151 1
407 4/28/2006 0151 1
444 4/24/2006 0151 1
483 5/3/2006 7451 1
492 5/5/2006 7451 1
2581 4/26/2006 0151 1

This is how I report it:

Foreman 1/2/2006 1/9/2006 1/16/2006 1/23/2006 1/30/2006
0187 5 5 4 5 5
10108 3 5 5 5 5

The table for the equipment history looks like this:

FleetNumber ReqDate ReqNumber ReqShort Job
AB0301 1/30/2006 1472 Assigned - Issue 1900
AB0301 4/1/2006 6306 Unassigned - Return 1900
AB0301 4/1/2006 6542 Assigned - Issue 1001
AB0302 2/4/2006 1689 Assigned - Issue 99998
AB0302 4/8/2006 6695 Unassigned - Return 99998
AB0307 2/4/2006 1553 Assigned - Issue 9045
AB0307 2/18/2006 2929 Unassigned - Return 9045
AB0307 2/18/2006 2936 Assigned - Issue 9995
AB0307 2/25/2006 2969 Unassigned - Return 9995
AB0307 2/25/2006 2982 Assigned - Issue 9045

All equpment is issued, then returned. I need to find the weekend dates
between the 2. Help! Any ideas? I hope I gave info info... lol
Click to expand...
Click to expand...
 
Back
Top