MS Access Person Profile Form

[brikusi]

Ok I feel like there has to be an easier way to do this and maybe I'm just not capable of thinking of it. I tried building queries, I have an earlier post where i tried using a Dlookup but then tried to ConcatRelated and nothing is working for me.

Here's the breakdown:

Desired Result:
I want 1 form whereby I can pull all of a person's Personal information, Membership information, and Family information. I need this form to be in 2 forms, 1 that's searchable by name and modifiable to edit member information when needed and 2nd available as a New form for a new record.

Problem:
Because I have several tables involved I am unable to find a way to create this form with the view I desire. I have created the database in such a way where it uses IDs versus the name however I want the form to show the names, and when it updates, update it by the IDs if that makes sense. Secondly I want the family information to show for each person profile with their family info displayed only once, not repeated for each record.

For Example:
I would like to have 1 person highlighted on the form with all their personal details, then another section with family details such spouse, dependants, etc. and then their service details such what ministry they belong to, what service they attend, year joined, etc. Will attach a sample of expected view for better visual.

Tables in Database:
tblPerson
PersonID (PK)
F_Name
L_Name
DOB
MobileNo
Sex
MaritalStatus
Nationality
Email
FamilyID (FK)
Type
ServiceID (FK)

tblFamily
FamilyID (PK)
StreetAddress
City
State
Zip
HomePhone

tblFamilyRelationship
FamilyID (PK) also (FK)
PersonID (PK) also(FK)
RelationshipType

tblService
ServiceID (PK)
ServiceName

tblMember
MPersonID (PK)
Status
YearJoined
ShepherdID

tblMinistry_Assignment
MA_PersonID (PK)
MA_MinistryID (PK)

Is this even possible to somehow join everything into like 1 master profile per person? Or am I trying to do to much and should have separate forms? Please provide any help or guidance you can.


** I am new to MS Access and Databases in general. I do however understand the concepts of databases so I made several tables to re-frame from having a lot of null values and data redundancy.**
Thank You

[twinnyfo]

I PM'ed you. Let me know your thoughts.

[twinnyfo]

Bri,

I'm going to take a look a this. Just sending a note to bump it up so I don't lose sight of it.

[twinnyfo]

I need this form to be in 2 forms, 1 that's searchable by name and modifiable to edit member information when needed and 2nd available as a New form for a new record.

Try not to bind the conscience of the DB. It is good to have a vision of what you want to do. However, in most cases, your display form can also serve as an edit/new record form.

I have created the database in such a way where it uses IDs versus the name however I want the form to show the names, and when it updates, update it by the IDs if that makes sense.

1) Don't apologize for designing a database using proper methodology. All the experts here should be aware of such a design and would encourage it.

2) I think you are simply less familiar with how to create joins and display the dat you want. We can work with that.

Secondly I want the family information to show for each person profile with their family info displayed only once, not repeated for each record.

Very, very easy to do. We will work on this.

===========================
Now, to the brass tacks:

First, think hierarchically for a moment. What is the higher level: person or family? It should be family, because a family can have many members. So, I would encourage you to look at families first, because this is how you want to group people (usually) in a church.

So, your main viewing form should be based on families. You should still be able to search for a person, but when you find that person, you filter the form by the person's family.

When you view a family, you have a separate subform with people. You filter this subform by FamilyID. I see no need for tblFamilyRelationship, because I would create tblFamilyMemberType (Husband, Wife, Son, Daughter, etc.) which would then be a FK in tblPerson.

In this subform, if you click on a person, you should be able to view/edit the person's details. At the same time, you should be able to add a new person.

Additionally, I would recommend eliminating tblMember, and incorporating that data into tblPerson. The reason being that all this information applies to every person. For example, the person would be one of the following: Member, Visitor, Regular Attender, Communing Member, Non-Communing Member, Deceased, Dropped from Rolls, etc. This also allows you to maintain tblPerson for historical purposes.

Ministry assignment could also be a subform on the person's details. A person could be a member of multiple ministry areas, so you would want that flexibility.

