elena said:
Hi, All
I have a table with following fields:
IssDate IssTime Tick Badge Locat Viol
6/18/07 15:30 102003 999 MAIN 141
6/19/07 16:30 102023 999 MAIN 151
6/22/07 17:30 102022 999 MAIN 146
Is it posible in the crosstab query show summary for the every day of the week
with actual date assign like "Sunday 6/17/07" and have as many columns as 7
for every day?
Please, help
How can i produce produce following result:
Badge Sunday Monday Tuesday Wednesday Thursday ......
6/17/07 6/18/07 6/19/07 6/20/07 6/21/07
999 0 1 1 0 0
elena,
Table:
I was not able to identify a primary key for certain, and so I picked IssDate, IssTime,
Tick as my best guess. (Badge should be a foreign key to a Badges table, but I have
omitted it and the table for brevity.)
Create a blank MS Access database. You can copy and paste this DDL SQL query into an MS
Access Query, executing it in order to create the table.
CREATE TABLE BadgeTracking
(IssDate DATETIME
,IssTime DATETIME
,Tick INTEGER
,Badge INTEGER
,Locat TEXT(255)
,Viol INTEGER
,CONSTRAINT pk_BadgeTracking
PRIMARY KEY (IssDate
,IssTime
,Tick)
)
Sample Data:
Make sure a reference to Microsoft DAO 3.6 Object Library is checked.
Create a module and paste the following VBA code it and run it to load the sample data.
Public Sub sInsertBadgeTracking()
Dim db As DAO.Database
Dim strSQL As String
Set db = CurrentDb()
strSQL = "INSERT INTO BadgeTracking VALUES "
strSQL = strSQL & "(" & Chr(34) & "06/18/2007" & Chr(34) & ","
strSQL = strSQL & Chr(34) & "15:30" & Chr(34) & ","
strSQL = strSQL & "102003" & ","
strSQL = strSQL & "999" & ","
strSQL = strSQL & Chr(34) & "MAIN" & Chr(34) & ","
strSQL = strSQL & "141" & ")"
db.Execute (strSQL)
strSQL = "INSERT INTO BadgeTracking VALUES "
strSQL = strSQL & "(" & Chr(34) & "06/19/2007" & Chr(34) & ","
strSQL = strSQL & Chr(34) & "16:30" & Chr(34) & ","
strSQL = strSQL & "102023" & ","
strSQL = strSQL & "999" & ","
strSQL = strSQL & Chr(34) & "MAIN" & Chr(34) & ","
strSQL = strSQL & "151" & ")"
db.Execute (strSQL)
strSQL = "INSERT INTO BadgeTracking VALUES "
strSQL = strSQL & "(" & Chr(34) & "06/22/2007" & Chr(34) & ","
strSQL = strSQL & Chr(34) & "17:30" & Chr(34) & ","
strSQL = strSQL & "102022" & ","
strSQL = strSQL & "999" & ","
strSQL = strSQL & Chr(34) & "MAIN" & Chr(34) & ","
strSQL = strSQL & "146" & ")"
db.Execute (strSQL)
End Sub
VBA:
For some reason, MS Access 2000 will not allow the use of the WeekdayName() function in
SQL QueryDefs, so I had to encapsulate it in a user VBA function. (Your version of MS
Access may allow this. If so, change fWeekdayName to WeekdayName in the queries below.)
Create a module and name it basWeekdayName and paste the following code in:
Public Function fWeekdayName(intWeekday As Integer) As String
Dim strWeekday As String
strWeekday = WeekdayName(intWeekday)
fWeekdayName = strWeekday
End Function
Query:
Your request calls for seven fixed columns, but the columns all have variable names.
The way to established fixed column names is to use the IN clause after the PIVOT clause.
That IN clause only accepts a literal list (as far as I have ever been able to determine).
You can either display the dates, or have the seven fixed column titles.
BadgesByWeekdayNameAndDate_Unfixed
TRANSFORM COUNT(BT1.Tick) As CountOfTick
SELECT BT1.Badge
FROM BadgeTracking AS BT1
GROUP BY BT1.Badge
PIVOT fWeekdayName(DatePart("w", BT1.IssDate))
& " " & BT1.IssDate
BadgesByWeekdayNameAndDate_Fixed
TRANSFORM Nz(COUNT(BT1.Tick),0) As CountOfTick
SELECT BT1.Badge
FROM BadgeTracking AS BT1
GROUP BY BT1.Badge
PIVOT fWeekdayName(DatePart("w", BT1.IssDate))
IN ("Sunday"
,"Monday"
,"Tuesday"
,"Wednesday"
,"Thursday"
,"Friday"
,"Saturday")
Results:
Badge, Friday 06/22/2007, Monday 06/18/2007, Tuesday 06/19/2007
999, 1, 1, 1
Badge, Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday
999, 0, 1, 1, 0, 0, 1, 0
Additional Comments:
Since any particular seven consecutive days are only going to have a single date range
anyway, this implies that the queries listed above will really have additional code:
WHERE BT1.IssDate BETWEEN <weekstartdate> and <weekenddate>
This means you already know what the dates were.
You could have:
TRANSFORM Nz(COUNT(BT1.Tick),0) As CountOfTick
SELECT BT1.Badge
,<weekstartdate> & "to" & <weekenddate> as WeekdateRange
FROM BadgeTracking AS BT1
GROUP BY BT1.Badge
WHERE BT1.IssDate BETWEEN <weekstartdate> and <weekenddate>
PIVOT fWeekdayName(DatePart("w", BT1.IssDate))
IN ("Sunday"
,"Monday"
,"Tuesday"
,"Wednesday"
,"Thursday"
,"Friday"
,"Saturday")
<weekstartdate> and <weekenddate> would be replaced by parameters calls or form/control
references (or possibly subquery calls to other tables).
That way, the dates of the week would be clear in the output.
Sincerely,
Chris O.
