Many-to-Many Issues

[OxDavis]

I am importing some data from an excel spreadsheet into tables and attempting to set the relationships so that I can run reports on it. As you can see, each unit can contain a number of case managers, no sweat. However, it sems as though I need to create a many to many relationship between my case managers and the actual clients. Each child can have up to 3 case managers of 3 different types. Just as a case manager can have many kids, so a child can have many case managers. I've tried quite a bit to get this to work, but to no avail. Could I get some suggestions on how to bind these tables together? I have expanded the view so you can see all the fields for each table. The last 3 fields on the far right table match up to the fields under the middle table, but even trying a linking table I can't seem to break this barrier.

Notes:
No data will ever be inputted into this db, just monthly erasure and reimportation.

 

[Pat Hartman]

You need to remove CMID, UnitID, and CMType from tblChild. Then you need to create a new table. This table will manage the relation between tblChild and tblCaseManager.

tblChildCaseWorker
CMID (primary key field 1, foreign key to tblCaseManager)
ChildID (primary key field 2, foreign key to tblChild)

 

[OxDavis]

If I am importing data, how do I populate those fields in the linking table? In other words, how do I tell Access which CMs go with which Children?

 

[Pat Hartman]

Access has no way of working this out by itself. Does the input contain the CMID?

 

[OxDavis]

Access has no way of working this out by itself. Does the input contain the CMID?

No, its an autonumber. What about an append query putting the CMID and ChildID into those fields?

Edit: Ok, tried this, I cannot append both fields at the same time for some reason. The comp locks up, the DB goes to roughly 250 MB of space and I get weird data back. If I append one at a time I get non-overlapping data, like 3404 rows of one field and 3404 rows of the other field below it. How can I get the primary keys set up in the linking table do you think?

 

[Pat Hartman]

What exactly does your input file contain?

 

[OxDavis]

The spreadsheet I am importing contains every field you see in tblChild except anything with an 'ID' after it. Plus the Unit field is also in the spreadsheet and is imported.

Each child can have 3 CM of 3 different types. I have sorted all the case managers into 3 separate tables based on type of CM they are (although CMs can actually be any one of the 3 from case to case.)

 

[OxDavis]

Would you mind looking at the DB if I uploaded it? I can put in some fake data so I don't break federal law I think if you saw it it might make more sense that what I am saying.

 

[Pat Hartman]

The CMs should all be in the same table. There should be a lookup table that defines the CMtype. Then the many-to-many is implemented with CM to Child and includes the CMtype to define the relationship.

 

[OxDavis]

Alright, but that puts us back to square 1: populating the linking table, establishing the connection between the CM table and the Child table.

 

[Pat Hartman]

I think posting the input file would be better since that is what you have to work with.