This is all talking vision and design. No detailed solutions here, as that's typically not what we can provide here. This should get you on the right track. We can work thorugh significant challenges and road blocks as they come up.

[brikusi]

Oh no rush. I been thinking about it over the weekend. I think I normalized the data too much and can denormalize parts of it to make this easier.... So I made a copy of my database in its current state and going to play around with the copy to see if I can get somewhere with it.

[brikusi]

Just seeing post above. Thanks for the guidance. I will get started on it and see where it takes me.

[brikusi]

Ok i took the route you explained above and I think I'm almost there. Here's where I'm stuck.

Right now i added my tblMember table to my tblPerson table.
However, when I made my main form based on Family and created a subform on tblPerson, the subform (frmPersonSub) has become 1 long form, whereby I have to scroll pretty far to view all the details. I want to design it in such a way that for 1 person in the family their details are grouped kind of then a line beneath or colored row with another person's details below, versus 1 long row for each person.

I hope this makes since

[twinnyfo]

I hope this makes since

No. It does not.

Screenshots always hepp!

[brikusi]

I think I answered my own question. Do I remove my header and just arrange everything in the detail section?

[twinnyfo]

Ummmmmmmmmmmmmmm......

Screenshots always hepp!

[brikusi]

Nevermind on that question lol. I tried to delete but couldn't find how to.

Here's my issue. My FamilyID field is Autonumber in my tblFamily table. This ID however is a fk in my tblPerson Table.

If I add new person on my form, how can I have the FamilyID field copy or update the tblPerson table once the form is saved. I will add a screenshot.

Update: I added an attachment. THE FamilyID number generates at the main form form level which is tblFamily but I would like to have it update the tblPerson Table when a new member is added to the family, as FamilyID is a FK in my tblPerson Table.

[twinnyfo]

If you have your form for Families and have a Command Button for "Add New Person to Family", you click the button and have a new form pop up, with record source tblPerson. It is filtered by FamilyID--but have it set to Data Entry Mode. This will take you to a new record. Any record added at that point should be assigned to that same family. When you are done with the Add New Person Form, refresh the family form and they should show up.

