Analyzing two sets of data from two queries

[JITA]

Hi
I hope somebody can give some advise on how to handle my "challenge".
I have two tables with financial information over several years. I want to create a query that shows me data from two years in two seperate columns.
In order to achieve this I have created a union query that sums up the financial information per year per category.
Then I have created two queries based on this union query to retrieve the information per year (say 2010 and 2009).
Finaly I have created an other query that shows me in two columns the year 2010 and 2009 per catagory. This query is based on the two queries mentioned before.
I want to be more flexible on this queries by for instance changing the years to compare without changing the basic queries everytime. I am sure ther is a better / more efficient way to retrieve the data I need.
I have copied my queries into this thread. I would appreciate any thoughts.

Union Query called UqrySYM
SELECT tblGrb.ID, tblGrb.RekDisc, Format$([tblBkMs].[DateMut],'yyyy') AS [DateMut By Year], Format(Round(Sum(tblBkMs.Be),2),"Standard") AS [Sum Of Be], tblTypeR.RType
FROM tblTypeR INNER JOIN (tblGrb INNER JOIN tblBkMs ON tblGrb.ID = tblBkMs.GrbRek) ON tblTypeR.ID = tblGrb.TypeR
GROUP BY tblGrb.ID, tblGrb.RekDisc, Format$([tblBkMs].[DateMut],'yyyy'), Year([tblBkMs].[DateMut]), tblTypeR.RType
ORDER BY tblGrb.ID
UNION ALL SELECT tblGrb.ID, tblGrb.RekDisc, Format$([tblMml].[DateMut],'yyyy') AS [DateMut By Year], Format(Round(Sum(tblMml.Be),2),"Standard") AS [Sum Of Be], tblTypeR.RType
FROM tblTypeR INNER JOIN (tblGrb INNER JOIN tblMml ON tblGrb.ID = tblMml.GrbRek) ON tblTypeR.ID = tblGrb.TypeR
GROUP BY tblGrb.ID, tblGrb.RekDisc, Format$([tblMml].[DateMut],'yyyy'), Year([tblMml].[DateMut]), tblTypeR.RType
ORDER BY tblGrb.ID;

qryAMLY
SELECT UqrySYM.ID, UqrySYM.RekDisc, UqrySYM.[DateMut By Year], Sum(Round([Sum Of Be],0)) AS Be, UqrySYM.RType
FROM UqrySYM
GROUP BY UqrySYM.ID, UqrySYM.RekDisc, UqrySYM.[DateMut By Year], UqrySYM.RType
HAVING (((UqrySYM.[DateMut By Year])="2010") AND ((Sum(Round([Sum Of Be],0)))<>0) AND ((UqrySYM.RType)="V "));
qryAMLYO
SELECT UqrySYM.ID, UqrySYM.RekDisc, UqrySYM.[DateMut By Year], Sum(Round([Sum Of Be],0)) AS Be, UqrySYM.RType
FROM UqrySYM
GROUP BY UqrySYM.ID, UqrySYM.RekDisc, UqrySYM.[DateMut By Year], UqrySYM.RType
HAVING (((UqrySYM.[DateMut By Year])="2009") AND ((Sum(Round([Sum Of Be],0)))<>0) AND ((UqrySYM.RType)="V "));

Final query
SELECT tblGrb.ID, tblGrb.RekDisc, qryAMLY.Be AS 2010, qryAMLYO.Be AS 2009, -Nz([qryAMLY].[Be])+Nz([qryAMLYO].[Be]) AS Dif, IIf(Nz([qryAMLY].[Be])=0,1,IIf(Nz([qryAMLYO].[Be])=0,-1,([qryAMLY].[Be]/[qryAMLYO].[Be])-1)) AS AF
FROM (qryAMLY RIGHT JOIN tblGrb ON qryAMLY.ID = tblGrb.ID) LEFT JOIN qryAMLYO ON tblGrb.ID = qryAMLYO.ID
WHERE (((tblGrb.TypeR)=2) AND ((Nz([qryAMLY].[Be])+Nz([qryAMLYO].[Be]))<>0))
ORDER BY tblGrb.ID;

 

[CindyK]

Describing this solution is a bit of a pain, but let me try.
1) On a form, enter the year that you want to START with - ie 2009.
2) Take one of your queries (currently used in the union), but in place of the Year = "2009", use the form value. (If your form was called frmDateConsole, and the unbound text box is called unbYr, then your query would read something like ...
HAVING ((UqrySYM.[DateMut By Year]) = Forms!frmDateConsole.unbYr

3) Copy and paste that query, give it a name that indicates it is one year later - and this time you HAVING clause will look like this:

HAVING ((UqrySYM.[DateMut By Year]) = (Forms!frmDateConsole.unbYr + 1)

(You might want to add the 1 to the year right on the form in another hidden text box instead - I've not tested whether the addition will work okay in the having clause.)

4) Now create another query (simple select, not totaled) that joins those two queries on the DateMut By Year field. After making the join, you'll have to switch to the SQL view to change the operator to be a < instead of an equal (or a > depending on which one you start with).

Your output fields for that query will be include the total fields from BOTH YEARS - labeled something like "StartYr" and "Next Yr" (avoiding the actual years because then you lose the flexibility). If you make a report out of this, you can use a formula to display the years as a label.

This example is much simpler - but the final SQL looks like this - should help give you an idea of where I'm going.

SELECT qryOneYrBack.SumOfsngUnits AS OneYrAgo, qry2YrsBack.SumOfsngUnits AS TwoYrAgo
FROM qry2YrsBack INNER JOIN qryOneYrBack ON qry2YrsBack.Yr < qryOneYrBack.Yr;

Add a join on any other fields that are always the same - ie it looks like the RType field and maybe your RekDisc fields would be a join in your final query.

Hope this helps.

 

[dhookom]

I would start by trying SQL like this for your initial query. Ideally the [Enter Year] should be replaced by a reference to a control on a form since I detest parameter prompts in queries.

SELECT tblGrb.ID, tblGrb.RekDisc, tblTypeR.RType,
Sum(tblBkMs.Be * Abs(Year(dateMut)= [Enter Year])) AS [SumOfCurr], 
Sum(tblBkMs.Be * Abs(Year(dateMut)= [Enter Year]-1)) AS [SumOfPrev]
FROM tblTypeR INNER JOIN (tblGrb INNER JOIN tblBkMs ON tblGrb.ID = tblBkMs.GrbRek) ON tblTypeR.ID = tblGrb.TypeR
GROUP BY tblGrb.ID, tblGrb.RekDisc, tblTypeR.RType
ORDER BY 1

Duane
Hook'D on Access
MS Access MVP

 

[JITA]

@dhookom
Thanks for your reply. This is exactly what I was looking for.

@CindyK Thank you too.