help to set the relationship ?

[siamandm]

Hello All
i have these tables below;

Capture.PNG

the tables on the right hand is used for the cascading combo boxes ...
each student address may change in the future ...
and two students may have the same address,

how to set the relation between address and student table ?
regards

[HansV]

Can there be different addresses within one TentID?

If yes, tblAddress only needs 2 fields: AddressID and TentID. You don't need SectionID, LocationID and ZoneID, because those all depend on TentID.
In this setup, TentID in tblAddress is joined to TentID in tblTent.
The tblStudentID table doesn't need ZoneID. Change this to AddressID, and join this field to AddressID in tblAddress.

If no, you don't need tblAddress at all. The tent identifies the address of the student. Change ZoneID in tblStudent to TentID, and join this field to TentID in tblTent.

[siamandm]

Thanks for the reply

the issue is the Tent field will be optional and my be not be filled out in most cases, in order you have a better understanding what im after i just created a quick sheet with sample data as shown below :

Capture.PNG

regards

[HansV]

That complicates the design. A possible workaround is to create dummy records, e.g. in tblSection a section named "Unspecified section in Eileen Camp" with the LocationID of Eileen Camp, and in tblTent a tent named "Unspecified tent in District Name" with the SectionID of District Name.
That way, each address has a TentID.

[siamandm]

in this case, we have to make the default value for the Section and Tent = Unspecified, in this case, what will be the design?
at the beginning i was expecting the design will be many to many relationships as below

Capture.PNG

[HansV]

I assume that a student has only one address, while several students can have the same address. So it would be a one-to-many relationship:

S1640.png

[siamandm]

thanks for the reply

but now how to design the form to enable a user enter data

here is my tables file : https://www.dropbox.com/s/0ssxz2alglirn ... accdb?dl=0

regards

[HansV]

I am away from my computer at the moment. I will look at it later today.

[siamandm]

I am away from my computer at the moment. I will look at it later today.

thanks alot , take your time...
another thought instead of creating relation between tend and student table why we dont create relation between student and zone?

[HansV]

Open tblStudent in design view.
Click in the TentID field.
Clear the Default Value property - it was the default value of 0 that caused the problem, since 0 is not a valid TentID.

We do NOT want to join tblStudent to tblZone since the zone is a property of the section (and that is a property of the location, and that is a property of the tent).

[siamandm]

ok, how to desing the Form to enable the user to make data entry ?!!

[HansV]

See the attached version. I created two forms:
frmEditZones lets you edit and add zones, locations, sections and tents.
frmStudent lets you edit and add students, with their zone etc.

Student-Address.zip

[siamandm]

thanks a lot for the help
that was an interesting way of making student form,
1- in the frmStudent, if we add new student and only choose the zone, it will not save the zone filed. i have to fill out all the fields!
for solving this, I thought to set the default value of the location to "UnSpecified " after updating the zone combo box ...but seems to be this didn't work for me..

any idea?

2- the other thing I didn't figure it out how you did, is the frmEditzone, when you click a zone it will show the corresponding location and tent?


Regards

[HansV]

You MUST select something in the Tent No field, since that is the value that will be saved.

[siamandm]

if we make tent value = "Unspecified" after updating the zone com box does this will work ? if yes ... how you set its value = unspecified after you update zone combo

[HansV]

See the attached version; I hope it does what you want.

Student-Address.zip

[siamandm]

thanks a lot, yes that's what I'm after..