# Web Development

## Question

Locked

### Query Calc is Multiplying Answer by Rows?

By ·
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.

Thread display: Collapse - | Expand +

Collapse -

### Bit hard to see from you explanation.

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

## Related Discussions

• 12

• 23

• 5

#### Javascript will not work in child theme with wordpress

spencer1621515293 ·

• 3