Firstly, you don't need to group the query as you are not aggregating
any values in the query itself; that will be done in the report as
described below.
Secondly, you need to include a computed column in the query to return
the quarter for each InputDate, which you can do using the DatePart
function.
Finally as the query is being used as the RecordSource for a report
there is no point in ordering it as the report will simply ignore the
ORDER BY clause and use its own internal sorting mechanism. So the
query would look like this:
SELECT InputDate, SOC, JSA, LockoutAnalysis, SafetyHuddle,
DatePart("q", InputDate) AS Quarter
SpecialHousekeeping, Employee
FROM tblSafetyIncentiveMain
WHERE Employee = [forms]![frmSafetyIncentiveMain]![cboEmployees]
AND Year(InputDate) = [forms]![frmSafetyIncentiveMain]![CalYear];
In report design view group the report firstly by Quarter and then by
InputDate. Give the Quarter group level a group header and footer.
Put the Employee in the report header, along with a text box with a
ControlSource of =Year([InputDate]) to show the year in question (or
in the page header if you want it at the top of each page rather than
just the first). Put the Quarter in the group header, the rest in the
detail section. I'm assuming that you want to count the number of
rows were each column has a value, i.e. is not Null, so in the group
footer add text box controls to sum the values per quarter with
ControlSource properties of:
=Count([SOC])
=Count([JSA])
=Count([LockoutAnalysis])
=Count([SafetyHuddle])
=Count([SpecialHouskeeping])
However, if these columns are Boolean (Yes/No) data type rather than
text, number or date data type then they can't be Null, so you'd have
to count the True values in a different way. This is done by summing
the return value of an expression which returns 1 or 0:
=Sum(IIf([SOC],1,0))
=Sum(IIf([JSA],1,0))
=Sum(IIf([LockoutAnalysis],1,0))
=Sum(IIf([SafetyHuddle],1,0))
=Sum(IIf([SpecialHouskeeping],1,0))
To get the totals for the whole year put an identical set of text
boxes in the report footer.
I should point out that your table design is not a good one. By
having a separate column for SOC, JSA etc you are doing what's known
as 'encoding data as column headings'. A fundamental principle of the
database relational model, 'the information principle' is that data is
stored as explicit values at column positions in rows in tables, and
in no other way. A correct design would be to have related tables,
one for employees, one for the 'issues' say (you'll doubtless be able
to come up with a more appropriate name), and a third,
'employee_issues' say which is related to the other two by having
columns . The 'employee_issues' table would have columns such as
EmployeeID, IssueID (each a foreign key), InputDate. The 'issues'
table would have columns suchs as IssueID (its primary key) and
IssueType. You'd then join the tables in a query and base your report
on the query. In this case you could group the report by EmployeeID,
Employee and IssueType and count the rows per group with COUNT(*), or
you could leave the query ungrouped and do the grouping and counting
in the query in the same way as described above.
As well as being a better design in principle there are practical
advantages resulting from it in that you can do things such as adding
a new issue type at any time simply by inserting a new row into the
'issues' table rather than having to amend the table design as at
present.
Ken Sheridan
Stafford, England
Sorry wayne thats the wrong SQL... Here is what I have so far!
SELECT tblSafetyIncentiveMain.InputDate, tblSafetyIncentiveMain.SOC,
tblSafetyIncentiveMain.JSA, tblSafetyIncentiveMain.LockoutAnalysis,
tblSafetyIncentiveMain.SafetyHuddle,
tblSafetyIncentiveMain.SpecialHousekeeping, tblSafetyIncentiveMain.Employee
FROM tblSafetyIncentiveMain
GROUP BY tblSafetyIncentiveMain.InputDate, tblSafetyIncentiveMain.SOC,
tblSafetyIncentiveMain.JSA, tblSafetyIncentiveMain.LockoutAnalysis,
tblSafetyIncentiveMain.SafetyHuddle,
tblSafetyIncentiveMain.SpecialHousekeeping, tblSafetyIncentiveMain.Employee
HAVING
(((tblSafetyIncentiveMain.Employee)=[forms]![frmSafetyIncentiveMain]![cboEmployees]) AND ((Year([InputDate]))=[forms]![frmSafetyIncentiveMain]![CalYear]))
ORDER BY tblSafetyIncentiveMain.InputDate;
--
Thanks,
Chad
SoggyCashew said:
Wayne thanks for the reply! I must say im confused? I have the form andI
have created a query for the report but how would I get it to show a total
for each quarter for the year selected. I need totals for the fields SOC,
JSA, LockoutAnalysis, SafetyHuddle and SpecialHouskeeping. Here is the
reports query and the employees name and year is pulled from the form. Now
how do I get it to seperate and total these fields?
SELECT tblSafetyIncentiveMain.InputDate, tblSafetyIncentiveMain.SOC,
tblSafetyIncentiveMain.JSA, tblSafetyIncentiveMain.LockoutAnalysis,
tblSafetyIncentiveMain.SafetyHuddle,
tblSafetyIncentiveMain.SpecialHousekeeping, tblSafetyIncentiveMain.Employee
FROM tblSafetyIncentiveMain
GROUP BY tblSafetyIncentiveMain.InputDate, tblSafetyIncentiveMain.SOC,
tblSafetyIncentiveMain.JSA, tblSafetyIncentiveMain.LockoutAnalysis,
tblSafetyIncentiveMain.SafetyHuddle,
tblSafetyIncentiveMain.SpecialHousekeeping, tblSafetyIncentiveMain.Employee
HAVING (((Year([InputDate]))=[forms]![frmSafetyIncentiveMain]![CalYear]))
ORDER BY tblSafetyIncentiveMain.InputDate;
Hi
There are lots of bits of code that you can write to make this work -or you
can use a simple QBF to do the same thing.
Create your report to show the "stuff" you want. Add a QBF (Query ByForm)
to the query the report is based - point the query criteria to the form you
are using and it should work fine.
--
Wayne
Trentino, Italia.
:
Hello, I have a form "frmSafetyIncentiveMain" with a year combo control named
"CalYear" and I have a combo control for employee’s names named
"cboEmployees". I want to use these as my selections for a query named
"qryYearTotals". In this query I want to be able separate totals byquarter
for the year and the employee that I selected from above. Let’s say I had a
field named "SOC" How would I separate how many times SOC was used within the
year selected and have it show up on a report separated by quarter for that
year?
