Simple question about an orders table

[GopherUK]

Hi,

I was just wondering what is the best way to create an "orders" table so that I can enter multiple items in to a single order and have it so they will be all IDed as the same order in the table. Given that autonumber wont allow duplicate IDs, is the answer simply to have the form have fields to allow multiple entiries for products and then have corresponding fields in the table. Then just hope that the number of different items ordered does not exceed the number of fields?

Is there another solution? I can't see how else it would be possible to create a schema that would allow a query to get the details of all the items in a single order if they do not have the same primary key?

Thanks.

 

[HiTechCoach]

I've done a lot of reading now and I think I am coming along leaps and bounds.

One question I have... if you are using a database that has information in it regarding the stock a company has, is it better to...

a) Have a field in the table holding the information about the different items in stock that holds the actual number of items, which I assume would be adjusted using a macro when recording a new delivery of the item or shipping an order of the item in the database.

or...

b) Have a form/report that calculates the total in stock by subtracting the total number of that item shipped from the total number of that item delivered.

Or they both acceptable by the God of RDBMS?

The Best Practice method for determining Quantity on Hand is to calculate it as needed. This is normally done by summing the transaction records.

Note: Inventory control is one of the most difficult things to program with any development system.

see: Inventory Control: Quantity on Hand

 

[GopherUK]

I am pretty well schooled in VBA for Excel so I can decipher that VBA code easy enough I think. Thank you.

 

[GopherUK]

In this video...

http://www.datapigtechnologies.com/flashfiles/subforms1.html

... how will he getting the total for the order? In Excel I assume it would just be done with a SUMIF array formula. But I have no idea where to start with the same thing in Access.

 

[HiTechCoach]

Good luck with your project.