Re: Monthly Totals Query
- From: "Allen Browne" <AllenBrowne@xxxxxxxxxxxxxx>
- Date: Sun, 11 Nov 2007 14:04:56 +0900
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
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Scottie" <Scottie@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:F78285FF-C4E4-4D64-B8CA-E51122DFECDE@xxxxxxxxxxxxxxxx
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" wrote:
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" <Scottie@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:C801C38E-AA39-4B0A-B508-69B55F8863D5@xxxxxxxxxxxxxxxx
> 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 :)
.
- References:
- Re: Monthly Totals Query
- From: Allen Browne
- Re: Monthly Totals Query
- From: Scottie
- Re: Monthly Totals Query
- Prev by Date: "floating" date query
- Next by Date: Re: Need your help
- Previous by thread: Re: Monthly Totals Query
- Next by thread: Grid Pane Field Expressions
- Index(es):
Relevant Pages
|
