This may seem like a simple problem to most but I am not a programmer so if you can help please be gentle and remember I am a novice. I have 3 tables: Family, Member, and Children. All three are linked by FamilyID. Ideally each family would reside at the same address, have the same phone number etc. But as we know in todays world often thats not the case. Assuming that it is the case over half the time I would like to have certain fields in a master form auto populate on the subforms. For example the frmfamily has a field for address, city, state, zip, and phone. These fields also exist in the member and childrens tables by the same names referenced in the master form as subforms. When the user types an address in the frmfamily that address should automatically appear in the subform fields of the same name. If however the member and children tables should require a different value, that should be allowed to change without changing the value of the master (family) record? Makes sense?
Copy field data to subform?
- Thread starter erkerns
- Start date
It's easier to address your problem this way.
If it can happen, it will, so build the database accordingly.
What I mean is, it would be better to create another table with the address information and create a one (family id) to many (addresses) relationship between them. This way a family could have forty addresses and it wouldn't effect the database. You could leave the master address in the family table an use it as a primary address.
If it can happen, it will, so build the database accordingly.
What I mean is, it would be better to create another table with the address information and create a one (family id) to many (addresses) relationship between them. This way a family could have forty addresses and it wouldn't effect the database. You could leave the master address in the family table an use it as a primary address.
I was thinking along those lines, but perhaps my logic is flawed. Again I'm new, so please bear with me. My goal is to save data entry time. So even if the all fields in the table sharing the same FamilyID are the same I dont want to have to type it for each member of the family. The database is a membership database. The master unit is the family. Families are broken into adult members, typically husband and wife, and further broken down in children. All may share the same home address, or may have up to three different addresses. Additionally there is a club address that is often in the same city and state as the member. Wouldnt it be nice to enter an address, city and state in the master (family)form, and have that information automatically populated in the subforms contain like fields. By the way this should apply to new records only. Not when editing. In other words if i edit the family address it wont change the address of the member if a value already exisits.
- Local time
- Today, 15:01
- Joined
- Feb 19, 2002
- Messages
- 47,578
The Address fields should NOT appear in all the tables. The Address fields should be stored in a separate table which is joined to the persons table by a relation table because there is a many-to-many relationship between people and addresses. One person may have many (you said 3 but there is no reason to limit this) addresses and one address may be shared by many people. I presume that each person has ONE address that is designated as primary. I'm not sure what the other addresses would be used for.
Before I spend a lot of time on an explaination, I need to understand the relationship between people and address better. Did you say 3 addresses just because you were thinking one for family, one for member, and one for children? Or can each individual really have more than one address and what are you doing with these addresses.
Also, start thinking about using a single person table rather than the three separate tables that you have now. I can explain how to make that work as a self-referencing table so that rows in the table refer to other rows. Each row has a FamilyID. When you enter the person who is designated as the FamilyHead, that person's PersonID gets placed in the FamilyID of his record and all other family members' records.
Before I spend a lot of time on an explaination, I need to understand the relationship between people and address better. Did you say 3 addresses just because you were thinking one for family, one for member, and one for children? Or can each individual really have more than one address and what are you doing with these addresses.
Also, start thinking about using a single person table rather than the three separate tables that you have now. I can explain how to make that work as a self-referencing table so that rows in the table refer to other rows. Each row has a FamilyID. When you enter the person who is designated as the FamilyHead, that person's PersonID gets placed in the FamilyID of his record and all other family members' records.
Pat,
That definitely sounds like something I should be doing, however as I stated before I am just a beginner and I'm afraid I might get lost if things become to complicated. I'm not being paid to do this, I am volunteering for a church. Perhaps I can email you what I have and you can see what I am trying to do and make suggestions...perhaps I need to start from sctrach???
That definitely sounds like something I should be doing, however as I stated before I am just a beginner and I'm afraid I might get lost if things become to complicated. I'm not being paid to do this, I am volunteering for a church. Perhaps I can email you what I have and you can see what I am trying to do and make suggestions...perhaps I need to start from sctrach???
- Local time
- Today, 15:01
- Joined
- Feb 19, 2002
- Messages
- 47,578
Properly structured tables will greatly simplify your task. But before I can offer a suitable solution, you need to answer the question about the relationship between people and addresses. Please reread my previous post and try to answer the questions. If you don't know the answer, ask your contact at the church. The solution you need will be different depending on whether you have a 1-address-to-many people relationship or a many addresses-to-many people relationship.
I tried uploading the previous version so you could see all tables and structure but the file is 1.2MB too large to upload here.
*****************************
Version2 basically contains the tables and sample data when I began building the forms. The tables were quite ambitious and show a basic thought process as to the data planned for the application. The forms have very limited functionality and were made before realizing the underlying problem of the atabase structure. Good example of getting the cart before the horse.
Version2a shows a little more functionality in the forms as seem in frmMain and Members, but the the stucture of the database is still presenting problems.
Version2b has concentrated on a different approach for the table/value relationships. tblHeadofHousehold replaces the family table and tables for spouse and children have been added. I am attemping to organize the data in such a way that members of the same family can easily be cross referenced without complex queries. This is mainly because I really dont know VB and building complex queries and using them is not my strong point.
The form displayed in this version is "blueprint" of what the family interface should be. Of course the interface will change, but the basic idea, ie; tabs, an subforms will remain.The information within each tab will be added in addtional tables where either the familyID or memberID is utilitized to associate records.
Future updates, should I master this much, will include other forms that display different types of information and categories, such as visitors only, children only, staff only, members with attendance between date and date, etc. Other data to be referenced and recorded will include class information, personal notes, visitation journal, and perhaps a companion contribution/finance application that allows for more detailed entry and reporting of contributions, sales, and other fionancial transactions based on familyID or memberID. It is also planned to allow certain fields to be updateable and customized by the user. For example
default header and footer information containing the organizations contact information, messages to be displayed on various reports("Thank you or your tax deductable donation" on a receipt footer), and certain customizable lookup fields (adding titles like Rev. to the Mr., Mrs, Dr. title options,
or changing Benevolence to Building fund in contribution types).
I am volunteering my time for this project, so the learning experience will be my compensation.
Earnest Kerns
ekerns@ureach.com
*****************************
Version2 basically contains the tables and sample data when I began building the forms. The tables were quite ambitious and show a basic thought process as to the data planned for the application. The forms have very limited functionality and were made before realizing the underlying problem of the atabase structure. Good example of getting the cart before the horse.
Version2a shows a little more functionality in the forms as seem in frmMain and Members, but the the stucture of the database is still presenting problems.
Version2b has concentrated on a different approach for the table/value relationships. tblHeadofHousehold replaces the family table and tables for spouse and children have been added. I am attemping to organize the data in such a way that members of the same family can easily be cross referenced without complex queries. This is mainly because I really dont know VB and building complex queries and using them is not my strong point.
The form displayed in this version is "blueprint" of what the family interface should be. Of course the interface will change, but the basic idea, ie; tabs, an subforms will remain.The information within each tab will be added in addtional tables where either the familyID or memberID is utilitized to associate records.
Future updates, should I master this much, will include other forms that display different types of information and categories, such as visitors only, children only, staff only, members with attendance between date and date, etc. Other data to be referenced and recorded will include class information, personal notes, visitation journal, and perhaps a companion contribution/finance application that allows for more detailed entry and reporting of contributions, sales, and other fionancial transactions based on familyID or memberID. It is also planned to allow certain fields to be updateable and customized by the user. For example
default header and footer information containing the organizations contact information, messages to be displayed on various reports("Thank you or your tax deductable donation" on a receipt footer), and certain customizable lookup fields (adding titles like Rev. to the Mr., Mrs, Dr. title options,
or changing Benevolence to Building fund in contribution types).
I am volunteering my time for this project, so the learning experience will be my compensation.
Earnest Kerns
ekerns@ureach.com
- Local time
- Today, 15:01
- Joined
- Feb 19, 2002
- Messages
- 47,578
You have a bunch of extraneous indexes. They all need to be removed. They were created because of a default setting. To Tools/Options Tables/Queries tab and remove all the values in the Autoindex field. This will prevent Access from continuing to make these extraneous indexes but you still need to delete the ones that are already there.
You also have no way to close your main form. You have removed the control box.
Combine the three tables into a single table. Add an address table. Connect the two with a relation table that includes PersonID, AddressID, AddressTypeID. Search the archives for the many-to-many sample db that I posted to see how this relationship works.
You also have no way to close your main form. You have removed the control box.
Combine the three tables into a single table. Add an address table. Connect the two with a relation table that includes PersonID, AddressID, AddressTypeID. Search the archives for the many-to-many sample db that I posted to see how this relationship works.
