Worked V Overtime Report

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am using Access 2000.

I have 2 tables hours worked (1) and overtime (2). Structure is Table 1.
Employee, Date, Hours. Table 2. Employee, Date, Hours, Rate.
What is the best way to create a report summarising the data into one line
per employee for a given date range ?
Data in the files looks like -
Table 1. 123, 01/12/2004, 37.5
Table 2. 123, 01/12/2004, 3.5, X 1.5
123, 01/13/2004, 3.5, X 1.5
123, 01/18/2004, 8, X 2
Report should look like :
Employee, Worked, X 1.5, X 2
123 37.5 7 8
I have tried a union query to get the data into a workable form but creating
the report is the problem.

Thanks in advance
 
Lockhak

I think a crosstab query would be applicable.

Would it be possible to combine the two tables into one? This would
certainly make life easier for you.
 
Lockhak,

Normally there would be no problem in applying a date range criteria in
a crosstab query. What have you tried, and what happens... error
message? incorrect data returned? something else?
 
Hi Steve

In my crosstab query I have made reference to the form where I enter the required dates in criteria. The line reads
Between [forms]![Overtime_Date_Range]![Beginning_Date] And [forms]![Overtime_Date_Range]![Ending_Date

The error I get at run time is "The Microsoft Jet database engine does not recognise the '[Forms]![Overtime_Date_Range]![Beginning_Date]Week No] as a valid field name or expression

If I replace the [forms]! etc with #01/12/2004# then it works

I have used the above filter in other select queries and it works OK
 
Lockhak,

I know you will have already double-checked, but I think you will have
to check again... have you correctly spelled the exact name of the form
and the beginning_date and ending_date controls in your expression. It
is strange that there is the 'Week No]' at the end of the error message.
If you are sure the criteria is entered correctly, I would delete the
query, compact/repair the database, and then create the query again... I
have had times when weird things were happening with queries that was
fixed by this approach. Please post back if you still can't get it
right... but as far as I can see at the moment, you are doing it right
and it *should* work.
 
AHA!

First thing, with a crosstab query you MUST declare your parameters and if any
other queries are used in the crosstab their parameters must also be declared.

Open the query in design mode
Select Query: Parameters from the Menu
Fill in the EXACT name of the parameter in column 1
Select the data type of the parameter in column 2

So you would do the following in Column 1 and Column 2:
[Forms]![Overtime_Date_Range]![Beginning_Date] Date
[forms]![Overtime_Date_Range]![Ending_Date] Date
 
Back
Top