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;
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;