Multiple autonumbers in a many to many relationship

[Elizabeth]

I have one table named Jobs and one named Customers. We sometimes build
more than one job per customer and occasionally have more than one customer
per job. I have created a join table to permit the many-to-many relationship.
The problem I have is that I can only have one autonumber per table.
The JobID is text due to how we name our jobs. However, I need to have
unique numbers for both the CustomerID and the CustomerJobID.
If anyone has any suggestions on how to accommodate this, I would
HIGHLY appreciate it. I am at a complete loss as to how to fix this.

 

[Duane Hookom]

Assuming your customer table has an Autonumber primary key of CustomerID, if
you want to use this value in related tables, the data type would be Numeric
Long (not autonumber). I'm not aware of any situation requiring or desiring
more than a single autonumber in a table.

 

[Steve]

TblCustomer
CustomerID (autonumber)
<Customer Fields>

TblJob
JobID (autonumber)
JobDescription
<Other Job Fields>

TblCustomerJob
CustomerJobID (autonumber)
CustomerID (Number data type - long integer)
JobID (Number data type - long integer)
CustomerJobDate

Steve
(e-mail address removed)

 

[Fred]

Elizabeth, ther's one item in you post which Duane's post did not address
and Steve's structure conflicted with.

Your JobID fields in Job table and your junction table will be of the TEXT
type.


Fred

 

[TedMi]

In addition, if your business rules stipulate that a customer can be
assigned to a job only once, create a compound unique index on the two
fields CustomerID and JobID
-TedMi