Sums Way to big

[Guest]

I have 2 queries one to get the data and another to group and sum it but for
some reason when try to sum the data for the second query the numbers are way
to big

the Sql for the 1st query is

SELECT Production.Batch, Production.Profile, Production.[Total Pieces],
Production.Preimum, Production.[Rerun Preimum]
FROM Production
GROUP BY Production.Batch, Production.Profile, Production.[Total Pieces],
Production.Preimum, Production.[Rerun Preimum], Production.Date
HAVING (((Production.Batch)=[Enter Batch Number]) AND ((Production.Date)
Between [Enter Start Date (mm/dd/yy) ] And [Enter End Date (mm/dd/yy)]));

And the sql for the one that is summing incorrectly is

SELECT CheckNumbers.Profile, Sum(CheckNumbers.[Total Pieces]) AS [SumOfTotal
Pieces], Sum(CheckNumbers.Preimum) AS SumOfPreimum, Sum(CheckNumbers.[Rerun
Preimum]) AS [SumOfRerun Preimum], Sum(tblImage.[Pieces Per Lift]) AS
[SumOfPieces Per Lift], Sum([CheckNumbers]![Preimum]+[CheckNumbers]![Rerun
Preimum]) AS TotalP, CheckNumbers.Batch
FROM Production, CheckNumbers INNER JOIN tblImage ON CheckNumbers.Profile =
tblImage.Profile
GROUP BY CheckNumbers.Profile, CheckNumbers.Batch, tblImage.Profile
HAVING (((tblImage.Profile)=[CheckNumbers.Profile]));


Any ideas would be great thanks

 

[twoodmore via AccessMonster.com]

