INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Form that shows 4 quarters data for selected year

Form that shows 4 quarters data for selected year

(OP)
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.....

RE: Form that shows 4 quarters data for selected year

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"

CODE --> sql

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

RE: Form that shows 4 quarters data for selected year

(OP)
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.....

RE: Form that shows 4 quarters data for selected year

A crosstab query will output the format you require:

CODE --> sql

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

RE: Form that shows 4 quarters data for selected year

(OP)
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.....

RE: Form that shows 4 quarters data for selected year

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

RE: Form that shows 4 quarters data for selected year

(OP)
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.....

RE: Form that shows 4 quarters data for selected year

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

RE: Form that shows 4 quarters data for selected year

(OP)
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.....

RE: Form that shows 4 quarters data for selected year

This query will sum award by quarter columns:

CODE --> sql

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

RE: Form that shows 4 quarters data for selected year

(OP)
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.....

RE: Form that shows 4 quarters data for selected year

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:

CODE --> sql

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).

CODE --> sql

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close