Report Design Questions (calling all cars!)

[benallen002]

Hi all. Being such a big fan of this board, I thought I would come to you guys for help on designing a report and maybe implementing it as well. Here is what I am trying to do:

I have an employee time tracking database setup that tracks each employee's time at an atomic level. Each employee has 3 different sets of hours recorded, ST (straight time) OT (overtime) and DT (doube-time), and these hours are assigned a date. These hours get distributed over any number of job tasks and the tasks get rolled up into job numbers, which in turn get rolled up into project numbers. Now, the database works great and all my tables and querys are structured well (I think, i'm still new to Access) and I can do the report based on a DAILY format; the problem comes when i want to print a WEEKLY time report that includes all the ST, OT, and DT hours per employee line at the Detail level. What I need to have for each line (employee) in the Detail section are the hours from each of the seven days of the week that the report is for, and have them properly split among the three sets of hours for each employee for each day.

Basically, the way it looks like now is each employee has 3 rows (ST, OT, DT) and 7 columns (days of the week). I currently do this in Excel, and while it is easy to do this using VLOOKUPs and other formulas, overall Excel is horrible at what I'm trying to accompllsh.

So, any help/suggestions you guys might have on how to structure this report and its underlying queries would be much appreciated. Right now it is looking like I will have run a sub-report for each day of the week...but then how can I feed it the date of each day based on the weekending date the report is for?

 

[dsdavids]

What you need is a crosstab query, the problem with that is to turn it into a reusable report your really have to get into some vba coding. I don't know how familiar you are with the process of coding but you'll have to create and name your columns at runtime. I've done something similar and it was much more complicated than I would have thought. I've since moved on and never had to mess with it so I'd really need to look back to refresh my memory in order to be of any help to you.
One suggetion, for years before I tackled it in access, I used to export the query including all the individual records to a template in Excel. A pivot table is fairly easy to manipulate and get the results you want. This worked quite well, the only drawback being that I had to handle the process personally. Now that I worked it out all in Access, anyone can create the report by selecting a period and pushing a button.

 

[benallen002]

Thanks for the information Davids. I'll have to do some studying on crosstab queries, as I have never worked with them before. VBA code, on the other hand, I am very comfortable with (at least from an Excel standpoint, haven't worked with VBA in Access very much, but I'm familiar with the overall syntax and structure of the language).

Anyways, I'd love to hear more about how you pulled it off if you have time to share. As far as the Excel stuff goes, I'm trying to do exactly what you did: make it easy for someone who DOESN'T know Office programs very well to use, and make the information more manipulatable.

Thanks again for you time and help.

 

[SydneyGeek]

Ben, if you use Access XP or higher, you may find that a pivot table in Access will do what you need.

See how you go with building a PivotForm via the Wizard (in Forms, New and select the PivotForm option). If you're any good with pivot tables in Excel you will find the process is pretty similar.

Denis

 

[benallen002]

Well gang, I'm halfway there. I used dsdavids advice about crosstab queries and created one for each set of hours (ST, OT, & DT). I then made a union query of all three of those queries and viola!, I have all the hours of the employees in corresponding columns (fields) of the days of the week. Which makes creating a report off of this query a breeze.

This works great when I have time to hard code the dates of the week into the queries.

Now all I have to do is figure out the code required to name the date fields used in the queries at runtime so that it is a dynamic report...any help?

SydneyGeek, I took your suggestion and ran the query as a PivotTable and it did come out about what I'm looking for, but for some reason I have never achieved a familiarity with PivotTable (even with my extensive work in Excel) that allows me to get them to look the way I want it to; while still giving me the data I'm after. I appreciate the advice though, and I'll probably keep messing with the PivotTable until I can figure out this coding issue. Thanks for the help.

 

[dsdavids]

