Weekly Time Report

[Art Barnwell]

I have an Access 2002 database that stores daily employee time records. The DB will replace a manual system that relies on Excel-based weekly time sheets. I need to be able to produce weekly time reports but I am stumped. The report needs to provide rows of employees on the left-side, daily (Monday thru Sunday) time details for a particular week in the center, and weekly subtotals on the right-side. I need a way to select a particular week, and then return time records for that week, and then find a way to subtotal the daily records.

I am completely stumped, any suggestions will be greatly appreciated.

 

[mdmilner]

You should look at using a dynamically generated crosstab query as the recordsource for a report.

What does that mean?

First, look at crosstabs. Using the wizard is a good idea to start. See if you can manually reproduce what you need right now, being explicit about what timeframe you want to use.

Second, about the times. You have to be careful how you specify times. Putting # characters in front/behind are mandatory.

Third, dynamically. Use VBA behind the form to generate the crosstab query by extracting values from a text-box or combo-box. When creating a combo-box control, you get the option to either extract fields from a table, or to manually type the values. My thought is, what you really want to do is specify options like 'current week', 'prior week' or something like that.

Your VBA needs to use functions like Date() (use VBA Access Help for options) to determine today, then figure out what 'current week' and 'prior week' means. Once it generates a range, you create a SQL query (the crosstab) and have it insert the values.

For example, this is a sample production crosstab query that I use.
The Crosstab is based off a Query that filters by date

{crosstab}
TRANSFORM Count(qryCount.[Car Number]) AS [CountOfCar Number]
SELECT qryCount.Yard, qryCount.LineOfRoad, Count(qryCount.[Car Number]) AS [Total Of Car Number]
FROM qryCount
GROUP BY qryCount.Yard, qryCount.LineOfRoad
PIVOT qryCount.Report;

{qryCount}
SELECT * FROM tblImpactsAll
WHERE (((tblImpactsAll.[Event Date])>#5/1/2003#));

When creating the date filtering query, what you could do is something like.

strSQL = "SELECT * FROM tblname WHERE [Event Date]> " & dteMyDate
-
And the last part. "As a recordsource for a report". Really, you have two options when telling a report what to use as it's recordsource. You can reference an existing table/query - or you can specify a SQL query as the recordsource. The former is a little easier, while the latter requires you to edit the Reports' recordsource property. Not hard, but a tiny bit more complex.

I'd recommend as a start, that create querydef's (allows you to create Queries that show up under the queries tab just like if you'd made them manually instead of from vba using...) You'd then specify that query as the default recordsource for the report (rather than attempting to modify the recordsource property) From there, for completeness, make sure the button that you hit that opens all this up opens the Report or does whatever it will need to do with it (email/save)

Dim dbs As Database
Dim qdf As QueryDef, strSQL As String

strSQL = "PROCEDURE Split; " _
     &  "SELECT * FROM tblname WHERE [Event Date]> " & dteMyDate

Set qdf = dbs.CreateQueryDef(qryname, strSQL)

This is a hodgepodge of several different types of tasks to create via code. As I mentioned, it's not necessarily the absolute best way to do this, but it would work and I tried to throw up things that were easier for me to learn to do.

Hope this points you in the right direction, or at least, gives you an idea where to go to figure out how you will end up doing it.

Mike