Single Transaction

Hi all

I am trying to make a sales form using ASP and MSAccess.

The problem is, how do I update the sales table with many items from the inventory(stuff that are bought in a single sales transaction, like one teddy bear and two snicker bars) and give them the same SaleID? The SaleID is generated automatically in Access.

Right now, each item that is entered to the sales table gets a new SaleID. Each item is entered one at a time. I want a way which would show (even if it is by adding an additional field to the table and not using the SaleID) that they are from the same transaction.

So how do I do this? Or is it possible at all?

Thanks in advance.

Hi Suzy,
You'd need a third table joining inventory and sales with a concatenated key of the primary key from the other two tables.

So you'd have (the * means key):

Inventory
* ItemID (autonumber)
ItemDetail

Sales
* SalesID (autonumber)
CustomerID

Sales_Items
* SalesID
* ItemID

Thanks numbernine.

But each item's sale is recorded on at a time to the sale table. Which means that each time a new SaleID is generated. If two items are sold in a single sale each item will be given a new SaleID, but I want a way which would show that they were bought in a single transaction. If I add a table, how would I show this? Can you please explain a little more?

Thanks again.

Hi Suzy,
When you have a sale, you would put a single transaction in your Sales table (no matter how many items were part of the transaction.) Then you would take the SalesID that Access generated and put that into your SalesItems table multiple times: one time for each item sold. So each sale would have its own unique number. So your data would look like: