Re: display week dates between to different dates

Tech-Archive recommends: Fix windows errors by optimizing your registry

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.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Karen0927" <Karen0927@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:3C63E2FF-7BF7-42C4-B2E7-B9F356BD08AA@xxxxxxxxxxxxxxxx
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


.

Quantcast