Form that shows 4 quarters data for selected year

[oxicottin]

MaJP, you have help me out tremendously in the past with a similar project (Attendance Database) and im envisioning this sorta like that but not like a calendar but as data. I have attached a image of what I want the form to look like and I think I have the query already done (qry_QuartlySafetyActivity. I want to be able to select an employee and year and display all the (SafetyActivities,SafetyLimit,SafetyAward) like the image on(frm_SafetActivity). How can this be done? Thanks!

Thanks,
SoggyCashew.....

 

[dhookom]

Four copies of the same subform placed on a main form with four text boxes:
Name: txtQ1
Control Source: ="Q1"
Name: txtQ2
Control Source: ="Q2"
Name: txtQ3
Control Source: ="Q3"
Name: txtQ4
Control Source: ="Q4"

Change the query to return "Q1-4"

GroupOnQuarter: "Q" & DatePart("q",[ActivityDate])

Then just use the Link Master Child to filter each subform to the appropriate text box.

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

 

[oxicottin]

I was thinking, there isnt enough room on a form to do all that because the text of the Safety Activity is to long. Im now thinking if I had a list of the safety activity sentences down one side then in a Q1, Q2, Q3, Q4 across I could just count how many times after the employee has accomplished that activity. this would drastically cut down on space and wouldn't repetitively show words.

Thanks,
SoggyCashew.....

 

[dhookom]

A crosstab query will output the format you require:

PARAMETERS [forms]![frm_SafetyActivity]![cboEmployee] Long, 
[forms]![frm_SafetyActivity]![cboYear] Long;
TRANSFORM Count(tbl_Activities.ActivityLimit) AS CountOfActivityLimit
SELECT tbl_Employees.EmployeeID, tbl_Activities.SafetyActivities
FROM tbl_Employees INNER JOIN (tbl_Activities INNER JOIN tbl_SafetyActivity 
ON tbl_Activities.ActivityID = tbl_SafetyActivity.ActivityID) 
ON tbl_Employees.EmployeeID = tbl_SafetyActivity.EmployeeID
WHERE (((tbl_Employees.EmployeeID)=[forms]![frm_SafetyActivity]![cboEmployee]) 
AND ((Year([ActivityDate]))=[forms]![frm_SafetyActivity]![cboYear]))
GROUP BY tbl_Employees.EmployeeID, tbl_Activities.SafetyActivities
PIVOT "Q" & DatePart("q",[ActivityDate]) In ("Q1","Q2","Q3","Q4");

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

 

[oxicottin]

Duan, yes that is exactly what I wanted. Now lets say if I created a subform or do I even need a subform can I make it a list box on the main form and its source was the Query1 (the sql you just gave me) how would I go about linking the combo box (cboEmployee) of my main form (frm_SafetyActivity). Thanks

Thanks,
SoggyCashew.....

 

[dhookom]

You would simply view the subform control properties and update the Link Master and Link Child properties. The link master would be the name of the combo box and child would be EmployeeID.

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

 

[oxicottin]

Last question (Im hoping).... I would like to show a total of (ActivityAward) for each Quarter in the main form (frm_SafetyActivity) I cant do it in the query you gave I dont think because its counting dates and im not familiar with crosstab queries at all. Anyways, I just want to total the ActivityAward for each quarter in the (frm_SafetyActivity) footer. Thanks!

Thanks,
SoggyCashew.....

 

[oxicottin]

Sorry left out the attachment...

Thanks,
SoggyCashew.....

 

[dhookom]

You need to be more specific regarding what you want. "show a total of (ActivityAward) for each Quarter" does this mean for all employees or what?

Does your database have enough records and the desired output?

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

 

[oxicottin]

Ok lets say I selected the first employee "Bob" and in the image below I got from the subform on the main form from the query that we currently have the outcome shows bob has an

1) AWARD of 0.4 and 0.2 for Q1
2) AWARD of 0.4 for Q2

Now, either in the query we already have so I can display it on my main form or subform or somehow I need a "Total Activity Award" that would show the sum for the quarter like:

1) AWARD of 0.4 and 0.2 for Q1 = 0.6
2) AWARD of 0.4 for Q2 = 0.4

Thanks,
SoggyCashew.....

 

[dhookom]

This query will sum award by quarter columns:

PARAMETERS [forms]![frm_SafetyActivity]![cboEmployee] Long, [forms]![frm_SafetyActivity]![cboYear] Long;
TRANSFORM Sum(tbl_Activities.ActivityAward) AS SumOfActivityAward
SELECT tbl_Employees.EmployeeID
FROM tbl_Employees INNER JOIN (tbl_Activities INNER JOIN tbl_SafetyActivity
 ON tbl_Activities.ActivityID = tbl_SafetyActivity.ActivityID)
 ON tbl_Employees.EmployeeID = tbl_SafetyActivity.EmployeeID
WHERE (((tbl_Employees.EmployeeID)=[forms]![frm_SafetyActivity]![cboEmployee]) AND
 ((Year([ActivityDate]))=[forms]![frm_SafetyActivity]![cboYear]))
GROUP BY tbl_Employees.EmployeeID
PIVOT "Q" & DatePart("q",[ActivityDate]) In ("Q1","Q2","Q3","Q4");

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

 

[oxicottin]

Thanks Duane its what I was after.... I never used CrossTab querys and everything I do I get a popup saying I either need some heading or value.

Thanks,
SoggyCashew.....

 

[dhookom]

Crosstabs are easier to understand if you realize they are simply a totals/group by query with a single Sum() or Count() type value and one group by field becomes the column headings. For instance if you have the Sum of revenue by department and Month:

SELECT [Department], [Month], Sum([Revenue]) as TotRev
FROM tblRevenues
GROUP BY [Department], [Month];

To convert this to a crosstab with months across the top, just set the Sum([Revenue]) as the value, [Month] as Column Heading, and all other displayed fields as Group By (or other aggregate).

TRANSFORM Sum([Revenue]) as TotRev
SELECT [Department]
FROM tblRevenues
GROUP [Department]
PIVOT [Month];

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016