Problem adding a table relationship from an associative table to another table

[vmbarlow]

Many to many relationship (Club Members -- Member_ID(PK) : Rocket Launches -- Launch_ID(PK) broken down by an associative entity (new table -- Member Launch, using both primary keys). All is well and good. Now I need to hang a new table {Member_Flights} off of the associative table. I have tried every key combination I can think of, I either get an indeterminate relationship or the NO UNIQUE INDEX FOUND error message shown below. ANY HELP - please!

[Micron]

The way you have it, there can be many records with the same member id and the same launch id - just not a duplicate combination of the two. You need a unique index - MemberLaunchID - autonumber PK? Not sure what the joins would be because I have no knowledge of the db purpose.

If that's all the table contains, I don't see the sense in just having a composite of 2 primary keys and nothing else. Perhaps you are adding more fields?

[mike60smart]

Hi
Can you upload a zipped copy of the database?

[vmbarlow]

Micron: thank you, THANK YOU, ThAnK YoU . . . (a thousand thanks). I added an autonumber to Member_Launch and made it part of the key. Works like a dream now. I must have a mental block, I still do not understand WHY member ID & launch ID are not unique. The intent was that each record in the table was a specific club member at a specific launch -- which should be unique. Then, for each club member at each rocket launch, there are multiple flights. Anyway, thank you so much for your help !!!

[Micron]

Not sure if I can clarify for you, but a composite index can look like this:

dog	cat
fish	bird
dog	bird
fish	dog
fish	cat

No two combinations are the same in that example. However, each of the fields in that example contains multiple values that are the same. The constraint with a composite primary key is that no record can contain the same combinations of values. IIRC, Access cannot create joins on composite PKs, so a single field that can only contain unique values is what is required for referential integrity. That's where the addition of the PK (usually autonumber PK) comes in.

Many advocate that you should not create composite PK fields - presumably because of the limitations around referential integrity, but beyond that I've yet to come across a definitive explanation for not creating them at all. When challenged, the best I've been offered is opinions. IIRC, I have used composite PKs but not without a unique PK field as well. That being said, I've morphed to using PK fields along with composite indexes, which are not the same thing as composite keys.

[ssanfu]

FMS has this article about PK fields:

Microsoft Access Tables: Primary Key Tips and Techniques

[vmbarlow]

I believe that Micron hit the nail on the head when he said, "IIRC, Access cannot create joins on composite PKs, so a single field that can only contain unique values is what is required for referential integrity. That's where the addition of the PK (usually autonumber PK) comes in." I have heard for many years that "purists" dismiss MS Access as NOT A TRUE RDBMS. Perhaps, in part, this is the reason why -- inability to create joins on composite keys? Anyway, a huge and sincere thanks to all who helped.

Unfortunately, as is the usual case, solving this problem has created another problem. I now have a report design, grouped by rocket launch and further grouped within rocket launch by rocket flyer. I want to count BOTH the number of UNIQUE rocket flyers at a particular launch and the number of rockets flown by that flyer (member). Please see design view below. In this example, expr1 is a concatenation of member last name & ', ' & member first name. I am familiar with DISTINCT in a QBE query (which the report is based on) but I am unsure if that is the correct solution. In other words, I only want to count unique (grouped) members in the expr1 footer, not 1 per line (every rocket flight).

[Micron]

Did you research running sum over group in Access report, or similar? Usually it's just a matter of a textbox in the detail records for a group, control source =1 and set to running sum (property sheet), choosing the right level to set the running sum over (e.g. over group). You can hide that textbox but show a group footer textbox = Sum([othertextbox]).
At least that's my recollection.