I am creating a table to track order details and I will have multiple quantities of the same item on an order. I want to be able to enter an item number and a quantity in a form and generate that number of identical entries in the table but I'm not sure how to go about it. A push in the right direction would be greatly appreciated.

Thanks

Can you post more detail on your table structure?

Purchase Orders:
purchaseOrderID (PK)
vendorID
purchaseOrder
dateOrdered
dateReceived
shippingMethod
shippingCost
orderCategory

Order Details:
detailID (PK)
purchaseOrderID
itemName
itemDescription
itemPrice

Order Form:
purchaseOrder
dateOrdered
shippingMethod
shippingCost
orderCategory
itemName
itemDescription
itemPrice
itemQty



Edited by: bstonehill on Mon Oct 2 16:20:57 EDT 2006.

It looks like you need to work normalizing your tables. I'm by no means an expert on normalization but hopefully I can at least point you in the right direction.

There's some links in this thread that have been very helpful to me.

I might do the structure a little more like this.

tblVendor
VendorID (APK)
VendorName
VendorAdd
etc...

tblCustomer
CustomerID (APK)
CustomerName
CustomerAdd
etc...


tblShipMethod
ShipMethID (APK)
ShipMethName
ShipMethCost (this is going on an assumption that there is a fixed cost related to a given shipping method, any calculations on cost, or any calculations for that matter, should be done at runtime and never stored in a table)
etc...

tblOrderCatagory
OrderCatID (APK)
OrderCatagory
etc...

tblItem
ItemID (APK)
ItemName
ItemDescription
ItemPrice
etc...

tblOrderMain (This will be the parent table)
OrderID (APK)
PONumber
OrderCatID (FK linked to ShipMethID in tblShipMethod)
CustomerID (FK linked to CustomerID in tblCustomer)
DateOrdered
etc...

tblOrderLineItem (This will be the child table)
LineItemID
OrderID (FK linked to OrderID in tblOrderMain)
ItemID (FK linked to ItemID in tblItem)
VendorID (FK linked to VendorID in tblVendor)
ShipMethID (FK linked to ShipMethID in tblShipMethod)
OrderQuantity (for the particular item)
DateReceived (I put this in the line item in case different products on one order are shipped seperately and arrive on different days.)
etc...

Anyway, that's how I'd set it up. Again, by no means am I normalization expert and some of the regulars might say I don't know what I'm talking about. If they do, believe them! (IMG:http://www.utteraccess.com/forum/style_emoticons/default/sad.gif)

Hope that helps!

Steve

Edited by: SWasko on Thu Oct 5 9:33:02 EDT 2006.

Very helpful, thanks!

No problem. Glad I could help.

Good luck!