Many-Many Relationships 1

[Garridon]

I'm working on a suspense database, where each department may have many suspenses, but one suspense may have many departments who have to complete it. I know that I need a junction database with two primary keys, one related to the department table and the other related to the suspense table.

The problem is making it populate the table. Access Help says that a query can be created which will do this, but I can't figure out what I need to do in the query to make it work. Does anyone know what I need to do?

Thanks!

Linda Adams (Garridon@aol.com)
"The Importance of Being Grammarian," published in The Toastmaster, March 2001

http://www.geocities.com/hallona_2000

http://www.david-hedison.com

 

[ghubbell]

Hi Linda, I not sure I understand where you're populating from so...

Department Table
DeptID P 1-many
DepartmentName field
More Dept fields

Suspenses Table
SuspID P 1-Many
SuspenseDescription
More Susp fields as required

DeptSusp Table
RecordID P autonumber
DeptID F allow dups from DeptID in Department Table
SuspID F allow dups from SuspID in Suspense Table
A Date Field perhaps

P primary, F foreign

Build a form based on the DeptSusp Table.
Build a combo box for DeptID but based off of the Department table: DeptID (hidden) and DepartmentName showing.
Ditto on the combo box for SuspID - based off of the Suspense table SuspID (hidden) and SuspenseDescription showing.

On a continuous form this will look pretty good and allow quick data entry too. Hope this helps!..... Gord
ghubbell@total.net

 

[dneufarth]

In the Suspense form add a procedure to After_Update event that does an update query to the junction table for each department selected on new records (Me.NewRecord = True) only.

The junction table may also contain fields to represent the department's status, etc.


Dave

 

[dneufarth]

Correction - make that an append query for new records

Dave

 

[Garridon]

Ah, ha! I got it to work. I created a query which does the join between the two tables (in this case, I didn't need to do the junction table). But ... there's a new problem. When I create the list box, it works fine. I can select one entry in it. But when I change to simple or extended, it won't retain any changes.
Linda Adams (Garridon@aol.com)
"The Importance of Being Grammarian," published in The Toastmaster, March 2001

http://www.geocities.com/hallona_2000

http://www.david-hedison.com