Ernie Sersen said:
I created a table to enter milestone dates achieved for my project that
has
lasted about one year. There are 3 columns: Design_complete,
Assembly_complete and Test_Complete. For metrics, I need to create an
access report by week that tells how many of the above dates occured in
each
week. The row headings will contain week ending dates (Week-Ending is
Saturday; Week-Beginning is Sunday). The 3 column headings will have the
above date descriptions. The report will summarize the number of dates
that
occurred for each week-ending period. I also need to be able to
chart/graph the above results. Can someone advise as to the best way
to
setup this report? THANKS!
Hi Ernie,
I have many reports that require
"enumerating weeks over the data."
There may be other (and maybe better) ways,
but most of those reports are based on a query
in the "form" as below:
SELECT
tblCourses.Term,
Format([ReportReceivedDate],'ww') AS TermWeek,
Sum(Abs([Progress]>1)) AS TermWeekProgressGT1,
Min(tblCourses.ReportReceivedDate) AS MinDateRcvd,
DateValue(MinDateRcvd)-(Weekday(MinDateRcvd)-1) AS SundayOfWeek,
DateValue(MinDateRcvd)-(Weekday(MinDateRcvd)-7) AS SaturdayOfWeek
FROM tblCourses
WHERE (((tblCourses.ReportReceivedDate) Is Not Null))
GROUP BY tblCourses.Term, Format([ReportReceivedDate],'ww')
So... it starts out grouping by the week number.
In each group, there may not necessarily be a date
for the Sunday/Saturday of that week.
But if I get a single date within that week ("MinDateRcvd"),
I can reuse it to find the Sunday and Saturday of that week.
In the above, I am then summing the number of reports
within those weeks whose [Progress]>1.
In your case, I believe you are going to need to first use
a Union query to make your data "thin."
qryunMilestones:
SELECT "Design" As Milestone, Design_complete As CompDate
FROM yourtable
UNION ALL
SELECT "Assembly", Assembly_complete
FROM yourtable
UNION ALL
SELECT "Test", Test_complete
FROM yourtable;
(in the selects of the union query, I did not add where
clauses to weed out Null dates. Maybe counts of those
may be important later. Maybe you cannot even have a record
if Design_complete date is null?)
Anyway, your report query would be based on "qryunMilestones"
similar to the following:
SELECT
Format([CompDate],'ww') AS CompWeek,
Sum(Abs([Milestone]='Design')) AS DesignCnt,
Sum(Abs([Milestone]='Assembly')) AS AssemblyCnt,
Sum(Abs([Milestone]='Test')) AS TestCnt,
Min(CompDate) AS MinDateRcvd,
DateValue(MinDateRcvd)-(Weekday(MinDateRcvd)-1) AS SundayOfWeek,
DateValue(MinDateRcvd)-(Weekday(MinDateRcvd)-7) AS SaturdayOfWeek
FROM qryunMilestones
WHERE CompDate IS NOT NULL
GROUP BY Format([CompDate],'ww');
In my reports, I usually show the complete interval for
the week, i.e., something like a textbox with source of:
=[SundayOfWeek] & " - " [SaturdayOfWeek]
That may be one way.....
As for graphing the results, I do that so little,
I usually start by importing a graph report from another db,
then start making changes to it, like recordsource, etc.
I think I started with
ACC97: Microsoft Access 97 Sample Graphs Available in
Download Center
http://support.microsoft.com/default.aspx?scid=KB;en-us;q186855
Do a search of your harddrive for
graph*.chm
it can be a big help.
Some recommend starting with your graph on
a form first, then copy and paste into a report...
others suggest work in Excel first,
recording macro as you work, then go back and
look at macro code.
Others suggest simply going into report design,
choosing Insert/Chart and going from there.
Graph implementation definitely was not the
Access-designers finest moment (but what they
have done otherwise makes up for it, IMHO).
Maybe its just that other things can work so well,
the graph part stands out as "flakey."
good luck,
gary
