Trying to link multiple reports together

[Thorlim]

Hi everyone, im posting this topic in the reports section because i think this is the right place for it. however it might need to be in the forms department. so if i posted this in the wrong section, my apologies in advance.

I've created a database for my church for collecting information on visitors so we can keep in touch with them. and the database is pretty much complete, however, im having trouble with trying to link multiple reports together. Sometimes our guest have different last names, but they are in the same family. so what i need to do is find a way to link the multiple entries together. So say if a Grandmother is visiting our church with her grandson who has a different last name. how can i link those 2 together? i wasnt able to really come up with any ideas on my own. so any suggestions are greatly appreciated.

 

[Swemebegur]

One way would be to create a table linking two records in the Person table:
Person Id 1 - FK (eg the grandmother)
Person Id 2 - FK (eg the grandson)
Link Type ("grandmother")
Inverse Link Type ("grandson")
The prime key would be the two person ids. This would enable you to link the two in either direction. If you wanted to cater for changing relationships, add a date to the key.

HTH

Swemebegur

 

[Thorlim]

Thank you! i will defintely try this. but do you think this method would work if the visitors are inputing the information themselves?

 

[Swemebegur]

This would depend very much on your form design. Both person records would need to be created before the link record could be created. How much data will the visitors be inputting?

Swemebegur

 

[Thorlim]

Well the Visitors would be putting in all of the information. there will be a computer booth set up for them. and they would list
First and Last name
Phone
Email
Address
City
State
Zip
Martial Status
Best Form of Contact
Age
And

 

[Swemebegur]

The simplest way would be to have a separate form which the administrator (you?) would use to link the associated visitors. It would also be possible to let the visitor(s) make the link but this would need to be carefully designed. BTW, are you sure that visitors would be willing to enter all that data?

HTH

Swemebegur

 

[Thorlim]

well i thought about letting the visitor put in their own link, but like you said ,we arent sure how much information they want to input. and if they dont input that link, then it would be pointless, but i think im going to try making the links ourselves.

 

[Swemebegur]

Hi Thorlim

I've been thinking about your problem over the weekend, and feel that, if you just want to link the family members for reporting purposes, a separate link table may be overkill. It will certainly involve more complex programming. It would be simpler to add a parent field ("parent" in a technical sense) and a relationship field to the person record, and use these fields in sorting the members of a family together. I attach a small .mdb file to illustrate how this could be done. This approach is limited - eg a child record can be linked to only one parent - but will be much simpler to implement.

HTH

Swemebegur