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.

 

[alansidman]

You are going to need to have two tables. One with the gross information about the order and a second with the detailed information about the order. Look at this video and it will demo how to set up the form and sub form. You will probably want to not use a query as your record source for the subform as Mike does in the video, but the detailed table so that you can enter data appropriately. You should be able to glean enough info here to build your tables and then your forms.

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

Alan

 

[GopherUK]

Thank you very much indeed

 

[GopherUK]

Out of interest, are there any books or video courses on practical database design in Access you would recommend? I would like to sharpen my skills on real-world examples.

 

[alansidman]

Suggest you try to google MS Access Tutorials. You will find hundreds. Pick one or two and follow them.

Alan

 

[HiTechCoach]

Out of interest, are there any books or video courses on practical database design in Access you would recommend? I would like to sharpen my skills on real-world examples.

What I would recommend is studying Database Normalization theory. This applies to all relational database, not just Access. Some of the best material I have read was on sites not related to Access.

Here is a great start:
Database - Planning/Normalization

 

[alansidman]

Boyd: Great Link. Added to my favorites as I had not seen that one before.

Alan

 

[GopherUK]

That is a brilliant link HTC. Thank you both for your help. Absolutely invaluable.

 

[HiTechCoach]

You're welcome. Glad it was helpful.

 

[GopherUK]

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?