Query Calc. is Mult. # of Table rows--Y?

[StaceyF]

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?

 

[John W. Vinson]

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

Sorry. Your table design IS WRONG. You're "committing spreadsheet upon a
database"! There's an old saying that "fields are expensive, records are
cheap".

If each Credit Memo record has up to ten line items... or two line items for
that matter - you have a One (memo) to Many (items) relationship. The correct
way to model this is with *a second table*, MemoItems; this would have fields
for PartNo (don't use # in fieldnames, it's a date delimiter), Quantity, and
ItemAmount (don't use blanks in fieldnames either), and a foreign key to the
Primary Key of the Credit Memo table.

If a given credit memo has four items, you'ld add four rows to this table
(using a continouous Subform with one row per item); if you ever need *twelve*
items, you would just add twelve records. You can very easily sum the credits
using a totals query because there's just one field.


John W. Vinson [MVP]

 

[StaceyF]

Sorry. Your table design IS WRONG. You're "committing spreadsheet upon a
database"! There's an old saying that "fields are expensive, records are
cheap".

If each Credit Memo record has up to ten line items... or two line items for
that matter - you have a One (memo) to Many (items) relationship. The correct
way to model this is with *a second table*, MemoItems; this would have fields
for PartNo (don't use # in fieldnames, it's a date delimiter), Quantity, and
ItemAmount (don't use blanks in fieldnames either), and a foreign key to the
Primary Key of the Credit Memo table.

If a given credit memo has four items, you'ld add four rows to this table
(using a continouous Subform with one row per item); if you ever need *twelve*
items, you would just add twelve records. You can very easily sum the credits
using a totals query because there's just one field.

John W. Vinson [MVP]

I know my naming conventions are not correct. This is my first
database and I caught onto that about 3/4 of the way through
Would a total query allow me to see the individual record the total is
attached to?
I know this may sound naive, but what difference is 2 tables going to
make? It's still the same amount of data as 1 table?
Thanks...Stacey

 

[John W. Vinson]

I know my naming conventions are not correct. This is my first
database and I caught onto that about 3/4 of the way through

I wouldn't worry too much about the naming conventions. If you *always*
enclose field names in [square brackets] you can get away with a lot - blanks,
# marks, etc. If you ever need to migrate to SQL/Server though it'll be a
major pain renaming the fields or writing complex SQL; and there's a real risk
of Access getting confused.

Would a total query allow me to see the individual record the total is
attached to?

Sure. You'ld just group by that record.

I know this may sound naive, but what difference is 2 tables going to
make? It's still the same amount of data as 1 table?

Take a look at the "Database Design 101" links on Jeff's website: there are
good explanations there, better than I could explain and far longer than I
would want to type...

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

John W. Vinson [MVP]