If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Quarterly and total for year selected
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 by quarter 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? -- Thanks, Chad |
#2
|
|||
|
|||
Quarterly and total for year selected
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 By Form) 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. "SoggyCashew" wrote: 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 by quarter 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? -- Thanks, Chad |
#3
|
|||
|
|||
Quarterly and total for year selected
Wayne thanks for the reply! I must say im confused? I have the form and I
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; -- Thanks, Chad "Wayne-I-M" wrote: 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 By Form) 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. "SoggyCashew" wrote: 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 by quarter 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? -- Thanks, Chad |
#4
|
|||
|
|||
Quarterly and total for year selected
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" wrote: Wayne thanks for the reply! I must say im confused? I have the form and I 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; -- Thanks, Chad "Wayne-I-M" wrote: 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 By Form) 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. "SoggyCashew" wrote: 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 by quarter 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? -- Thanks, Chad |
#5
|
|||
|
|||
Quarterly and total for year selected
Sorr my fault - I didn't explain.
You see/view the results on the report not the query (you can use a query but it much simpler in a report) To start with create your report to show the details/results you want. You will then simply open the report (maybe from a button on your form) and the report opens filtered (by the QBF in the query the report is based on) Hope this is clearer -- Wayne Trentino, Italia. "SoggyCashew" wrote: 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" wrote: Wayne thanks for the reply! I must say im confused? I have the form and I 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; -- Thanks, Chad "Wayne-I-M" wrote: 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 By Form) 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. "SoggyCashew" wrote: 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 by quarter 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? -- Thanks, Chad |
#6
|
|||
|
|||
Quarterly and total for year selected
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 On Feb 27, 1:29 am, SoggyCashew wrote: 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" wrote: Wayne thanks for the reply! I must say im confused? I have the form and I 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; -- Thanks, Chad "Wayne-I-M" wrote: 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 By Form) 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. "SoggyCashew" wrote: 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 by quarter 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? -- Thanks, Chad |
Thread Tools | |
Display Modes | |
|
|