relation and display issue....?

[zozew]

Explenaition:
Im adding a feature where the user can add records in a new table (people2) that will be related to existing records in a table (people1).

Basically im adding a feature with visitors to a db with inmates in a jail. The visitors can visit multiple inmates and the inmates can have multiple visitors

The new records can be related to multiple people in the people1 table and...the people1 records can be related to multiple records in people2 table

First example:
people1 to multiple people2

Second example:
people2 to multiple people1

The db is quite complicated with many calculations when moving from record to record in the main form. (The mainForm displays many tables records in subforms via relations)

The people1 table is already in the db and is used as the main table for the whole db and the main form.

Final goal:
I want to add a new table/form (people2) and display it in the main form. And display the relevant people2 records for the main forms current record.
Of course i need an input form to add the new people2 records and somehow link them to the existing records in people1.

I also want to add a form where the user can search for a people2 record and then display it with related people1 records

The main issues:
1. The main form already has 2 subforms with relations and 1 subform with filtering, so adding another form (for people2 table) with filtering or relation might slow it down to a crawl

2. How do i get the linking working between the tables as the people1 table is already in place. (If there would be a "one to many" linkage one way i can do it, but both ways...?)

3.I want the user to be able to add multiple records to the people2 table and link them to one or many records in the people1 table and if necessary the reverse way to (Link existing people1 records to existing or new people2 records)


thx for any ideas

Z

 

[MarkK]

I would not create two tables to store the same kind of thing. All your people should be in a single table, and if they differ somehow from each other then add a field to the table to make this distinction. Then you can very easily return subsets of people based on certain criteria.
Cheers,
Mark

 

[zozew]

Ok, i get that. But the two sets of people are extremely different. One has about 40 fields and the the other only about 7. ONe set are inmate profiles the other is The inmates visitors.

Would you still have them in the same table?

Z

 

[zozew]

If you look at the attached image, this was my idea..a intermediate table to link Inmates to Visitors...does anyone think this would work?

The tblVisitorInmate is the intermediate table....

 

[MarkK]

So perons1 and person2 are inmate and visitor? That does make a difference doesn't it.
What you propose in your last post is a classic example of a many:many relationship and it will absolutely work.

 

[Galaxiom]

There are many things I would change in your database as there are a number off normalization issues.

All those various people fields in Inmate like mother, father, next of kin, PleaseNotify are all just people. They should be moved out into a single table of people and referred to by a PersonID in the InmateProfile.

As many of these people will share addresses I would have a table of addresses and refer to them from the People table via AddressID.

People To Address would be best done as a ManyToMany so that any number of addresses can be assigned to any person. This can have an AddressType field to indicate Work, Home etc.

You will find once you do this that there is far less difference between inmates and other people. As Lagbolt suggested you should combine all people including inmates, visitors, parents etc into the people table. Inmates can have an extra 1:1 table for the extended attributes if you like.

Visitors are often parents so you are doubling up records and once again breaching normalization. I would use a ManyToMany relationship between People records and include a RelationshipID in the join table. So Mother, Father, Brother, Friend etc would all be indicated for an inmate.

Using this you could even indicate direct relationships between visitors if you wanted.

Using one table for all people will make it easy to admit those visitors when they fall foul of the law too.

------------------------------------
BTW I have had a look at what you sent regarding the disconnected recordset issue. Will contact soon with suggestions.

 

[zozew]

Yeah i agree with you fully, but...haha there is always a but. Im starting to get a little lost now with how to actually make the relations work and display them. As i said before this is my first Access project and its starting to get a tiny bit complicated now.

As an old flash designer i see most things from a design point of view and it complicates things when i want somethings to behave a certain way. You (Galaxiom) have seen (hopefully) my db and i want so many things to be displayed at once. Should i start dividing up the fields in the InmateProfile to normalize everything...? Im a little scared to F*¤% things up as they (the jail personel) are working with the db right now while im still developing.

I need some guidance on what to start doing first and if you can how to do it so i don't mess it up.

Thx again for taking the time

Z

ps. the main table has 5000 records now including "archived" ones.