Question

Locked

Query Calc is Multiplying Answer by Rows?

By noah3play ·
I've almost completed a database for my company to process Credit
Memo's. However, I have an issue which I've run out of options to
solve....
I have 11 rows of data in my table called 'Credit Memo Request'. In
this table I have a field for Customer #, Customer Name,......,Part #
1-10, Quantity 1-10, Item Amount 1-10. (Part #, Quantity, & Item
Amount are one line item used to calculate one credit. You can have
up to 10 line items per Customer #.) I don't have any totals in the
table, as I've read that's a no no. However, I want some of my
reports to contain totals. (We want to know what our credits are each
month:)
Therefore, I'm running a query, called 'Part # Totals', with 11
columns. 10 columns calculating the total of each Part#. I've named
each calculation Total#1, Total#2, etc. There is also 1 column called
'Grand Total' which is summing the 10 'Total#1, Total#2, etc'
columns. The following is the calculation which is contained in each
of the 10 'Total #' columns:
Total#1: [Credit Memo Request]![Quantity 1]*[Credit Memo Request]!
[Item Amount 1]
The following is the 'Grand Total' calculation:
Grand Total: Nz([Total#1],0)+Nz([Total#2],0)+Nz([Total#3],
0)+Nz([Total#4],0)+Nz([Total#5],0)+Nz([Total#6],0)+Nz([Total#7],
0)+Nz([Total#8],0)+Nz([Total#9],0)+Nz([Total#10],0)
The query is multiplying the rows from the source table (11) by the #
of 'Total#' columns (10) in my query. I end up with 121 rows of data
in the query. I should only end up with 11.
What am I doing wrong?

This conversation is currently closed to new comments.

1 total post (Page 1 of 1)  
Thread display: Collapse - | Expand +

All Answers

Collapse -

Bit hard to see from you explanation.

by Tony Hopkinson In reply to Query Calc is Multiplying ...

You appear to have managed a cartesian product, which says missing join or nothing to join

For that to make sense you must have two tables some how. I suspect access is joining select * From Credit ... and the Part# Totals query.

Do it in one go

Select
[Customer #], [Customer Name],
[Part #1] [Quantity #1],[Item Amount #1], [Quantity #1] * [Item Amount #1] as [Total #1], Repeat for 2 - 10,
,(([Quantity #1] * [Item Anount #1]) + .... ([Quantity #10 * [Item Amount #10])) as [Grand Total]
From [Credit Memo Request]

When you've written this lot out you'll realise your design is possibly not the best you could have come up with.

Suggest you look up cross tabulation.

Back to Web Development Forum
1 total post (Page 1 of 1)  

Related Discussions

Related Forums