Many-toMany relationship

[Cyberstar]

I'm concerned that a junction table is not correct. There are 2 tables,
Order and Jobcode. An order can have many job codes. A job code can have
many orders. This requires a junction table. How can I create a junction
table that ensures the correct orders are 'mapped' to the correct job codes?

Thank you

 

[John Vinson]

I'm concerned that a junction table is not correct. There are 2 tables,
Order and Jobcode. An order can have many job codes. A job code can have
many orders. This requires a junction table. How can I create a junction
table that ensures the correct orders are 'mapped' to the correct job codes?

Ummm...

By creating a table with a two fields, foreign keys to the primary key
of Order and to the primary key of Jobcode.

You would update this table (typically) by using a Form based on one
of the "one" side tables; for instance, if you want to assign a
variety of jobcodes to a specific order, you would create a Form based
on the Order table with a subform based on the junction table, using
the OrderID as the master/child link field. On the Subform you would
have a control (typically a combo box) selecting valid Jobcodes.

What specific problem are you having?

John W. Vinson[MVP]

 

[Cyberstar]

Thanks for your reply.

I had a many-to-many relationship, requiring a junction table.

I have a couple of more basic questions.
1) Should all tables in a relational DB have at least one relationship?
2) How can I insert a record in the middle of the data sheet? Do I need to
add a new record and then sort?

Thanks once again

 

[Douglas J. Steele]

I have a couple of more basic questions.
1) Should all tables in a relational DB have at least one relationship?

It's not an absolute rule, but in general, yes, there should always be at
least one relationship.

2) How can I insert a record in the middle of the data sheet? Do I need to
add a new record and then sort?

Tables don't have an "order" to them. The description I like is "sacks of
data": the data's put wherever it fits. The only way to control the order of
records is to use a query with the appropriate ORDER BY clause.

 

[John Vinson]

Thanks for your reply.

I had a many-to-many relationship, requiring a junction table.

I have a couple of more basic questions.
1) Should all tables in a relational DB have at least one relationship?

It's not absolutely essential. For instance, I'll often have a
"settings" table with things like the database owner's company name, a
graphic of the company logo, their address etc.

Any table which is *logically* related to another should be related
with referential integrity enforced.

2) How can I insert a record in the middle of the data sheet? Do I need to
add a new record and then sort?

Two answers:

- Use Datasheets *ONLY* for debugging. They're not designed for data
entry or display! Use Forms (based on Queries) instead.

- Realize that a Table HAS NO ORDER. It's an unordered "heap" of data.
If you want to see data in a particular sequential order, create a
Query sorting the data, and use that Query as the recordsource for a
Form (for onscreen use) or Report (for printing).

John W. Vinson[MVP]