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

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.

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.

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

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.

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)

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).

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.

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 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 (http://www.btabdevelopment.com/main/LinkClick.aspx?link=140&tabid=55&mid=385) to keep duplicates out.

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)