splitting an amount

[mohiahmed]

Hi there , I hope someone has the skills to help me with this problem.

I am trying to create a receipts database. Lets say my business has a few items you can purchase and lets call them: "Fees", "uniform", "books", "bags" and "experiences."



If someone came in and paid £2500 for their "fees", bought a "uniform" and also paid for an "experience", I would give only one receipt and on that receipt the total would be £2500. Could I somehow split the the £2500 for this record so that I can see £1500 went on fees, £150 on uniform, and £850 on experiences? The receipt number is the primary key so I don't want to split the data.

Any ideas how to do this?

[RayMilhon]

Only way to do this is to use 2 tables 1 for the Receipt Information and 1 for the details of the receipt.

For example
Receipt Table
ReceiptID PK
Purchaser
Date

ReceiptDetails
ReceiptID
DetailID
Product
QTY
Price

Then you link the 2 and sum the qty and price for the receipt.

This is simplistic to answer your question, but this is the first step.