Calculate % in Union query

[THWatson]

Using Access 2000
Here is the SQL for a Union Query that combines data from other queries for 4 separate years.

SELECT  LastName,  EnvNbr, SumOfLocal,"2004" AS Year
FROM qry900s2004Local
UNION SELECT  LastName,[Env #], SumOfLocal,"2001"
FROM qry900s2001Local
UNION SELECT LastName, [Env #], SumOfLocal, "2002"
FROM qry900s2002Local
UNION SELECT LastName, [Env #], SumOfLocal, "2003"
FROM qry900s2003Local
ORDER BY EnvNbr, Year;

I want to be able to calculate the percent change from 2001 to 2004. Is there a way to add an additional field in this union query to do that? Or, alternatively, is there another way to accomplish it?

I can't do it in a Select query, because there are cases where an individual LastName has a SumOfLocal entry in 2004 but not in 2001, and vice versa, so the join doesn't pull all the records.

Thanks.

Tom

 

[r937]

percent requires knowledge of the overall sum

this is getting very ugly/clumsy very fast

you actually can do it in a select if you use left outer joins

how complex are the 4 individual queries you already have?

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[THWatson]

rudy
I agree with you that this is getting very ugly/clumsy very fast. I can get all the stuff for each individual year to show fine by using the union query and then basing a crosstab query on that. So far so good. But the percent won't work out in the report based on the crosstab query.

A left outer join in a select query won't show everything either. Say there are 58 entries in 2001 and only 53 in 2004, but an individual had no SumOfLocal in 2001, he/she won't show.

The problem is accentuated by the fact that in early 2004 I changed the program, so 2001, 2002 and 2003 have a little different structure.

In any event, you asked how complex are the individual queries. Here they are...

for the year 2004
SELECT tblTrinity.UniqueID, tblTrinity.LastName, tblNewGivings.EnvNbr, Sum(tblNewGivings.Local) AS SumOfLocal
FROM tblTrinity INNER JOIN (tblNewGivings INNER JOIN tblEnvelopeNumbers ON tblNewGivings.EnvNbr = tblEnvelopeNumbers.EnvNbr) ON tblTrinity.UniqueID = tblEnvelopeNumbers.UniqueID
GROUP BY tblTrinity.UniqueID, tblTrinity.LastName, tblNewGivings.EnvNbr
HAVING (((tblNewGivings.EnvNbr)>899))
ORDER BY tblNewGivings.EnvNbr;

for the year 2003
SELECT [Trinity(2003)].UniqueID, [Trinity(2003)].LastName, [New Givings(2003)].[Env #], Sum([New Givings(2003)].Local) AS SumOfLocal
FROM [Trinity(2003)] RIGHT JOIN [New Givings(2003)] ON [Trinity(2003)].[Env #] = [New Givings(2003)].[Env #]
GROUP BY [Trinity(2003)].UniqueID, [Trinity(2003)].LastName, [New Givings(2003)].[Env #]
HAVING ((([New Givings(2003)].[Env #])>899 And ([New Givings(2003)].[Env #])<1200))
ORDER BY [New Givings(2003)].[Env #];

for the year 2002
SELECT [Trinity(2002)].UniqueID, [Trinity(2002)].LastName, [New Givings(2002)].[Env #], Sum([New Givings(2002)].Local) AS SumOfLocal
FROM [Trinity(2002)] RIGHT JOIN [New Givings(2002)] ON [Trinity(2002)].[Env #] = [New Givings(2002)].[Env #]
GROUP BY [Trinity(2002)].UniqueID, [Trinity(2002)].LastName, [New Givings(2002)].[Env #]
HAVING ((([New Givings(2002)].[Env #])>899 And ([New Givings(2002)].[Env #])<1100))
ORDER BY [New Givings(2002)].[Env #];

for the year 2001
SELECT [Trinity(2001)].UniqueID, [Trinity(2001)].LastName, [New Givings(2001)].[Env #], Sum([New Givings(2001)].Local) AS SumOfLocal
FROM [Trinity(2001)] RIGHT JOIN [New Givings(2001)] ON [Trinity(2001)].[Env #] = [New Givings(2001)].[Env #]
GROUP BY [Trinity(2001)].UniqueID, [Trinity(2001)].LastName, [New Givings(2001)].[Env #]
HAVING ((([New Givings(2001)].[Env #])>899 And ([New Givings(2001)].[Env #])<1200))
ORDER BY [New Givings(2001)].[Env #];

Tom

 

[r937]

first things first, unless you are still accepting new historical data into your tables, why not save the results of those queries into their own tables, it will make things much easier

as for the left outer join, that's easy, just create a table called TheYears with one column called TheYear and with 4 rows, with the values -- you guessed it -- of 2001, 2002, 2003, 2004

then do your left outer joins starting with the TheYears table, so that every individual will be included...

... well, except for individuals who have no records in any of those 4 years



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[THWatson]

rudy
Thanks for the tips on approaches.

Failing that, and since this is a one-shot thing I was asked to work out, and since only 74 records are involved in this batch, I could also just do the % calculations by hand (but that seemed like too much work).

Thanks again. Sometimes one gets so mired in the details that it becomes difficult to step back and take a look through new glasses.

Tom

 

[r937]

you don't have to do them by hand, paste them into excel



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[THWatson]

rudy
Yep, thought of that too. But I think I will follow your tips and save the resulting tables, and then if they ask me a year from now to run this again and include 2005 data it will be easy to do.

Tom

 

[THWatson]

Rudy
I have tried your methods. Saving the results of the 4 individual queries into a table doesn't seem to make a difference as to the results I can obtain.
Then, making a TheYears table and trying to do a join from that doesn't quite do it either - it will produce a result for each record but, since the join is on TheYear, the records are individualized rather than related.

Part of the problem is that the field known as EnvNbr in 2004, or [Env #] in the other years, does not always apply to the same individual. (it would take me quite a bit of writing to explain this)

So, what I want to be able to do is produce a report that looks like this...
EnvNbr901 Jones 2001 2002 2003 2004 %change

The 2001, 2002, 2003 and 2004 would be $ contributions in those years. The %change would be from 2001 to 2004.

I can get a report to show all but the last column, the %change, by combining the data using a union query, and then using that union query as the basis for a crosstab query.

The crosstab query SQL is

TRANSFORM Sum(qry900sLOCALcombined.SumOfLocal) AS SumOfSumOfLocal
SELECT qry900sLOCALcombined.EnvNbr, qry900sLOCALcombined.LastName
FROM qry900sLOCALcombined
GROUP BY qry900sLOCALcombined.EnvNbr, qry900sLOCALcombined.LastName
PIVOT qry900sLOCALcombined.Year;

I have tried to calculate the %change in this crosstab query but since the columns (2001, 2002, etc.) are not defined before the query is run, the results won't show.

I would love to do it all in Access but it may be the case that I have to throw the results of the crosstab query into Excel to do the calculations.

Tom

 

[PHV]

And can't the report do the % calculation ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[r937]

... it will produce a result for each record but, since the join is on TheYear, the records are individualized rather than related."

i'm lost

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[THWatson]

PHV
The report doesn't seem to want to do the % calculation because it's based on the crosstab query...I guess because the fields in the report are derived rather than actual fields.

rudy
Sorry to lose you. I think I was vague in my explanation. When the query is run, all the 2001 results show first with blank columns for the other years, then the 2002 columns show with blanks under the already showing 2001 columns, and so on. So there end up to be 191 rows rather than a maximum of 74.
Hope that shows the picture.

Tom

 

[r937]

well, i'm confident i could do this if i had your mdb on my desktop, where i could figure stuff out as i go, but ...

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[THWatson]

Just to wrap this thread up, the procedure I finally used was this...

1. Make a table of the results of the Union query
2. Take the results into Excel to make the calculations
3. Import the spreadsheet results back into Access and make the report

Tom

 

[THWatson]

PHV and Rudy

A p.s. to my last post...

The main purpose of the exercise was to calculate the change in contributions made by individuals from 2001 through 2004. The principal reason that the normal procedures didn't work in this particular case was that some people made no contributions in 2001, while others made no contributions in either 2002, 2003 or 2004.

I could always get the results for the individual contributors in the respective years. However, the zero contributions in some years resulted in "division by zero" errors when I tried to calculate the percentage change in the report itself.

That's why, in the end, I had to transport things to Excel to do the calculations, and then bring the results back into Access.

Once again, thanks for your suggestions. I appreciated it.

Tom