I've designed the attached database which I believe is normalised and resolved the many-to-many relationships by creating junction tables (eg. between tbl_Incident and tbl_FaultList: tbl_IncidentFaultDetail as shown in the attached diagram).

I've now come to populate the database but cannot figure out how to populate the junction table. I am presuming that since the primary key of Incident is Incident Number and that of Fault List is Fault ID which are both Autonumbers [and which are foreign keys in the junction table and together form its primary key], the junction table should automatically take these two numbers, ie they do not need to be entered either manually or through the use of a combo box. I've tried creating a query but did not manage.

Maybe something's wrong with the logic!

Would appreciate any help.

Thanks
Mario

Welcome to Utter Access Forums!

Using form with sub-form, one of the PK can be "captured" by the system, however, you will need to enter the second one... the system does not know which FaultID to relate to which IncidentID...

Also, you will be better off avoiding spaces in object names (tables, fields, etc.) and using reserved words (Date, Time, etc.)... it may cause a headache as you continue designing the application.

Many thanks for your answer. Will try it out. I guess what I still can't reconcile in my head is that while an occurence should only be entered once, the entries in the junction table are being "re-entered".

Thanks again

Hi Mario

"Ditto" on Welcome to Utter Access.

Just a few tips and observations as I have a rare minute to respond.

The Junction table should have it's own Primary Key (meaningless to a user) Thus avoiding the use of a Compound Primary Key as you indicated. There are some FAQA articles in regard to avoiding that practice.
However the articles will guide you in the use of a compound index if you really need it (comprised of IncidentID and FaultID ).

But Back to your original point of confusion/question.
When properly set up in a form/subform combination Access will fill in (populate) the FK records in the junction table.

I made a little demo for you to visually see this occuring.

open the form an add records to the Fault and agent number fields in the form/subform and you will see the FK fields fill(Populate) automagically.

I used the form wizard to produce the form/subform and merely added all the fields from the three tables.

Charlie

Hi Charlie,

Many, many thanks. You made it look so easy! Now that I can see it working, I hope I'll be able to replicate your approach.

Thanks again for your time.

Regards,
Mario

You're Welcome.

Charlie

Hi Charlie,

I tried it and it worked!! I guess the trick was using a new primary key for the junction tables rather than the combined foreign keys. That or I was unknowingly doing something silly!

Thanks a million.

regards
Mario

Hi Mario
Definitlly not silly at all. It could work the way you had it , however I took the opportunity to include a bit of
primary/autonumber basics within the example. It's the form/subform relationship that makes it work right.

The part about Access automagically inserting FK values is something that is not readily apparent and seldom if at all mentioned in textbooks. Seeing it in action is worth more than a thousand words.

What makes this such a great place is how people repay each other by passing the knowledge and experience to others in much the same way they received it.

Enjoy

Charlie

I'll hold my hands up now and admit to being a lurker who's signed up to UtterAccess only to be able to steal information and dig up old posts... Please forgive me as I'm hoping for an existing solution to my very similar problem...

Having downloaded your database though, I can't see any forms! I'm using Access 2003, is this likely to be why!?

Many thanks in advance! :-)

Note that you were posting in a thread that's five years old, and that Charlie hasn't posted anything at all since 2010: it's rather unlikely he'll see your message. We happily welcome lurkers out from behind the bushes; you may be better served by starting a new thread, explaining the structure of your tables, their relationships, and what specific problem you're trying to solve.