Crosstab help

[JEA]

I want to combine the data from 2 tables in a report. I think I need to do this through a crosstab query, but I've no idea how as it needs to calculate it's data.

The row heading needs to be [DateWorked] from the "Hours" table.

Each Column heading needs to be every [StaffPIN] from the "StaffData" table.

I want it to display the number of hours each staff member worked for a perticular day. The "Hours" table holds each staff member's start and finish time. I have a function called workedHours that, given the start and finish time it will return hours worked (a shift can overlap 2 days so DateDiff wouldn't work).

Can anyone help?

 

[Rabbie]

Try Normalising your data. That will make everything much easier.

 

[JEA]

I can't see how I could.

Think I'm in a real mess with this though. Don't want to spend more time on it if it's fundamentally wrong. Could anyone please help?

 

[Rabbie]

Your Hours table is clearly not normalised. THis will only give you grief in the future. What happens if someone decides there can be 10 people in on a day?

Remember that Access is NOT a spreadsheet. Access tables should be tall and thin. A a simplistic level you should have a table that has fields for the following

EmployeeID
Date
StartTime
EndTime

Obviously you could expand this entry to include a cost code so you can see what each person was working on.

From this you can write a query to extract all the reocords for a particular date and see who was working that day and for how long.

Hope this Helps

 

[JEA]

A a simplistic level you should have a table that has fields for the following

EmployeeID
Date
StartTime
EndTime

With this example, can one employee work more than one day?
It would also mean there'd be multiple entries for the same day.

I'm not trying to argue. In my ignoance I don't quite follow you.

 

[Rabbie]

With this example, can one employee work more than one day?

Yes

It would also mean there'd be multiple entries for the same day.

Correct. Thats why you would run a query to extract the data for that day

I'm not trying to argue. In my ignoance I don't quite follow you.

If you arent sure what I mean then its good to ask.

The schema for the table I gave you was very minimal. It should have an Autonumber field to make it easier to to refer to a record.

Please ask if you need any more help

 

[JEA]

Would this be any be any better?

 

[Banana]

Looks nice!

Just two comments:

You don't need table OtherHours- you can use HourWorked table with a ShiftType of "Other".

I am not sure what StaffRef is all about- Can you elaborate a bit on that table's purpose?

 

[JEA]

The Hours table records the hours that are chargeable to the client. ie, the hours that the staff members actuall spend doing their job.
The 'other hours' table records the hours that the staff spend doing other stuff. This will include authorised/unauthorised absences, training hours, holiday hours etc. These are not chargable to the client.
The shiftType field of this table will record what they were doing (holiday, training, authorised abcence etc). The shiftType field of 'hours' will record what type of shift they worked (day, night, morning etc).

StaffReq records each shift that is required on a perticular day. Eg)

| ID | DateReq | ShiftReq |
...1....01/11/07.....Day
...2....01/11/07.....Day
...3....01/11/07.....Night
...4....02/11/07.....Day

So on 01/11/07 2 staff were required on the day shift and 1 on the night shift.
On 02/11/07 1 staff was required on the day shift.
This is so the db can compare the StaffReq table with the Hours table to work out the number of required hours that went unworked.

Hope that was clear enough.

 

[JEA]

reading that back, I think I could get rid of the otherHours table, the ShiftType field of the Hours table would be sufficient to identify if the shift is chargeable or not if I put a chargeable/non-chargeable flag in the shiftType table .

Thanks.