Populating Junction tables and/or populating two tables at once

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Cheese_whiz - 04 Oct 2006 14:24 GMT

My system needs a junction table between tblPersons and tblProjects so I can
assign people to projects (and hopefully add a "role" as well). Therefore, I
think I need a junction table using the projectID and the personID from each
of those primary tables (both fields, respectively, are the individual PKs in
those tables) as the composite PK for my junction table, and then I'll add in
a "projectRole" field in the junction table as well.

All that I think (hope) is ok. What I can't figure out (and it's a broader
question than just with the junction table but the junction table is a good
example) is how one populates more than one table with "one" form. Can one
form populate more than one table simultaneously? Does it have to be based
on a query or something? Does it have to be a form/subform setup or
something?

In any of those cases that might work, do you end up with the user putting
the same piece of information in the same form TWICE just so you can parse it
to two different tables? I just don't know how this works and even a little
direction would be helpful.

Thanks,

cheese W

Reply to this Message

Douglas J. Steele - 04 Oct 2006 14:52 GMT

Typically, you use a subform.

Take a look in the Northwind database that comes with Access.

The relationship between Products and Orders is a many-to-many, so Order
Details is introduced as the junction table (sure, Order Details contains
more than simply the IDs from the Products and Orders tables, but that
doesn't really change the approach)

Now, look at the Orders form, and the Orders Subform that's contained on the
Orders form. The Order ID is contained on the main Orders form, and that's
what links the Orders form to the Orders Subform. Note how the Orders
Subform uses a combo box to allow the selection of the specific product:
that's the part you want to carry over into your situation.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> My system needs a junction table between tblPersons and tblProjects so I
> can
[quoted text clipped - 29 lines]
>
> cheese W

Reply to this Message

Cheese_whiz - 04 Oct 2006 15:56 GMT

Thanks for the reply, Doug.

I knew it was similar to that type of situation, but for some reason I still
haven't put it all together in my head. Maybe when I can look at that
northwind db it will help. Unfortunately, that's not loaded onto my comp at
work. I do have it at home, though.

Thanks again,
CW

> Typically, you use a subform.
>
[quoted text clipped - 44 lines]
> >
> > cheese W

Reply to this Message

prontonet - 27 Oct 2006 00:37 GMT

Can you include a bit more detail please....I have the Northwind database but
do not know
how to construct the SQL select statement to show how the many-many senario
works!!

>Typically, you use a subform.
>
[quoted text clipped - 16 lines]
>>
>> cheese W

Reply to this Message