create total counts from different fields

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

My product table in Access 2003 includes 80 fields of product, their count
and sales amount from each of 43 locations. The 80 products listed need to
be totaled in their respective groups (not listed) for each location. How
can several different products be selected (8" red cable, 8" green cable, 8"
blue cable) in order to create a total count of each group (8" cables)
selected? There are about 21 different groups to be counted from the 80
fields.
I am guessing that the 21 groups somewhere need to be created and the 80
products assigned to them in order to get the sum of each group by each
location.
How do I accomplish this?
Thank you for your help.
 
You need to change the structure of your database from spreadsheet layout to
a relational database. You should not have the 80 fields but 80 records, one
for each product.
Product --
ProdID - autonumber - primary key
Name - text
Group - text
Price - number - single

Location --
LocID - autonumber - primary key
Name - text
Phone - text
Addr1 - text
Addr2 - text
City - text
ST - text
Zip - text
ZipPlus - text

Sales--
SaleID - autonumber - primary key
ProdID - number - integer - foreign key
LocID - number - integer - foreign key
Sale - DateTime
QTY - number - integer
Rmrks - text

Product and Location have a one-to-many relation to Sales. Use form/subform
to enter location sales.
 
Back
Top