Since you are familiar with coding, it shouldn't be that difficult. You'll want to create an event procedure on the Open event of the report. In that procedure, loop through the fields in your crosstab report (I assume the fields are named by the date) and set the control source of the appropriate text box by relating the correct date to the correct column.
My procedure looks like this:
-----------------------------------------------------------------
Set db = CurrentDb
Set rst = db.OpenRecordset("qryDailyHours_Crosstab")

'Convert Dates to Days, set control source of each column
For Each fld In rst.Fields
varFldName = fld.Name
If IsDate(varFldName) Then
intDay = WeekDay(varFldName, vbSaturday)
Set txt = Me.Controls("txtHours" & intDay)
txt.ControlSource = varFldName
End If
Next fld
Set fld = Nothing
Set txt = Nothing

---------------------------------------------------------

This is using DAO, so if you haven't already, you would need to set that reference.
Also note that I named each text box "txtHours1", "txtHours2" etc. so I could easily relate the proper field to the proper textbox in the loop. This will need some more work if you are including more than one week or if any days may have no records.

Dean

 

[benallen002]

Dean,

I'm posting this reply before I get a chance to punch in the code you gave me, so I haven't fooled around with it yet. One thing I can tell you that I just thought of a little while ago is that it is almost a certainty that at some point there will be some days that have no records at all (weekends, for example, are sometimes, but not always, worked). I'm just going to take the code you gave me and start fooling around with it until I get something that works. One thing I will do when I finally get this thing working is post both the VBA code used on the report as well as the SQL statements used to generate the queries, so that anyone interested can follow our footsteps and not re-invent the wheel. Anyways, I appreciate the help immensely and hopefully I can figure it out from here.

Oh, but before I get started...what exactly was DAO again? and how do I set the reference?

Thanks

Ben

 

[dsdavids]

DAO was the reference for Access97 objects. If your are more familiar with ADO, the default for Access2000 and later you can do exactly the same thing. The syntax and code is different. You can still use DAO in later versions but you have to set the reference manually (only the first time):

Open a module in code view, go to Toos:References from the menu bar and scroll down to Microsoft DAO 3.6 Object Library or whatever the latest version you have. Select it and check to include in your references.

Then whenever you create an object variable for a recordset or database, you'll want to explicitly declare it as DAO or ADO in order to avoid confusion:

Dim db as DAO.Database
or
Dim rst as ADODB.Recordset

Personally I haven't found one thing that is easier or better in ADO. I believe it is mostly to move Access more toward compatiblity with SQL Server.

As for the days without entries, I had the same issue. I think somewhere in the source queries I made sure to create records with zero values for the days like weekends if no hours were recorded.
If you run into more weeks, it gets more complicated. I haven't had that need but I could see that if you did you would have to dynamically assign additional columns, lables and data. It would require much reworking.

Dean

 

[benallen002]

Dean,

Well, I can't say thank you enough. It looks like I just about got it all figured out.

I worked around the missing day issues by changing my UNION statement to just include ALL fields (*) from the three crosstab queries. This way if the there isn't a certain date, it (the VBA code) just leaves the source blank on the report and moves on to the next date.

The one problem I'm having though is when I try to issue the paramaters (in this case the date range) to the crosstab queries. My union query table works like it is supposed to, but for some reason when I run the report off of this query, I get the following VBA error message:

Run-Time Error '3061':

Too few parameters. Expected 0.

When I go to debug, it is highlighting this row in the module

Set rst = db.OpenRecordset("qryWeeklyTime")

Any ideas why? If not, I'll keep hacking away until I figure it out. And then post the final stuff when I'm done.

Thanks for all the help,

Ben

 

[dsdavids]

Where are your parameters coming from, if they are coming from a control on a form or somewhere outside the report you have to feed those values. I don't remember exactly but I know before you run the query you must declare a querydef, set it to the represent the query that needs the parameters and define them off the form. Something like:

dim qdf as querydef

set qdf = "NameOfYourSavedQuery"

qdf.parameter("YourParameterName") = Forms!("YourSourceFormName").SourceControl

There is bound to be error in my syntax but that is the general idea as I remember it.

Dean