The shifts have to come from somewhere, so you would need a table of shifts,
and a query that outer-joins this to your existing table.
1. Create a table that contains the 1st of the month for each month.
One field named (say) TheMonth, of type Date/Time.
Mark the field as primary key.
Save the table as (say) tblDate.
Enter a record for each month in the period you are interested in.
2. If you don't already have one, create a table of the valid shift numbers.
One field named ShiftID, of type Number.
Mark the field as primary key.
Save as tblShift.
Enter a record for each shift.
(It would be a good idea to use this as a foreign key to your existing
sys_shifts table, so you cannot get an invalid Shift number entered.)
3. Create a query using tblDate and tblShift.
There should be no join in the upper pane of table design.
Drag both fields into the design grid.
Sort by TheDate first, then ShiftID.
Save as (say) qryDateShift
This gives you every combination of date and shift.
4. Create a query using sys_shifts.
(a) Type this expression into the Field row:
TheMonth: DateSerial([Year([Incident Date]), Month([Incident Date]), 1)
Depress the Total icon on the toolbar (upper sigma icon.)
Access adds a Total row to the grid.
Accept Group By under this date.
(b) Add the Shift field to the grid.
Accept Group By in the Total row.
(c) Add the Shift field again.
This time choose Count in the Total row.
This gives you the count of shifts for each month.
Save as (say) qryShiftsPerMonth.
5. Create a query using qryDateShift and qryShiftsPerMonth as input tables.
(a) In the upper pane of query design, drag qryDateShift.TheDate and drop
onto qryShiftsPerMonth.TheMonth. Access shows a join line. Double-click this
line, and choose:
All records from qryDateShift, and any matches from qryShiftsPerMonth.
(This is called an outer join.)
(b) Drag qryDateShift.TheShift, and drop onto sys_shifts.Shift.
Double-click, and make it an outer join.
This gives you all dates and shifts, even where there is no match in
sys_shifts.
6. Turn this into into a crosstab query.
Use
- TheMonth as the row heading;
- Shift as the Column heading;
- CountOfShift as the Value.
You've actually used several SQL tricks here:
- Cartesian product: = every possible combination, step 3 above.
- Outer join: = all records from one side of the join. More info:
http://allenbrowne.com/casu-02.html
- Stacked queries: = using one table as an input "table" for another.
- Crosstab query. More crosstab tricks:
http://allenbrowne.com/ser-67.html
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Reply to group, rather than allenbrowne at mvps dot org.
Scottie said:
Ok that query is working great....
TRANSFORM Count(sys_shifts.Shift) AS CountOfShift
SELECT Year([Incident Date]) & " " & MonthName(Month([Incident Date])) AS
TheYear
FROM sys_shifts INNER JOIN Incidents ON sys_shifts.ID=Incidents.Shift
GROUP BY Year([Incident Date]) & " " & MonthName(Month([Incident Date]))
PIVOT sys_shifts.Shift;
Sometimes the not all shifts appear during the month...but I would still
like to have the zero values.....any ideas?
Thanks a lot for the help
Allen Browne said:
A crosstab query will do this.
1. Create a query, using your table.
2. Change it to a Crosstab (Crosstab on Query menu.)
Access adds Total and Crosstab rows to the design grid.
3. In the Field row, first column, add the Name field.
Access Group By in the Total row under this field.
In the Crosstab row, choose Row Heading.
It now looks like this:
Field: Name
Total: Group By
Crosstab: Row Heading
4. In the next column, enter:
Field: TheYear: Year([Date])
Total: Group By
Crosstab: Row Heading
5. In the next column:
Field: TheMonth: Month([Date])
Total: Group By
Crosstab: Row Heading
6. In the next column:
Field: Category
Total: Group By
Crosstab: Column Heading
7. In the next column:
Field: Name
Total: Count
Crosstab: Value
Hopefully you don't really have columns called Name and Date. Date is a
reserved word (in JET SQL and in VBA code), so Access is likely to
misunderstand it for the system date, and almost everything in Access has
a
Name property, so again Access may understand it as the Name of your
form/report etc.
Scottie said:
Group,
I have been trying to make a query that will count text occurances in a
crosstab format. For example:
Table1
Name
Category
Date
ie:
Name Category Date
John Shift3 10/22/2007
Amy Shift2 10/18/2007
John Shift2 11/1/2007
The way that I would like the query to return is as follows:
Date Shift1 Shift2 Shift3
October 2007 0 1 1
November 2007 0 1 0
Thank you for the help
