My Assistant
Site Message
(Message will auto close in 2 seconds)
  |
 Summary Query
|
|
 Nov 12 2004, 03:32 AM
Post
#1
|
|
|
New Member Posts: 16 From: Northamptonshire, UK  |
Hi
I have a db that contains staff records that detail a status for each day eg. working day, sick day, holiday, overtime day. I need to summarise this information. e.g Person A has completed x working days, x sick days, x overtime days etc I have tried using a query and then using DCount function on the extracted records but to no avail! I thought it would be simple. However, nothing usually is! Regards Jim |
 |
 
|
|
Nov 12 2004, 04:14 AM
Post
#2
|
|
|
UtterAccess Addict Posts: 145 From: Lancashire, UK |
Try
Select staffid,count(working days), count(sick days), count(holiday), count(overtime day) group by staffid Hope this helps Tony |
|
|
|
|
Nov 12 2004, 04:40 AM
Post
#3
|
|
|
UtterAccess VIP Posts: 5,230 From: Scotland (Sunny Glasgow) |
Jim
We require a bit more info on the structure of your tables to be able to get closer to the answer. Jim |
|
|
|
|
Nov 12 2004, 04:49 AM
Post
#4
|
|
|
New Member Posts: 16 From: Northamptonshire, UK |
jsltraining
I have extracted the information in a query, with the following heading Driver Name, Day Description(working,sick etc), and the day(which is date format) so for each driver, I require a count of each day description type. Hope this helps Jim |
|
|
|
|
Nov 12 2004, 04:53 AM
Post
#5
|
|
|
UtterAccess VIP Posts: 5,230 From: Scotland (Sunny Glasgow) |
You post tells me nothing about your table structure.
I'm looking for table Name1 FeildName1 (PK Autonumber) FieldName 2 etc tableName2 FieldName1 (PK Autonumber) fieldNamex (FK to table1) etc Otherwise (and I've used this analogy before) it's like phoning up a mechanic and saying I have a car, it's blue and it doesn't start. Can you fix it for me? Cheers Jim |
|
|
|
|
Nov 12 2004, 05:23 AM
Post
#6
|
|
|
New Member Posts: 16 From: Northamptonshire, UK |
Jim
Fields from the following three tables make up the query tblDriver DriverID Driverforename DriverSurname tblDriverDays id AvailableDrivingDays (date field) DriverID (many side link to tblDriver DriverID) StatusCodeDescription (eg Working, sick etc) (manyside link from tblDayStatusCodes tblDayStatusCodes id StatusCode (eg w=working, s=sick etc) StatusCodeDescription The query contains the following fields Driver (concatenated from surname and forename AvailableDrivingDays StatusCodeDescription Hope this helps JIM |
|
|
|
|
Nov 12 2004, 05:30 AM
Post
#7
|
|
|
UtterAccess VIP Posts: 5,230 From: Scotland (Sunny Glasgow) |
Cheers, first off, try to maintain some consistency in naming conventions. Don't have multiple instances of the same field name (unless they are PK-FK links)
When linking tables, use the PK to link, nothing else so you are left with. tblDriver DriverID Driverforename DriverSurname tblDriverDays DriverDaysID AvailableDrivingDays (date field) DriverID (many side link to tblDriver DriverID) DayStatusCodeID (manyside link from tblDayStatusCodes tblDayStatusCodes DayStatusCodesID StatusCode (eg w=working, s=sick etc) StatusCodeDescription You should now be able to create a crosstab, With DriverName as the row data, Status CodeDescription as the column Data and count of availabledrivingdays as the value. HTH Jim |
|
|
|
|
Nov 12 2004, 05:43 AM
Post
#8
|
|
|
New Member Posts: 16 From: Northamptonshire, UK |
Jim
Winner! spot one Many thanks, one small problem, easily remedied not doubt! It is cross tabbing with the headers as the autonumber from the tblDaystatuscode rather than the day status code descriptions. any thoughts cheers JIM |
|
|
|
|
Nov 12 2004, 06:08 AM
Post
#9
|
|
|
New Member Posts: 16 From: Northamptonshire, UK |
panic ye not. all sorted I !
JIM |
|
|
|
|
Nov 12 2004, 07:29 AM
Post
#10
|
|
|
UtterAccess VIP Posts: 5,230 From: Scotland (Sunny Glasgow) |
Glad it worked out.
Jim |
|
|
|
 |
|
Jump To Forum:
|
Go to Top · Lo-Fi Version | Time is now: 19th May 2013 - 02:22 AM |