Summing fields

[Guest]

I'm having a table with the fields of 2005Q1FC, 2005Q1AC, 2005Q2FC, 2005Q2AC
for value that correspond to forecasts and actual values for a given quarter.
Now I have created a query and the SQL statement looks like this;

SELECT Data.Customer, Data.Product,
Sum([Data]![2005Q1AC]+[Data]![2005Q2AC]+[Data]![2005Q3AC]+[Data]![2005Q4AC])
AS Expr1
FROM Data, RoyaltyIntervalls
GROUP BY Data.Customer, Data.Product;

To be honest I have never really learned how to use calculations in querys
and I guess I'm missing some fundamentals in properties of the values etc.
This is probably why I get a result of 195 as the first sum and not the 5
which I manually can see it should be. Does anyone know what I do wrong? I
would also appreciate it if someone could provide me with a link of how to
handle queries in general.

Many thanks in advance / Bell

 

[Michel Walsh]

Hi,


Try:


SELECT Customer, Product
SUM( [2005Q1FC] )+SUM([2005Q1AC] )+SUM( [2005Q2FC] )+SUM( [2005Q2AC] )
FROM data
GROUP BY Customer, Product



Do not bring table RoyaltyIntervals.

When you use

SELECT data.*, royaltyIntervals.*
FROM data, royaltyIntervals


you should see that originals records are duplicated many times (because of
the Cartesian join). In fact, if there are n records in data and m record in
royaltyIntervals, that makes n*m records in the result. That was the main
problem: you brought a table when you didn't need it...



Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Thank you Mike it solved my problem but I still can't find a solution to the
problem that I'm really trying to solve. Please see my latest question.

Best regards, Bell

"Michel Walsh" skrev:

Hi,


Try:


SELECT Customer, Product
SUM( [2005Q1FC] )+SUM([2005Q1AC] )+SUM( [2005Q2FC] )+SUM( [2005Q2AC] )
FROM data
GROUP BY Customer, Product



Do not bring table RoyaltyIntervals.

When you use

SELECT data.*, royaltyIntervals.*
FROM data, royaltyIntervals


you should see that originals records are duplicated many times (because of
the Cartesian join). In fact, if there are n records in data and m record in
royaltyIntervals, that makes n*m records in the result. That was the main
problem: you brought a table when you didn't need it...



Hoping it may help,
Vanderghast, Access MVP

I'm having a table with the fields of 2005Q1FC, 2005Q1AC, 2005Q2FC,
2005Q2AC
for value that correspond to forecasts and actual values for a given
quarter.
Now I have created a query and the SQL statement looks like this;

SELECT Data.Customer, Data.Product,
Sum([Data]![2005Q1AC]+[Data]![2005Q2AC]+[Data]![2005Q3AC]+[Data]![2005Q4AC])
AS Expr1
FROM Data, RoyaltyIntervalls
GROUP BY Data.Customer, Data.Product;

To be honest I have never really learned how to use calculations in querys
and I guess I'm missing some fundamentals in properties of the values etc.
This is probably why I get a result of 195 as the first sum and not the 5
which I manually can see it should be. Does anyone know what I do wrong? I
would also appreciate it if someone could provide me with a link of how to
handle queries in general.

Many thanks in advance / Bell