Need Help Creating Access Database From Excel Document

[phifer2088]

Hello,

I am working on importing my first database from an excel document and I need a lot of help because I am not a database programmer or even close. I have created one in the past with the help from this site and others.

I have attached the excel document in a zip file. Excel documents are not an authorized file type. This is going to be used for volunteers to input the shift and day they would like to work. I want to start by requiring volunteers to contact me and I will add them to the list of possible volunteers and I will give them a unique identification number.

When they contact me I will require all the information that is currently in the excel document (first, last, supervisor, ph number) I will then add them to a list of possible volunteers.

Then I would like a form they can access via sharepoint for volunteering what shift they can work. On this form it will have a box to input their unique id number and then select a shift from a dropbox (that has not been filled and auto populates). This will add them to the schedule.

Then I need a way to pull a report that shows all the volunteers and does not include their ID numbers.

As you can see the supplied document is full for March. I also have a second sheet that is blank for April.

Thanks in advance.

I imagine there will be tables for
Volunteers (no duplicates in this list)
Supervisors (1 supervisor can be over multiple Volunteers)
Dates/Shifts (there are 2 shifts per day, there are 2 slots per shift)

[orange]

Database is very different than spreadsheet. Access is built on a different object model than Excel.
I strongly suggest you do some reading/practicing of database design, before attempting to convert a spreadsheet application to database application.
Here is a tutorial that will help you with database design. You have to work through it, then apply what you have learned to your own set up.
Others have found the tutorial very helpful.

Good luck.

[NTC]

The classic design would be to have a table each: Volunteers, Supervisors, Shifts
Then a table that ties them all together maybe named: Assignments
The Assignments table has fields: Volunteer, Supervisor, Date, Shift
Volunteer, Supervisor and Shift fields are all LookUp field types to their respective tables.

As for a SharePoint form for remote data entry - that is a whole other issue and potentially fairly complicated.

[rpeare]

Just to add to what NTC has said, Volunteers and supervisors can go on the same table if you're collecting the same information about everyone (name, address, contact info, etc) and identify them with another field defining their role.

[rpeare]

One other thing.... using a database on sharepoint really restricts what you can do with a database, you can't run any code at all, only macros, so keep that in mind as you design your database. It may not be an issue for you for something as simple as this database.

[phifer2088]

Thank you. I have started this project and have 2 tables.
Volunteer Dates (Date, Time, ID)
Volunteer Info (First, Last, Phone, E-Mail, ID, Supervisor First, Supervisor Last, Suprvisor Phone) ID number is Primary Key

I have also created 2 forms so far.
Add Volunteer (All fields are required and ID number should be a 10 digit number)
Add Volunteer Date (Date, Shift, ID number)

Add Volunteer Problems
When adding an ID number if the field does not contain 10 digits Access adds leading zeros. How do I prevent this?
If the same ID number is used it updates the current entry. How do I prevent this? While there will be a form for updateing information I do not want it to be done on this form.

Add Volunteer Date Problems
How would I ensure that only 2 positions, per time slot, per day are volunteered for?
How do I require the ID number match an ID number already populated in the volunteer info table? (I do not want a dropdown because that could cause volunteers to select a random persons information)

[ssanfu]

Be aware that "Date", "First" & "Last" are reserved words in Access and shouldn't be used for object names
See http://www.allenbrowne.com/AppIssueBadWord.html

Also use only alphanumeric characters (except the underscore) in object names. No spaces, punctuation or special characters.
http://access.mvps.org/access/tencommandments.htm

"ID" is a poor field name. "VolDatesID", "VolInfoID" would be better names.