I am building a database for my church in which it keeps track of all the people that have came to receive free food from our Food Pantry. But I need to have a form that will be continuous in which I could add people that show up for that day and also do a auto number in order of entry, so that I could call them to receive the food. I hope this makes sense
Making a call roster
- Thread starter freddyja
- Start date
First I created a table for all the customers, then other for attendance that has a field that auto lookups customers from customer table. I then created a query to create a input/view form for customers. I then created a query to create a form that I could input form that is linked to attendance table for all customers that are showing up that day.
bob fitz
AWF VIP
- Local time
- Today, 19:18
- Joined
- May 23, 2011
- Messages
- 4,809
I thought that if I saw your db I might have a better understanding of what you are trying to do. Unfortunately that is not the case.
I do have some advice though:
Remove spaces from all object names: e.g. Family Size becomes FamilySize
Normalize your tables: Having fields called "First", "M", "Last", "Full Name", suggest that this has not been done.
Do not use Lookup fields in tables
Define your table relationships.
I do have some advice though:
Remove spaces from all object names: e.g. Family Size becomes FamilySize
Normalize your tables: Having fields called "First", "M", "Last", "Full Name", suggest that this has not been done.
Do not use Lookup fields in tables
Define your table relationships.
Looking at just your last database here are the issues with your tables I see:
1. Invalid foreign key. A foriegn key is the data you store in one table to relate it to another. You've related AttendT to MainT via [Member] to [Full Name]. That is incorrect. You have an autonumber primary key in MainT--that's its express purpose, to be used as a foreign key. Use its value in AttendT, not the [Full Name] field.
2. Poorly named fields. [Count] is a reserved word (https://support.microsoft.com/en-us...n-access-2002-and-in-later-versions-of-access) which means it has special uses within Access. Do not name anything with a reserved words or it will cause issues with query and code later on. Also, avoid non-alpha numeric characters in field names ([Full Name]-->[FullName], [Phone #]-->[PhoneNumber]).
3. No date fields. A person can attend the food pantry more than once, right? Your database doesn't accomodate that.
4. No notes. In design view of your tables there's a field to add a description to what that field is for--use it. For example, that aforementioned [Count] field--its not intuitive to me what it is holding. Descriptions would help me (and people in the future who may work on your database) know what it is for.
5. Discrete data stored together. Every distinct piece of data (Address, Post Code, zip) needs to be stored in its own field. Those 3 pieces of data should each be in 3 fields, not 1. Same for [Full Name] data.
You have a lot of work on your tables before you even need to think about your forms.
In fact, after you have fixed your tables, you focus on building reports to get data out of your database. Once that is done, then you can work on forms. So put those out of your mind for now.
1. Invalid foreign key. A foriegn key is the data you store in one table to relate it to another. You've related AttendT to MainT via [Member] to [Full Name]. That is incorrect. You have an autonumber primary key in MainT--that's its express purpose, to be used as a foreign key. Use its value in AttendT, not the [Full Name] field.
2. Poorly named fields. [Count] is a reserved word (https://support.microsoft.com/en-us...n-access-2002-and-in-later-versions-of-access) which means it has special uses within Access. Do not name anything with a reserved words or it will cause issues with query and code later on. Also, avoid non-alpha numeric characters in field names ([Full Name]-->[FullName], [Phone #]-->[PhoneNumber]).
3. No date fields. A person can attend the food pantry more than once, right? Your database doesn't accomodate that.
4. No notes. In design view of your tables there's a field to add a description to what that field is for--use it. For example, that aforementioned [Count] field--its not intuitive to me what it is holding. Descriptions would help me (and people in the future who may work on your database) know what it is for.
5. Discrete data stored together. Every distinct piece of data (Address, Post Code, zip) needs to be stored in its own field. Those 3 pieces of data should each be in 3 fields, not 1. Same for [Full Name] data.
You have a lot of work on your tables before you even need to think about your forms.
In fact, after you have fixed your tables, you focus on building reports to get data out of your database. Once that is done, then you can work on forms. So put those out of your mind for now.
- Local time
- Today, 14:18
- Joined
- Jan 23, 2006
- Messages
- 15,586
freddyja,
Here are some links to information re Database design and planning. As Bob said, get a good description of what you are trying to do in plain English. Make a "data Model" using pencil and paper, test your model with some sample data. When it's working the way you want, then build your tables. (As plog said)Review what your proposed reports must output, make sure you have or can calculate all the data- revise your tables as necessary. Then focus on the forms/user interface.
Good luck.
Here are some links to information re Database design and planning. As Bob said, get a good description of what you are trying to do in plain English. Make a "data Model" using pencil and paper, test your model with some sample data. When it's working the way you want, then build your tables. (As plog said)Review what your proposed reports must output, make sure you have or can calculate all the data- revise your tables as necessary. Then focus on the forms/user interface.
Good luck.
HiTechCoach
Well-known member
- Local time
- Today, 13:18
- Joined
- Mar 6, 2006
- Messages
- 4,357
This may be helpful: http://databaseanswers.org/data_models/visits_to_church_food_pantry/index.htm
There are commercially available applications for this. just a few examples:
Desktop: https://www.pantrysoft.com/
Web Based: http://www.foodbankmanager.com/
There are commercially available applications for this. just a few examples:
Desktop: https://www.pantrysoft.com/
Web Based: http://www.foodbankmanager.com/
Looks better 2 things and a few questions:
1. Rename your Date field by prefixing it with what it is for (PickupDate, FoodDate, etc.). Date is a reserved word and will make things harder down the road if you use it as a field name.
2. Remove Member from AttendT. I assume that's the member's name from MainT. Its not necessary as long as you have the ID value from MainT in AttendT.
And the questions: what is the Call field for? When will that be populated--after the rest of the record is filled out, or during?
1. Rename your Date field by prefixing it with what it is for (PickupDate, FoodDate, etc.). Date is a reserved word and will make things harder down the road if you use it as a field name.
2. Remove Member from AttendT. I assume that's the member's name from MainT. Its not necessary as long as you have the ID value from MainT in AttendT.
And the questions: what is the Call field for? When will that be populated--after the rest of the record is filled out, or during?
- Local time
- Today, 14:18
- Joined
- Jan 23, 2006
- Messages
- 15,586
You might consider assigning more descriptive names to table(s), and fields. AttendanceID??
and adjust the relationship to enforce referential Integrity. Customers are now Members?
Agree with plog --not sure what some fields represent Called?.
Good luck with you project.
and adjust the relationship to enforce referential Integrity. Customers are now Members?
Agree with plog --not sure what some fields represent Called?.
Good luck with you project.
Okay made updates, the called field let me explain
On the day when people come for to get food, presently we have to fill out a roster that is numbered, then when we are ready , we start called out names by number ( according to number assigned on roster 1, 2, 3 etc) if we call out 1 - 3 we check them off on roster so we know who is next
This is the part I'm trying to automate in access because we serve over 200 people that day, a lot of writing, LoL
On the day when people come for to get food, presently we have to fill out a roster that is numbered, then when we are ready , we start called out names by number ( according to number assigned on roster 1, 2, 3 etc) if we call out 1 - 3 we check them off on roster so we know who is next
This is the part I'm trying to automate in access because we serve over 200 people that day, a lot of writing, LoL
I'm doing this as a volunteer, I was trying to help my church out. Sorry but I was trying to get it up and running for Friday. Thanks alot you guys for your help. Okay "jdraw" I made the changes you told me.
Last edited:
- Local time
- Today, 14:18
- Joined
- Jan 23, 2006
- Messages
- 15,586
Did you look at the links and info I gave in post#9?
You do not show relationships between tables. These tables must be related somehow in your "business". I now see a City table- what is its purpose?
Do you have some test scenarios that you can use with some sample data to test your model?
Do you have a clear description of the processes you intend to support with automation?
You should not be designing such that you require a lot of typing.
Perhaps there are lists and you can find the Member by searching/filtering with a few characters, and then update/create the necessary record.
See my stump the model info
Good luck.
You do not show relationships between tables. These tables must be related somehow in your "business". I now see a City table- what is its purpose?
Do you have some test scenarios that you can use with some sample data to test your model?
Do you have a clear description of the processes you intend to support with automation?
You should not be designing such that you require a lot of typing.
Perhaps there are lists and you can find the Member by searching/filtering with a few characters, and then update/create the necessary record.
See my stump the model info
Good luck.
