Hello
I have this problem:
Table 1 : customers
Table 2 : Products

Every customer buy several kind of products during the weeks of the
year.
I would like to have , for every customer, the total of the products
bought ,for ANY product.
i.e.:
customer 1 TOTAL : 100 product1, 120 Product2, 200 Product3 ......

How can I solve this?

THank you

On 2010-11-22 10:07:20 -0800, giacomo Barlettani
<giacomobarlettani (AT) gmail (DOT) com> said:

Look up the concept of "join tables" to understand this.

You need Table 3: Purchases.

Each record has Customer ID, Product ID, and the quantity and date
purchased. Also the price if price is going to change over time.
Create the records through a portal in a Customer-based layout.

Every time a customer purchases an item, create a record in Purchases
recording date and product, quantity and price. Then, from the table
Purchases you can get the report you want. Sort by customer, then
product, and make subsummary parts.
--
Lynn Allen
--
www.semiotics.com
Member FBA
FM 10 Certified Developer

Since every customer might buy many products AND any product might be bought
by several customers you will need a many-to-many relationship.
To set these up you need a third table, called the join table.
when a user orders a product you create a new record (called the join
record)
this will hold ONLY two fields. skCustomerID and skProductsID
Join::skCustomerID should form a relationship with Customers:kCustomerID
same for the skProductsID

pk = primary key
sk = secondary key

After that it's not too difficult figuring out how much a customer has
spent. You need a bit more work, but my guess is you will first have to
figure out more about many-2-many relationships.

--

Hou je goed / keep well,

Ursus

"giacomo Barlettani" <giacomobarlettani (AT) gmail (DOT) com> schreef in bericht
news:a8e1692f-0b77-4461-a2ec-232f4b0e149c (AT) j25g2000yqa (DOT) googlegroups.com...