Building Query from other queries

[Alain]

Hi to All,

I am trying to build a query based on 4 other queries but getting strange
results, my 4 basic queries are returning the sum of monthly results per
year which by themselves are working great, all the retuned numbers are
good, here is the example:

SELECT Projection.IdBranch, Projection.Annee, Projection.ModeDeCalcul,
nz([Projection].[Avril],0)+nz([Projection].[Mai],0)+nz([Projection].[Juin],0)+nz([Projection].[Juillet],0)+nz([Projection].[Aout],0)+nz([Projection].[Septembre],0)+nz([Projection].[Octobre],0)+nz([Projection].[Novembre],0)+nz([Projection].[Decembre],0)+nz([Projection].[Janvier],0)+nz([Projection].[Fevrier],0)+nz([Projection].[Mars],0)
AS YearSum
FROM Projection
WHERE (((Projection.Variante)="Basic Rental"))
ORDER BY Projection.IdBranch, Projection.Annee;

My problem is when I want to build a query that returns the sum of the that
need to return the sum of all 4 queries on a yearly basis, I am not getting
the rights results, here is my query:

SELECT [Q-ProjSumRent].IdBranch, [Q-ProjSumRent].ModeDeCalcul,
Sum([Q-ProjSumRent].[YearSum]+[Q-ProjSumBTV].[YearSum]+[Q-ProjSumOther].[YearSum]+[Q-ProjSumOMT].[YearSum])
AS YearSum
FROM (([Q-ProjSumBTV] RIGHT JOIN [Q-ProjSumRent] ON [Q-ProjSumBTV].IdBranch
= [Q-ProjSumRent].IdBranch) LEFT JOIN [Q-ProjSumOther] ON
[Q-ProjSumRent].IdBranch = [Q-ProjSumOther].IdBranch) LEFT JOIN
[Q-ProjSumOMT] ON [Q-ProjSumRent].IdBranch = [Q-ProjSumOMT].IdBranch
GROUP BY [Q-ProjSumRent].IdBranch, [Q-ProjSumRent].ModeDeCalcul,
[Q-ProjSumRent].Annee
ORDER BY [Q-ProjSumRent].IdBranch;

the tight results should be 57722 for the first idbranch for the first year
but i am getting 1558701
I have tried many combination of relationship, one-to-one, one-to-many but
still getting wrong results.
Can anybody let me know what I am doing wrong ???

Thanks

Alain

 

[Jeff Boyce]

Alain

If you have four queries that each return a yearly total, you could use a
UNION query to put all of the query results into one query, then run a
Totals query against that UNION query.

I'll point out that the example you provided implies that you have designed
a table that is quite suitable ... if it were a spreadsheet! Having
separate columns for each month is necessary in a spreadsheet, but fails to
use the relational features and strengths of Access.

You may want to look into further normalizing your data before proceeding.
I mention this because a more-fully normalized design would keep all the
information you need in two columns, one for the data, one for the date of
that data. A summation of 4 years (or one) is a simple Totals query, with a
date range selection criterion.

 

[Alain]

Thanks Jeff,

I will try the UNION query,
the tables I have desing is very suitable for a spreadsheet, each month is
on a seperate column ( field in Access) and all the data per year ( 12
months ) is on a recordset, so design wise I think is pretty good

thanks for the tips

Regards

Alain

Alain

If you have four queries that each return a yearly total, you could use a
UNION query to put all of the query results into one query, then run a
Totals query against that UNION query.

I'll point out that the example you provided implies that you have
designed
a table that is quite suitable ... if it were a spreadsheet! Having
separate columns for each month is necessary in a spreadsheet, but fails
to
use the relational features and strengths of Access.

You may want to look into further normalizing your data before proceeding.
I mention this because a more-fully normalized design would keep all the
information you need in two columns, one for the data, one for the date of
that data. A summation of 4 years (or one) is a simple Totals query, with
a
date range selection criterion.

--
Good luck

Jeff Boyce
<Access MVP>

Hi to All,

I am trying to build a query based on 4 other queries but getting strange
results, my 4 basic queries are returning the sum of monthly results per
year which by themselves are working great, all the retuned numbers are
good, here is the example:

SELECT Projection.IdBranch, Projection.Annee, Projection.ModeDeCalcul,
nz([Projection].[Avril],0)+nz([Projection].[Mai],0)+nz([Projection].[Juin],0
)+nz([Projection].[Juillet],0)+nz([Projection].[Aout],0)+nz([Projection].[Se
ptembre],0)+nz([Projection].[Octobre],0)+nz([Projection].[Novembre],0)+nz([P
rojection].[Decembre],0)+nz([Projection].[Janvier],0)+nz([Projection].[Fevri
er],0)+nz([Projection].[Mars],0)
AS YearSum
FROM Projection
WHERE (((Projection.Variante)="Basic Rental"))
ORDER BY Projection.IdBranch, Projection.Annee;

My problem is when I want to build a query that returns the sum of the that
need to return the sum of all 4 queries on a yearly basis, I am not getting
the rights results, here is my query:

SELECT [Q-ProjSumRent].IdBranch, [Q-ProjSumRent].ModeDeCalcul,
Sum([Q-ProjSumRent].[YearSum]+[Q-ProjSumBTV].[YearSum]+[Q-ProjSumOther].[Yea
rSum]+[Q-ProjSumOMT].[YearSum])
AS YearSum
FROM (([Q-ProjSumBTV] RIGHT JOIN [Q-ProjSumRent] ON [Q-ProjSumBTV].IdBranch
= [Q-ProjSumRent].IdBranch) LEFT JOIN [Q-ProjSumOther] ON
[Q-ProjSumRent].IdBranch = [Q-ProjSumOther].IdBranch) LEFT JOIN
[Q-ProjSumOMT] ON [Q-ProjSumRent].IdBranch = [Q-ProjSumOMT].IdBranch
GROUP BY [Q-ProjSumRent].IdBranch, [Q-ProjSumRent].ModeDeCalcul,
[Q-ProjSumRent].Annee
ORDER BY [Q-ProjSumRent].IdBranch;

the tight results should be 57722 for the first idbranch for the first year
but i am getting 1558701
I have tried many combination of relationship, one-to-one, one-to-many
but
still getting wrong results.
Can anybody let me know what I am doing wrong ???

Thanks

Alain