Building Query from other queries

A

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
 
J

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.
 
A

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



Jeff Boyce said:
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>

Alain said:
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
Click to expand...
Click to expand...
 
P

Peter R. Fletcher

I think that you missed Jeff's point! He was not intending to
compliment your design. Tables in a relational database will almost
always be structured very differently to the way you would handle
similar data in a spreadsheet. If your table structure looks like a
spreadsheet you are almost inevitably missing out on much of the
functionality of a database, as here.

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



Jeff Boyce said:
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>

Alain said:
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
Click to expand...
Click to expand...
Click to expand...


Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Nz function 0
Combining 3 queries 2
two queries from same data in table into one query 2
Need help trying to combine queries into 1 query 9
Problem with Query and count/sum/totals/calculation 2

Top