Creating a junction table- PK two autonumbers?

[Maria J]

I have a straightforward question: do I have to manually create a junction table in Access 2007 for a M to M relationship if I'm going to have as PK, two FK which are Autonumbers from the linked tables?
I can't see how Access will know "on it's own" how the junction table goes.
I'm going to use this junction table for linking a clients table (with PK client ID Autonumber) and a services table (with PK service ID Autonumber).
Thanks in advance

 

[boblarson]

The junction table would have
ClientsService - Autonumber (PK)
clientID - Long Integer (FK)
ServiceID - Long Integer (FK)

And normally (in this case, based on your description) you would have the Client table be the basis of the main form and then the subform would be the junction table and you the Master/Child links would be on clientID and you would set serviceID by using a combo box to select the applicable services for the client. Having the clientID set as the master/child link will automatically add the reference to clientID in the junction table.

 

[neileg]

I don't normally disagree with Bob, but I wouldn't bother with the ClientsService PK. The combination of the Two FKs will give you a PK.

 

[gemma-the-husky]

the original question was "how would access know on its own...?"

well of course it doesnt - but say you have a single table with repeating groups, then what you are doing is splitting the table - normalising

so within the single table, you have data that includes services, groups, and their relationship

so because access cant model a n-n relationship, you are breaking this into a series of 1-n relationships

clients 1-n junction n-1 services

and you end up with

a) clients table
b) services table
c) a junction/relationship table joining the two

---------
now in terms of access knowing how construct this then you have two ways of going

either
a) from the point of view of the clients, show the services relevant to each client - in which case access will automatically set the client for the appropriate service, if you deal with this in a form (client)/subform (service)

or

b) from the point of view of the services, show the clients using each service - in which case access will automatically set the service type for the appropriate client

 

[boblarson]

I don't normally disagree with Bob, but I wouldn't bother with the ClientsService PK. The combination of the Two FKs will give you a PK.

Disagreeing is okay. And I'll tell my side of it. I do not like composite keys - PERIOD. So I will not use them unless forced to. If I need to get a specific record from this table, and I've done it your way Neil, I need TWO fields instead of one to do so. I don't like it and never have and so I always have a primary key field in my tables and always a surrogate key, never a composite key. That is my reason.

 

[neileg]

Disagreeing is okay. And I'll tell my side of it. I do not like composite keys - PERIOD. So I will not use them unless forced to. If I need to get a specific record from this table, and I've done it your way Neil, I need TWO fields instead of one to do so. I don't like it and never have and so I always have a primary key field in my tables and always a surrogate key, never a composite key. That is my reason.

Yes I understand and I would usually use a surrogate key too. If the junction table contains other data, then I would use one here. I think I'm talking myself out of my own advice...

(Walks away muttering)

 

[Maria J]

I understand the M-N relationship between the clients table and services but what I don't know is how to physically create the junction table in the Relationship window. Is creating the two tables Clients and services enough, or do you have to physically create the junction table: how do I populate that? I don't know how to tell Access to grab the Client ID (autonumber) and the Service ID (autonumber).

 

[boblarson]

I understand the M-N relationship between the clients table and services but what I don't know is how to physically create the junction table in the Relationship window. Is creating the two tables Clients and services enough, or do you have to physically create the junction table: how do I populate that? I don't know how to tell Access to grab the Client ID (autonumber) and the Service ID (autonumber).

You have to physically create the junction table and then join the primary key of the ClientID (primary key) to the ClientID (foreign key) in the junction table and then the ServiceID (primary key) to the ServiceID (foreign key) to the junction table.

See attached a very simple mockup for you. Check out the relationships, the tables and the forms.

 

[Atomic Shrimp]

I don't like composite keys either, but I think this might be a case where they are worth having. If ClientID + ServiceID are used to form the PK, it will prevent any client being assigned the same service twice.

I might still have an autonumber field just for record identity - so I can point and say "that record there" without ever worrying about ambiguity.

 

[boblarson]

I don't like composite keys either, but I think this might be a case where they are worth having. If ClientID + ServiceID are used to form the PK, it will prevent any client being assigned the same service twice.

I might still have an autonumber field just for record identity - so I can point and say "that record there" without ever worrying about ambiguity.

I would not use a composite key for this, but instead use the surrogate key but set a Multi-field index to keep duplicates out.

 

[Atomic Shrimp]

Fair enough - that also works.

I don't think a composite key would hurt here though, given that it would be based on two entirely synthetic foreign keys (if it was a composite of client account number and some human-readable service code, I would be dead against it)

 

[amyofficepro]

I'm having a somewhat simular issue, I think...

Employee Training Database.. need to get results from Employees, Training Courses, Classes, to show all Training Courses required for that Employee Position whether or not they took a Class/ enough Class Duration... <Who took what/Who needs what?>

I just joined yesterday - Please reference my post yesterday June 13, 2012 05:38pm "EMPLOYEE TRAINING DATABASE OUTPUT TROUBLE..."

THANK YOU IN ADVANCE FOR HELP!

 

[Pat Hartman]

Most of my tables use surrogate keys primarially because good natural keys are hard to come by. For junction tables, I use the two FK's as a composite PK UNLESS, the junction table has dependent tables. If there are no dependent tables there would NEVER be an occassion to join to the junction table on both FKs so the composite PK doesn't cause composite FKs. You would always be joining on one or the other depending on what path you are following through the data. If I establish the table this way initially and determine later on that I actually have a dependent table, I add the surrogate key at that point. No change to anything is required because nothing would ever have used the 2-field composite key.

 

[zubersurati]

hi,

I have question here please. In my situation junction table will have relation with other table in Db. Also want that junction table will have one and only one record with fk1+fk2.
so creating junction table
tblTest (testId pk auto, fk1,fk2) should one other index unique via Electric rod Icon
make unique either fk1 or fk2..?
is this correct ?
Thanks
zs

 

[RainLover]

If you are going to set standards then stick to them. If you need to swap and change there there is something wrong with the standard you set yourself.

Bob Larson has set a standard and sticks to it.

I would and do agree with Bob.

 

[Pat Hartman]

I always look before I cross a street but I might violate that standard also if I were being pursued by a herd of stampeding turtles

zs,
If you go with the surrogate PK, then you need a unique composite index that includes both the FK fields to enforce the business rules.

Click on the button to open the indexes dialog.
On the first available line, enter a name in the first column, in the second column, select the name of the first field.
Check the box at the bottom to make the index unique.
Then on the next available line, skip the first column and in the second column, select the name of the second field.

You could continue with up to 10 columns selected which is the max for Jet/ACE. Access understands that as long as the index name column is blank, the selected column belongs to the previously named index.

 

[RainLover]

If you go with the surrogate PK, then you need a unique composite index that includes both the FK fields to enforce the business rules.

Click on the button to open the indexes dialog.
On the first available line, enter a name in the first column, in the second column, select the name of the first field.
Check the box at the bottom to make the index unique.
Then on the next available line, skip the first column and in the second column, select the name of the second field.

You could continue with up to 10 columns selected which is the max for Jet/ACE. Access understands that as long as the index name column is blank, the selected column belongs to the previously named index.

It is amazing how many experienced users do not know this.

 

[Pat Hartman]

It amazes me also.

 

[zubersurati]

Hi,

Pat Hartman, RainLover thanks.
Pat I had done as suggestion, Electric Rod Icon I meant Index dialog (Dialogue) box. Pat I appreciate you explanation very much, Now I am clear in my mind.

Cheers!

Note: Agree RL,Practical gives experience Textbook can give all the theoretical knowledge.

ZS