Remove the Production table from the second query - it doesn't seem to be
used and the results will be multiplied by the number of records in the
Production table (i.e. the other results get matched with every record in the
Production table (because there is no join or where clause).

I have 2 queries one to get the data and another to group and sum it but for
some reason when try to sum the data for the second query the numbers are way
to big

the Sql for the 1st query is

SELECT Production.Batch, Production.Profile, Production.[Total Pieces],
Production.Preimum, Production.[Rerun Preimum]
FROM Production
GROUP BY Production.Batch, Production.Profile, Production.[Total Pieces],
Production.Preimum, Production.[Rerun Preimum], Production.Date
HAVING (((Production.Batch)=[Enter Batch Number]) AND ((Production.Date)
Between [Enter Start Date (mm/dd/yy) ] And [Enter End Date (mm/dd/yy)]));

And the sql for the one that is summing incorrectly is

SELECT CheckNumbers.Profile, Sum(CheckNumbers.[Total Pieces]) AS [SumOfTotal
Pieces], Sum(CheckNumbers.Preimum) AS SumOfPreimum, Sum(CheckNumbers.[Rerun
Preimum]) AS [SumOfRerun Preimum], Sum(tblImage.[Pieces Per Lift]) AS
[SumOfPieces Per Lift], Sum([CheckNumbers]![Preimum]+[CheckNumbers]![Rerun
Preimum]) AS TotalP, CheckNumbers.Batch
FROM Production, CheckNumbers INNER JOIN tblImage ON CheckNumbers.Profile =
tblImage.Profile
GROUP BY CheckNumbers.Profile, CheckNumbers.Batch, tblImage.Profile
HAVING (((tblImage.Profile)=[CheckNumbers.Profile]));

Any ideas would be great thanks

 

[Guest]

I need the pieces per lift to do a calculation on my form, pieces varys by
profile. I tryed moving the pieces per lift to the 1st query but the sum is
still way to large. Is there another way i could still get the correct value
for pieces per lift and still get the some to be correct

 

[John W. Vinson]

I need the pieces per lift to do a calculation on my form, pieces varys by
profile. I tryed moving the pieces per lift to the 1st query but the sum is
still way to large. Is there another way i could still get the correct value
for pieces per lift and still get the some to be correct

Is there some field in [Production] which matches some field in [Check
numbers]? I.e., is there any way to identify which record in Production
corresponds to a particular [Check number]?

Putting it another way: if you printed out both tables, one sheet of paper per
record, and were handed a stack of all the Production data and another stack
of all the Check Number data, how would you answer the question? If you can't
do it with the information there, then neither can Access!

John W. Vinson [MVP]

 

[Guest]

Yes they are linked by profile. and i have the profile sorted by the one that
matches my record. How every it still seems to be adding for each recod in
that table.

I need the pieces per lift to do a calculation on my form, pieces varys by
profile. I tryed moving the pieces per lift to the 1st query but the sum is
still way to large. Is there another way i could still get the correct value
for pieces per lift and still get the some to be correct

Is there some field in [Production] which matches some field in [Check
numbers]? I.e., is there any way to identify which record in Production
corresponds to a particular [Check number]?

Putting it another way: if you printed out both tables, one sheet of paper per
record, and were handed a stack of all the Production data and another stack
of all the Check Number data, how would you answer the question? If you can't
do it with the information there, then neither can Access!

John W. Vinson [MVP]

 

[Guest]

I just figured it out i didn't realise there was a link to the production
table. thanks for helping

Yes they are linked by profile. and i have the profile sorted by the one that
matches my record. How every it still seems to be adding for each recod in
that table.

I need the pieces per lift to do a calculation on my form, pieces varys by
profile. I tryed moving the pieces per lift to the 1st query but the sum is
still way to large. Is there another way i could still get the correct value
for pieces per lift and still get the some to be correct

Is there some field in [Production] which matches some field in [Check
numbers]? I.e., is there any way to identify which record in Production
corresponds to a particular [Check number]?

Putting it another way: if you printed out both tables, one sheet of paper per
record, and were handed a stack of all the Production data and another stack
of all the Check Number data, how would you answer the question? If you can't
do it with the information there, then neither can Access!

John W. Vinson [MVP]

 

[John W. Vinson]

Yes they are linked by profile. and i have the profile sorted by the one that
matches my record. How every it still seems to be adding for each recod in
that table.

Then you need to explicitly Join the two tables in the query. As written, you
have *no* join line between the tables, so Access is returning every possible
combination - for each record in CheckNumbers you're seeing every record in
Production and vice versa. The sort order of the tables is *absolutely
irrelevant* to this.

Try

SELECT CheckNumbers.Profile, Sum(CheckNumbers.[Total Pieces]) AS [SumOfTotal
Pieces], Sum(CheckNumbers.Preimum) AS SumOfPreimum, Sum(CheckNumbers.[Rerun
Preimum]) AS [SumOfRerun Preimum], Sum(tblImage.[Pieces Per Lift]) AS
[SumOfPieces Per Lift], Sum([CheckNumbers]![Preimum]+[CheckNumbers]![Rerun
Preimum]) AS TotalP, CheckNumbers.Batch
FROM (Production INNER JOIN CheckNumbers ON Production.Profile =
CheckNumbers.Profile) INNER JOIN tblImage ON CheckNumbers.Profile =
tblImage.Profile
GROUP BY CheckNumbers.Profile, CheckNumbers.Batch, tblImage.Profile;

I removed the HAVING clause since it's already taken care of in the Join on
tblImage.


John W. Vinson [MVP]

 

[Guest]

Thanks for your help. I fixed the joins in the tables it works perfectly now

Yes they are linked by profile. and i have the profile sorted by the one that
matches my record. How every it still seems to be adding for each recod in
that table.

Then you need to explicitly Join the two tables in the query. As written, you
have *no* join line between the tables, so Access is returning every possible
combination - for each record in CheckNumbers you're seeing every record in
Production and vice versa. The sort order of the tables is *absolutely
irrelevant* to this.

Try

SELECT CheckNumbers.Profile, Sum(CheckNumbers.[Total Pieces]) AS [SumOfTotal
Pieces], Sum(CheckNumbers.Preimum) AS SumOfPreimum, Sum(CheckNumbers.[Rerun
Preimum]) AS [SumOfRerun Preimum], Sum(tblImage.[Pieces Per Lift]) AS
[SumOfPieces Per Lift], Sum([CheckNumbers]![Preimum]+[CheckNumbers]![Rerun
Preimum]) AS TotalP, CheckNumbers.Batch
FROM (Production INNER JOIN CheckNumbers ON Production.Profile =
CheckNumbers.Profile) INNER JOIN tblImage ON CheckNumbers.Profile =
tblImage.Profile
GROUP BY CheckNumbers.Profile, CheckNumbers.Batch, tblImage.Profile;

I removed the HAVING clause since it's already taken care of in the Join on
tblImage.


John W. Vinson [MVP]