Access newbie - creating database to gather visitor data at a church

[sowatup]

Hi all, I am new to this site and pretty new to using MS Access. I am using Access 2010, and I have access to Sharepoint Online ($8/month plan). I have been put in charge of creating MS Access database for visitors at our church. We already have sparsely populated existing data of about 1100 entries, mainly name and address. I have started the database, and here's what I have so far. Each word represents a column.



In tbldatabase

Unique ID,FirstName,LastName,Address,City,State,PostalCod e,PhoneNumber,EMail,SpouseFirstName,SpouseLastName ,SpousePhoneNumber,SpouseEMail,Child1,Child1DOB,Ch ild1 Male/Female CheckBox,Child2,Child2DOB,Child2 Male/Female Checkbox

What I want to do is:

1. Create a form that a visitor can come up to, type a name and search. If a name pops up, they can check it for accuracy, and update if necessary.
2. In the form, I want the children information to go to a separate table (tblchildren), but associated by their parents' first and last name.
3. I want to have a field in both tables that will automatically add/update to current date when a modification or a new entry is made.
4. I want to be able to have multiple instances of the form open on 4 different computers simultaneously, and feed into the same table (I have sharepoint online, if it makes this step easier).

Any help with any of these items would be greatly appreciated. I know basics of creating a form, buttons, etc. To me, most important is items 2 and 4 first.

Thank you so much in advance!
Abe

[Homegrownandy]

What is the purpose of gathering this information?

[sowatup]

The purpose is to send out emails, create mailing label, etc. for various events and programs we have through out the year. We also have children activities and functions that require to send emails and invitations using the database.

Abe

[Homegrownandy]

have one table for all of the 'people' then another table that uses just the primary keys to define relationships.

example:

table - people
PK - NAME - ADDRESS
1- joe - address xxxx
2- wendy - address xxxx
3- paul - address xxx

table - relationships
PK - PEOPLE_FK - PEOPLE_FK2 - RELATIONSHIP_TYPE_FK
1-1-2-1 (this shows: primary key for this record, the person (joe), the person (wendy), the relationship (parent))

table: Relationship_type
PK-TYPE
1-parent
2-child
3-spouse
and so on...


I hope you understand what I mean here. Have all the people together in one place and then define relationships between them using a junction table. Keep asking anything you don't know and I'm sure you can achieve your goals;

[Homegrownandy]

it seems odd to have a children table. If you know the DOB you can use this information to define if someone is a child or not. that way you never have to change or move data.

point 4: use a split database.. Google this and it will do as you require.

[sowatup]

Thank you so much for the replies! To be honest, I am still lost Here's what I have with screenshots:


There's a desire from the temple admin to keep tblMandirDatabase the way it is shown because that's how the previous database in Excel was setup, so I just copied those fields into Access. During our new years, we are expecting close to 1000 people come in it will be the perfect opportunity to add/modify existing data then. I am confused on how to manage children database, and not sure if the way I have it is even the most proper or robust way. We want to give the ability to add up to 3 children, hence up to child 3.

FatherName = FirstName in tblMandirDatabase
MotherName = SpouseFirstName in tblMandirDatabase
Child1/2/3LastName = LastName in tblMandir Database

The yes/no boxes are for male/female children because we sometimes have separate activities for boys and girls.

The form should have all these fields.

Please help. I am losing my hair and sleep over this

EDIT: I also forgot to mention tblMandirDatabase will be using to create mailing label reports.

Abe

[ssanfu]

Are you building this for a desktop or for Sharepoint?

I would use a main form/sub form setup. Main form is the parents, sub form is the children.


But your dB table structure is not normalized. You are "committing spreadsheet". Excel spreadsheets are "short and wide". Access tables are "Tall and narrow". While a table in access LOOKS like a spreadsheet, it is a totally different animal. You should never allow a user to add/edit a table directly.

For example, in the child table.....
1) There is no foreign key field linking a record back to a parent in the table "tblMandirDatabase".
2) You don't need fields "Child1Male" AND "Child1Female". Only need 1 field, "Child1Gender". (Actually, it would only be Gender if the table was designed correctly.)
3) If there were only 1 child, the other fields are wasted space.
4) And you are limited to 3 children. What about a family that that has 5 children?

There's a desire from the temple admin to keep tblMandirDatabase the way it is shown because that's how the previous database in Excel was setup,

The problem is that Access is not Excel. You should really learn about relational database design. Here is a tutorial (one of many) that you should work through...
Roger's Access Library
http://www.rogersaccesslibrary.com/forum/forum46.html

[Homegrownandy]

That's why I said to not have a table for children at all, just have everyone as an individual, then to know if they are a child or not just query the DOB.

relationships between people can be easily defined, but if it were me I would only define parent/children.

[sowatup]

Here's an update. I figured out how to create a one-to-many relation between the parent and children table and created a subform form it. The children table has a drop down menu that designates son/daughter. I was also able to split the database and test it in a multi-user environment. We go live tomorrow with visitor data entry. During the test run, I found couple of issues with my form. I have added validation rules for the zip code and email addresses and an input mask for phone number in ###-###-#### format.

Here are the issues:

1. Since I have separate field in the form for Mr. And Mrs/Ms. last name, is there a way to automatically reference what's being entered in the Mr. last name text box and when the visitor tabs over to the next field, it automatically copies it to the Mrs/Ms box? In most cases, last names are the same so it would be beneficial to do that. In case the last name is different, visitor can just backspace it and overwrite it.

2. For the first and last name fields, is there a way to chance the case to proper case so regardless of how the visitor types, it always defaults to Xxxxx Xxxxx. Maybe an after update event in the field property of the form?

3. Any other suggestions to improve data entry for visitor information from your experience?

Thanks,
Abe