Report using totals from 2 queries...

[TSALauren]

I have a database that is used to record training hours. I have queries set up for each year that dump into a report that lists each staff member and then totals the training hours for that year (using the sum feature). I need to get a report that will list the staff name and then 2 years' totals, side by side.



Ex.

staff name 2011 2012
Mr. X 23 45

I know there is probably a simple solution, but I do not know Access very well and I just cannot figure this out. Thanks in advance for your help.

Lauren

[JoeM]

One way.

First create three queries,
1. List of all staff members
2. 2011 totals
3. 2012 totals

Now use, outer joins to link the 2011 and 2012 totals queries back to the list of all staff members in a new query. Use this query as the source of your report.

[TSALauren]

Thanks JoeM, I am wondering how you do the totals in the query then? In the report I have, I used the query to define the dates, then in the 2011 report I used =Sum([hours]) to get the totals. I can do that in the query?

[JoeM]

Take a look at doing Aggregate (Totals) Queries in Access' built-in help files. These are very useful queries that you will probably find many uses for!

[TSALauren]

Thanks so much!

[TSALauren]

I've got the 2 queries and they work great. Now, I am confused about outer joins. I will see what I can find on the forum.

[TSALauren]

There is the 2 queries - only 1 table. How do I do an outer join with just one table?

[JoeM]

You want your final query to include all three other objects:
1. Your staff listing table/query
2. Your 2011 totals query
3. Your 2012 totals query

In the query builder, drag a relationship line from your staff listing to your 2011 totals query to join the two. Then double-click on the join line itself, and a box should pop-up showing three options. You want to choose the ones that says something like "Include all the records from your staff listing and only the matching records from your 2011 totals query". If you do this properly, your join line will have an arrow on the end pointing to the 2011 totals query.

Now repeat the exact same process from your staff listing to the 2012 totals query.

Then, just select the fields you want from each object. Note that if you have people who appear in the 2011 totals, but not in the 2012, you will have a NULL value in that 2012 amount field for that person. If you wish to change that NULL to a zero, use the NZ function (see help for details).

[TSALauren]

It keeps telling me that the name can come from more than one source when I run the report. This is the sql.

SELECT Query1.[Workshop Data].[Staff Member].Value, [Query 2].SumOfHours, Query1.SumOfHours, Query1.[Staff Table].[TSA Staff Name]
FROM [Query 2], Query1;

[JoeM]

Two problems:
1. There should be three queries in your SQL code (I only see two).
2. You have not defined the relationships between your queries (read the instructions I posted in my previous post).

[TSALauren]

oops wrong one. It says that I have ambiguous outer joins if I try to run it.


SELECT [Staff Table].[TSA Staff Name]
FROM ([Staff Table] RIGHT JOIN Query1 ON [Staff Table].[TSA Staff Name] = Query1.[Workshop Data].[Staff Member].Value) RIGHT JOIN [Query 2] ON [Staff Table].[TSA Staff Name] = [Query 2].[Workshop Data].[Staff Member].Value;

[TSALauren]

am going to delete the queries and start over.

[JoeM]

Make sure that the fields you are joining are the exact same type (not trying to join Text to Numeric or vice versa). I mention that because I see that you have ".Value" after some of the field names.

[TSALauren]

ok. This is what I have now.

SELECT Query1.[TSA Staff Name], [Query2 2011].SumOfHours, [Query2 2012].SumOfHours
FROM (Query1 LEFT JOIN [Query2 2011] ON Query1.[TSA Staff Name] = [Query2 2011].[Workshop Data].[Staff Member].Value) LEFT JOIN [Query2 2012] ON Query1.[TSA Staff Name] = [Query2 2012].[Workshop Data].[Staff Member].Value;

[TSALauren]

the fields I am joining are the same, 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?