3 tables, 2 junctions and how to append and update the combinations.

[smndnm]

Hello,



Following on from my previous discussion www.accessforums.net/showthread.php?t=71837 which was hugely helpful. I have questions on how to update the records.
There are:

1. Three tables - Project, Builder and Contact.
2. Two junction tables which relate Project to Builder, and Contact to Builder.

From this I have a queryPBC that entirely describes Who is Working for Whom and Where.

I see 8 combinations (2x2x2) of new or updated information that the user is likely to enter to create new records which would be discovered in the queryPBC.
Just focusing on the Project-Builder relationship; in all instances of new data entry, there will be additional combinations in jnctProjectBuilder table AND in some of these there will be an additional record in either tblProject or tblBuilder or Both.
The same combinations apply to the Builder-Contact relationship.

Presuming I am going to use a well presented form for the user.
My Excel Educated brain sees a user form with a multitude of buttons to cater for all the combinations of data entry into all tables... This is clearly not the way forward.
Now, I will hazard a guess the solution is probably via an append and/or update query, However I am blind to the first step.

I can build forms all day.
I can create unique records in jnctProjectBuilder from existing records in tblProject and tblBuilder in a basic clunky way.
I can create unique records in both tblProject and tblBuilder in the same clunky manner.
But this is currently a two step coal-fired-steam-driven and wholly inelegant process.

How do I drive the right queries from a user form that updates/appends the required 5 tables with the required data without bamboozling the user with options.


A hard shove in the right direction will be appreciated.

[June7]

Not really clear to me what the issue is.

Exactly what are the table relationships? Each project can have multiple builders and each builder can associate with multiple projects - many-to-many?

Maybe use form/subform arrangements for user data entry? Main form bound to Projects and subform bound to junction table with a combobox to select builder.

If you need to add new records to 'lookup' table on-the-fly during data entry then use combobox NotInList event code.

[orange]

Suggest you post a copy of your database ---zip format.

Your situation has been seen many times before ---Excel skills don't mean Access skills. Different animals, built on different object models and different principles.

The info in this link on Database Planning and Design may be helpful.

[ssanfu]

It sounds like you are still really lost. In your previous thread, I suggested some tables - I dropped 1 table (don't know what I was thinking - see example).

I strongly suggest you work through the tutorials at Roger Carlson's site "Rogers's Access Library" since you have an Excel background.


Maybe this example will help you.