Order Details Table - Auto Number field ??

[Will]

When I set up the 3 tables...

tblOrders
tblOrder_Details
tblProducts

I put the id from both Orders Table & Products Table in the Orders Details
Table like this...

tblOrder_Details
idOrder number
idProduct number

My Questions...

1 - Do I need an idOrder_Details field in tblOrder_Details?
2 - If so should I make it an "auto number" field?
3 - Should I mark both idOrder and idProduct as "key fields" in
tblOrder_Details ??

thanks for any help.

 

[Baz]

When I set up the 3 tables...

tblOrders
tblOrder_Details
tblProducts

I put the id from both Orders Table & Products Table in the Orders Details
Table like this...

tblOrder_Details
idOrder number
idProduct number

My Questions...

1 - Do I need an idOrder_Details field in tblOrder_Details?
2 - If so should I make it an "auto number" field?
3 - Should I mark both idOrder and idProduct as "key fields" in
tblOrder_Details ??

thanks for any help.

1. I would suggest yes.

2. Yes, so long as you are aware of, and accept, the limitations of
autonumbers, which are primarily that:
(a) you can get gaps in the sequence when new records are started but not
saved, or records are deleted
(b) if such a "gap" appears at the "end" of the table, then the numbers
in question will get reused after the database is compacted

3. You can only designate one field as a key, and that's the primary key
(the id field). However, you should certainly create relationships between
those fields and the other tables, thereby making them, in effect, foreign
keys. You should also index them.

 

[Dirk Goldgar]

When I set up the 3 tables...

tblOrders
tblOrder_Details
tblProducts

I put the id from both Orders Table & Products Table in the Orders
Details Table like this...

tblOrder_Details
idOrder number
idProduct number

My Questions...

1 - Do I need an idOrder_Details field in tblOrder_Details?

If the pair of fields {idOrder, idProduct} between them will uniquely
identify a record, so that they can be made into a compound primary key,
then no, you don't. Otherwise, you do.

2 - If so should I make it an "auto number" field?

If you need such a field, then you'd probably make it an autonumber
field. But you may not need it; see my answer to (1).

3 - Should I mark both idOrder and idProduct as "key fields" in
tblOrder_Details ??

If they will uniquely identify a record, then I would do that -- make
them into a compound primary key.

 

[Dirk Goldgar]

3. You can only designate one field as a key, and that's the
primary key (the id field).

I'm sorry, but this is not true. It is common practice to create a
compound primary key where circumstances warrant. To do this via the
Access user interface, you select the two (or more) fields in table
design view (by shift-clicking or control-clicking) and then click the
"key" button.

I suspect that the difference between our answers is due to this
misunderstanding.

 

[Baz]

I'm sorry, but this is not true. It is common practice to create a
compound primary key where circumstances warrant. To do this via the
Access user interface, you select the two (or more) fields in table
design view (by shift-clicking or control-clicking) and then click the
"key" button.

I suspect that the difference between our answers is due to this
misunderstanding.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Of course, what I meant was "you can only designate one key on a table".

It hadn't occurred to me that the product might be unique within the order,
rather than simply being a foreign key, but only the OP knows whether that
is the case!