Report using totals from 2 queries...

[JoeM]

it's how they are set up I think because there is a drop down for choosing the staff names and that is how it lists it?? Does that make sense?

That shouldn't make a difference. It still only stores one value in that field.

Why do you have this?
[Query2 2011].[Workshop Data].[Staff Member].Value

Why the need for the "Value"? How is this field formatted?

I think the best thing you may be able to do is to post the first few rows of data from your Staff Name table and two queries, so I can see if your SQL code makes sense, based on the data.

[TSALauren]

ok. This is the design view of the Table that contains the information I need to use.



This is the 2011 query



This is the 2012 one



and this is the last query that I put the sql down for.

[JoeM]

Though that may also be helpful, I think you misunderstood. I was hoping to see some data samples of each table/query, to make sure that the values you are joining on look good. I would like to see if I can re-create your scenario on my computer, so having some sample data would be helpful.

Also, what is the difference between the "Staff Table" and "Query1"?

[TSALauren]

There really is no difference, i just thought you said to make the first query the list of staff.

Query 2011

Workshop Data.Staff Member.Value	Workshop Data.Date of Workshop	SumOfHours
, Karen	6/7/2011	0.75
, Martha	10/13/2011	2.5
, Martha	12/8/2011	7
, Karin	6/7/2011	0.75
, Robbyn	2/18/2011	1.5
, Robbyn	2/22/2011	3
, Robbyn	4/28/2011	2
, Sue	3/8/2011	2

from Query 2012

Workshop Data.Staff Member.Value	Workshop Data.Date of Workshop	SumOfHours
, Martha	2/10/2012	3
, Martha	2/17/2012	3
, Martha	3/2/2012	3
, Brenda	2/10/2012	3
, Brenda	2/17/2012	3
, Brenda	2/21/2012	3
, Brenda	3/2/2012	3
, Jessica	2/10/2012	3
, Jessica	2/17/2012	3

From Query 3:

TSA Staff Name	Query2 2011.SumOfHours	Query2 2012.SumOfHours
, Cheryl	3	6
, Cheryl	3	6
, Cheryl	3	3
, Cheryl	3	3
, Cheryl	3	3
, Cheryl	3	6
, Cheryl	3	3

from the Main Table

Date of Workshop	Staff Member	Name of Workshop	Description	Presenter	Registration Cost	Hours	Other Expenses	Comments	Certificate of Attendence	Misc Notes
1/19/2011	, Penni	Ethical and Risk-Management Issues in Social Work		NASW-MN Chapter, Rachel Konzen		2			Yes	Minnesota Board of Social Work - CEP# 06-02
1/25/2011	, Ellen	Expanding Our Toolbox: Sharing Social Skills Strategies That Work For Young People With Emotional-Behavioral Issues and Attention Concerns		Cathy Thomas, LISW, MS. ED & Scott Graham, LISW, MS. ED		3		Hosted by MACMH	Yes
1/27/2011	, Linda	Brain Change Therapy: New Strategies for Anxiety, Depression & Trauma		Carol Kershaw		6		Premier Education Solutions (PESI)	Yes
1/27/2011	, Annette	Brain Change Therapy: New Strategies for Anxiety, Depression and Trauma		Carol Kershaw	$189.99	7		Premier Education Solution (PESI)	Yes

Does that help?

[JoeM]

If you want the total number of hours per person for the whole year (and not just for each date during the year), in "Query 2011" and "Query 2012", change the "Total" row on the "Date of Workshop" field from "Group By" to "Where".

Also, what is "Query1" returning? Is it a subset of Staff Table? If so, what is the SQL code behind that?

[TSALauren]

Q1 is not returning anything. I just thought you said I needed a query for staff name. So I put one.

[JoeM]

Sorry for the confusion. Notice back in poat #8 I said "Your staff listing table/query".
What I was implying was is if you already have a Staff Listing in table form, then just use that. Otherwise, you might have to create a query to get the Staff Listing.
Since it looks like you already have a table that has that, there probably isn't any need to confuse matters and you can just use your table.

[TSALauren]

that seems to work ok.....is there any way to make it not list the people in my staff that have 0 hours?

[TSALauren]

Oh...ok. I feel so stupid when I get into Access. I pretty much understand every other program we have, just not this one and I have to create this databases. <shaking my head>

Sorry for the confusion. Notice back in poat #8 I said "Your staff listing table/query".
What that implies is if you already have a Staff Listing in table form, then just use that. Otherwise, you might have to create a query to get the Staff Listing.
Since it looks like you already have a table that has that, there probably isn't any need to confuse matters and you can just use your table.

[JoeM]

that seems to work ok.....is there any way to make it not list the people in my staff that have 0 hours?

Sure. One way is to add a calculated field to your final query that adds up the hours. The NZ function can be used to convert NULL values to zero.

HourCheck: nz([Query 2011]![SumOfHours],0)+nz([Query 2012]![SumOfHours],0)

So after you add this calculated field, just add > 0 to the criteria row of this field to only return entries where hours are greater than zero.

[JoeM]

Oh...ok. I feel so stupid when I get into Access. I pretty much understand every other program we have, just not this one and I have to create this databases.

Access is not as intuitive as other programs like Excel. And if you attmept to build anything other than a simple database without being comfortable with Access and having background knowledge in Relational Databases and the Rules of Normalization, you might be in for a lot of frustration.

I always recommend that people become familiar with these topics and take some introductory Access classes (or intro books) before trying to do any sort of serious Access programming. It will save you a lot of time, effort, and frustration!

[TSALauren]

Well, I really appreciate your help. Truly. Thank you so much.

[TSALauren]

where do you add this?

Sure. One way is to add a calculated field to your final query that adds up the hours. The NZ function can be used to convert NULL values to zero.

HourCheck: nz([Query 2011]![SumOfHours],0)+nz([Query 2012]![SumOfHours],0)

So after you add this calculated field, just add > 0 to the criteria row of this field to only return entries where hours are greater than zero.

[JoeM]

In a calculated field in a query. If you are not familiar with that, search on "Creating Expressions" or "Calculated Fields" in Access queries (in Access help or a Google search).

[TSALauren]

Ok. Thank you.