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?

 

[benallen002]

The parameters are coming from the crosstab queries themselves right now. I haven't bothered to make a form for all this stuff yet. And like I said, the Union query that is the reports Source runs perfectly fine after the parameters are fed into it. And I have run the report somewhere earlier in the delevopment with parameters and it would ask for them at runtime just like the query. Now I just get the error message.

Anyways, I'll play around with your code solution and see what I can come up with.

Thanks again

Ben

 

[benallen002]

Man, these parameters are driving me crazy. I think it is because I had to set the same parameters (StartDate and EndDate) on each of the three seperate queries that form the union query that the report is sourced to. Anyways, I'm giving up on it for the day. Try again tomorrow.

Thanks for all the help

Ben

 

[SydneyGeek]

Ben,

Following up Dean's approach -- If you build the form and use that to feed the parameters for the Union queries, you won't get parameter prompts.

Set it so the button that triggers the report does the following:
1. Hides the form (Me.Visible = False)
2. Runs the report (DoCmd.OpenReport ...)

Then, on the Close event of the report, close the form as well (DoCmd.Close acForm, "YourFromName")

Denis

 

[benallen002]

well thanks for all the help guys, but I still can't figure it out. I've tried running the report from a form and without a form. Tried to rearrange the code a million different ways. Tried to figure it out using the form in the Northwind database that is parameter driven as template; didn't work. Tried the help files and the code in there; didn't work.

Can it really be that hard to create a report that is based on a parameter query? I mean, that just seems a little odd.

Oh well

Thanks again

Ben

 

[dsdavids]

no, I know exactly what you need. I don't remember where I had to do it but when I find it and refresh my own memory I'll see if I can help.
Did you try that querydef code I posted? That is the basis of what you need, I'm sure. I just don't remember the whole process and haven't found the report I used it in.
Another thing you can do is change your parameter to a user function, then get the value you need in the body of the function. I've had to do that before as well. With nested queries I found that something that worked as standalone query failed when it was nested in another.

Dean

 

[VicRauch]

Ben, What I have done to get past the "Missing Parameters" message is to create a form based on the same query the report is based on. Then I can open that form as hidden just before opening the report. I can then use the form and Public functions within the form to make whatever changes are needed within the report at run time. Have some VBA within the report call the needed Public functions within the hidden form. The sytax for the call to the Public function within the form is (I think) ReturnValue = Forms!frmName.functionName(). You can pass parameters to the function, and the function will return whatever value you have defined, if needed. Of course the "frmName" and "functionName" need to be changed to your form and function names.
My other suggestion would be to use the Day-of-the-week number you can get from a date, rather than using the date within your queries. That way, you will always know you will have numbers from 1 thru 7 that will be the names of the columns within the crosstab queries. If you need to post the dates on the report, you can use text fields on a hidden form where their (the text fields) control source would look something like: Forms!frmHiddenForm!SundayDate (MondayDate, etc.) Of course, as you start the whole process, these fields would be loaded with the appropriate dates after you open the form in Hidden mode.
HTH,

 

[SydneyGeek]

Dean, I shoudl have read your post a bit more carefully.

If you are using a crosstab based on a parameter query, you need to specifically define those parameters for the crosstab.
Say, for argument's sake, you have [FirstDate] and [LastDate] as parameters in the query that the crosstab uses. Do this:
1. Open the crosstab in Design view.
2. Query > Parameters will give you a pop-up with 2 columns: Parameter name and Data Type. In the example, enter [FirstName] and [LastName] as the parameters, and Date (or possibly Date/Time) as the data type. The data types are a drop-down list, so you won't get it wrong.

Save the query and see if that fixes it.

EDIT: If you are using a form for the parameters, you will need to reference them like [Forms]![frmSearch]![FirstDate]

Denis

 

[benallen002]

Denis, Vic,

Thank you both for your suggestions. Vic, I'm going to give your suggestion a try...if I can ever fully understand it. I'm still learning Access as I am developing this application/database and that leads to frustration sometimes; like trying to build a house and not knowing how to use nails and a hammer.

Denis, I found out pretty early that when you run a parameter query (at least a crosstab), Access won't even let you run the query until you have defined said parameters on that form you were describing. So, unfortunately that isn't the solution.

Anyways, I really want to learn how to do this in code; and since I know it can be done, I'm just going to stick with it until I get it.

Thanks to all those that have been a help. As soon as I reach a non-reacharound solution, I will gladly share it with the board.

Ben

 

[benallen002]

Well guys, I finally found a solution that not only works, but isn't complicated or complex at all. As promised, I will gladly share this information to all who are interested. It is a pretty lengthy process, however, too long for me to post all the details here on the board. So if anyone comes along and wants/needs the details, feel free to e-mail me.

Basically, what I did was take the Union query, that was parameter based, and between running it and the report, created a table that was a straight copy of the Union query, except without any parameters (obviously). I then used this table (and not the query) as the RecordSource for the report. I was also able to use field names of the table (the dates of the range specified on a form) as the ControlSource for the controls on my report as well. All this without involving QueryDefs or anything like that. In fact, the VBA code is incredibly short and easy.

Thanks to everyone that has posted before in reply. The different ideas presented by the experts on this board led me in the right direction and, eventually, to the right solution. I will leave some keywords down at the bottom of this post to aid those in search of a simliar solution.

Thanks,

Ben


WEEKLY PAYROLL REPORT, UNION QUERY REPORT, PARAMETER QUERY REPORT

 

[VicRauch]

Ben,
Thanks for the "final answer," sorry you don't get the million bucks! Anyway, the one thing that perked my interest is that you said you can use the column names in your report that are from the dates you use. I would think those column names, if they are a date would change with every new running of the report. Or did you use the Day-Of-The-Week as the column name?
Thanks,