You may have to post your DB (remove real people's names) for us to work through this. Like I said when I PM'ed you. This has already been done by hundreds of vendors and saves all these headaches.

This can be done. We will just have to work through the steps.

[brikusi]

I got it to work by "Allow Additions" on my frmPersonSub. this adds a new record to the end of the sub form and when I tested saving it, it updated the tblPerson table with the familyID. However that only worked for existing families.

I had to create a new form and set it to Data Entry so it opens on new, as well as opens the sub form as new.

Now I have 2 forms:
1. A form to edit existing families or to add to an existing family
2. A form to add a new family entirely.

This may not be the most efficient way of going about it but it works for me now. Not sure if it will cause me problems later.

My last thing to do now is just figure out how to show the name versus the ID number for some fields
for example: Display Ministry Name which is Media vs the MinistryID which is 1. Yet if someone changes or updates the record to lets say Transportation, table should update with 3 and not the description.

[twinnyfo]

I got it to work by "Allow Additions" on my frmPersonSub.

This should default to "Yes" and should always be truned on unless there is a specific reason you don't want to add any records.

However that only worked for existing families.

We'll talk about this in a bit.

Now I have 2 forms:
1. A form to edit existing families or to add to an existing family
2. A form to add a new family entirely.

Why would you need two forms for this? If you have a form that views Families, You just go to a new record and add a family. It's not so much about efficiency, it's about necessity. A form can modify existing records or it can add new ones.

My last thing to do now is just figure out how to show the name versus the ID number for some fields
for example: Display Ministry Name which is Media vs the MinistryID which is 1. Yet if someone changes or updates the record to lets say Transportation, table should update with 3 and not the description.

This is all done with basic JOINs. Your subforms (for ministries, for example) should be built upon the table. The Subform has as its record source the MinistryID and the MiniistryName. The ID does not need to be displayed ont he form. But the subform has a Master/Child relationship on the parent form with the MinstryID of the Person. It is only just barely slightly more complicated than this.

However, again, screenshoots help. Posting the queries you use for your record sources helps.

Additionally, I think you are overlooking the inherent complexity of a "simple" church database.

Also, for the purposes of this forum (and for my sanity), let's try to stick to ONE THING, so we can work through it one piece at a time. When nothing works properly, you can't fix it all at one time. This is a basic troubleshooting principle.

[brikusi]

Ok Sorry. So I didnt base any of my forms for this profile creation on queries. All are built off the tables. If I can post an empty DB I will attach it otherwise Ill try and create screenshots of what I have. You're confusing me a bit when you say "Your subforms (for ministries, for example) should be built upon the table." but then say "Posting the queries you use for your record sources helps". Should they be based on queries?

I can't upload a copy of the blank DB. States a security token is missing. error message.

[twinnyfo]

You're confusing me a bit when you say "Your subforms (for ministries, for example) should be built upon the table." but then say "Posting the queries you use for your record sources helps". Should they be based on queries?

The only way that you can see the numerical value of these things is if you are not using a query to join the tables. Then all you will get is numbers (unless, of course you have Combo boxes set up to reflect the values associated with htose numbers).

BUT--if you have your tables created properly, whenever you drag and drop a field onto your form, if there is a "value behind a value" it will already create the combo box for you with the appropriate display.

Example: You build your Families Table. You Build your People Table. As you build your people table, you have a field called Family. Use your Lookup Wizard to choose the FamilyID and FamilyName. BAM - Your relationships are made. Every time you use that field for a person the family name will show up.

[twinnyfo]

This might hepp.

[brikusi]

This is my setup of the db. are you suggesting I use the lookup field in the table design. I always read that you shouldn't use it there.

[twinnyfo]

If you use Lookup Fields properly, you will never have any issues. You always use lookups that use the index and not the value. If you do this, you are fine. Trust me, I've gone in circles with the experts here who say, "DO NOT EVER USE THEM!" but then when I describe how I use them, they're "Oh--that's fine."

I just prefer to see a person's name, not a number. A number is meaningless to me, visually, even though I know it refers to other data.

[brikusi]

Exactly! thats exactly what I want to do. So maybe I'm using it wrong now. I have for example of 2 tables:

tblPerson and tblFamilyRelationshipTypes

FMemberTypeID is the PK in the tblFamilyRelationshipTypes table but the FK in the tblPerson table.

tblFamilyRelationshipTypes
FMemberTypeID (1,2,3,4)
FMemberType (Head of House, Spouse, Son Daughter)

I went to the tblPerson table on this field and chose lookup --> Combo Box --> Table/Query--> then selected the FMemberType field as the values to display. I got an error when I ran my form that data type was incorrect.

The FMememberID field is a Number, but as you mentioned I don't want to display a number.

[twinnyfo]

OK - here is the disconnect: ignore the stuff ont he bottom of the design view. Follow these instructions explicitly.

1. Open tblPeople in design view
2. Set your cursor to create a new Field
3. Name: FMemberType
4. Data Type: Select Lookup Wizard...
5. Check "I want the lookup field to get the values from another table or query"
6. Click Next
7. Click the Tables Button
8. Click the Table tblFMemberTypes
9. Click Next
10. Add both FMemberID and FMemberType
11. Click Next
12. Sort by FMemberType
13. Click Next
14. Check the "Hide Key" box
15. Click Next
16. Check "Enable Data Integrity" and ensure that "Restrict Delete" is checked
17. Click Finish
18. It should ask you to save the Table
19. Open your table in datasheet view
20. Select the FMemberType for several people
21. Violas, Violins and Violoncellos! Now you see the FMemberType and not the number!!!!!

Is that what you have been doing? The KEY is to always use the Primary Key in these transactions.

[brikusi]

Ok. I'll give it a try. btw zipping the db worked. I attached it above.