Which way should I go?

[aian]

Hi to all!

I have a question that I can't find a logical answer to, especially after 3 days of thinking, I think I can't think anymore (

So here's what's going on:

I have a Clients Table (*CLIENT_ID, NAME, SURNAME)
I have a Products Table (*PRODUCT_ID, DESCRIPTION)
I have a Charges Table (*CHARGE_ID, *CLIENT_ID, PRODUCT_ID, DATE, AMOUNT)

(All those with a *star are marked as Primary Keys)

The problem is that I can't figure out how to create a form (maybe with a subform in it?) that will store to the Charges Table (or ANY other table, please suggest by all means) a purchase done by a client, on a specific date, that includes multiple products, and how much each product was charged for. I can't figure out how to have the same CHARGE_ID for several purchases of the same client the same date.

I tried creating a form based on the fields of the Charges Table, but this creates (well... obviously) a separate record for each purchase of the same client for the same day. That's not what I want, because I have to get the sum of all the purchases' costs, so as to tell the client how much he has to pay and I can't do that.

On the other hand, I tried creating a form based on the fields of the Client's Table, and insert a subform based on the Charges Table. Still nothing.

Can you please tell me the way you would approach the problem? What have I planned wrong?

Thanks in advance

Alexander

 

[KenHigg]

Seems you need something like an order table. The order table would be tied to the customer table where a customer can have multiple orders. The orders table would be tied to the charges table where the order has multiple charges. And a charge would have a one to one to the products table...

 

[aian]

Thanks Ken for your prompt reply.

If that's not too much, can you please suggest which fields the order table should include?

Furthermore, can I have multiple purchases for a single date this way?

 

[KenHigg]

The entities (tables) and thier relationships (the data model) should mirror your business as close as possible. In other words - I don't know how you do business. When you sell something is it sold on an order with multiple items per order. Can each item on the order be discounted or do you discount the entire order, etc., etc. I suggest you write out a set of business rules and prioritize it. You should find things you can live with and things you must have.

 

[aian]

Ok, thank you, I'll try my best )