Help with normalization for my first database

[basstwo]

Greetings! I will describe the need and then list what I have picked so far. The database will hold a list of names, phone numbers, the group they each are in, a list of dates. I need to be able to assign each name to a date, allowing for me to then contact them to see if that date works for them. If it does I wil note this. If they can’t be there for that date I will need to select another person from their same group (if possible) to call *them* and see if they can be there for the date. I would then swap the assigned dates for the two people.



Here are the design thoughts I have:

• Tables
  
  • 'People' table with PID, First_Name, Last_Name, Group_Name, Phone
  • ‘Classroom’ table with ClassID, ClassName, number of people needed
  • 'Dates' table with DateID, Class_Date
  • 'Results' table with ResultID, PID, DateID, ClassID, and Result (said yes to the date, said no)
  
  
• Reports
  
  • ‘Group’ report for each Group with names and dates
  • ‘Date’ report for each Date with names
  • ‘Phone Call’ report with upcoming Dates, names, numbers
  • ‘History’ report with history showing what people were there on what Dates already
  
  
• Forms
  
  • ‘Entry’ form to enter a person with name, phone, group, dates served
  • ‘Phone Call’ form to show the next person to call, what date they are assigned to, and their response (yes/no). Record in ‘Results’ table
  
  
• How this would all be used:
  
  • Use the entry form to enter each name, phone, group
  • Run a module that would assign a ClassID and DateID to each PID
  • Each week run the ‘Phone Call’ report and use this to call the first few people on the list who have been assigned to the upcoming date
  • Note if they said Yes or No in the ‘Results’ table through the ‘Phone Call’ form
  • Get enough “yes”s for the upcoming date (6 people)
  • If you get a “no” the Form could show you the next person in the same group so you would know who to call. You would then swap the two dates for the two people
  • Periodically run the ‘Group’ report so each group can see what dates each person has assigned to them
  • Each week run the ‘Date’ report (after completing the phone calls) so you can see who is assigned to that date
  • Periodically run the ‘History’ report to see who was there on what date.

How does this all sound? I tried making this in Excel (my area of strength) but now see that Access is the better tool for this. I am hoping that my modules will move over pretty easy.

[June7]

Table structure looks good at quick glance but you really want to play phone tag? Or do you move on to next person if someone not reached immediately on initial call? What about using Outlook meeting planner? Or Access Collect Data?

[basstwo]

Can you give me links to Access Collect Data? I have worked with Outlook done but writing VBA for it has been a pain.

[June7]

Start with a search of Access Help for info on Collect Data. Google search should also get a lot of hits. You can see the buttons for this utility on the External Data tab of the ribbon.

[basstwo]

Would you suggest that I start with a template? If so, which one?

[basstwo]

I have been trying to start making tables. I made the first ones fine. But now i am making the results table. when i go to add a column from one of the other tables I get a Lookup Wizard. I am not sure what it is asking me. I was just going to add the PID field. But it is asking if I want to add HostName, First-time , ect. How do I know what I need here ?

[June7]

What you are encountering is basic to Access functionality and would be covered by an introductory book with tutorials. I think you should invest a few dollars and some time in going through one. Your learning experience will be faster than depending on responses to threads in a forum. A forum is not really an appropriate venue to learn basics. You will have question after question after question and we would end up rewriting the book here.

Just to get you started, review
http://support.microsoft.com/kb/304462
http://access.mvps.org/access/lookupfields.htm.

I don't know of any template that fits your requirements. The Contacts db might be close but I am not that familiar with it and it might be set up for web anyway.

[basstwo]

Do you have a suggestion of a good Access book? I know there are many out there. I am fairly familiar with VBA and feel comfortable there. But when I read that second link on the evils of Lookup Fields I can see that there is great benefit in getting some hard opinions instead of "here is what this feature does". I appreciate the editorials. Is there a book on Access that is written at this level and is more than just a rewrite of the help documents?

[June7]

I have one called Microsoft Access 2002 (Adamski & Finnegan - pub by Course Technology) but never really used it because I had a face-to-face mentor when I got started with Access. Try buying used books from Amazon. Bound to get at least one good reference. That's what I did when I was learning VB6. Bought a bunch of old used books, some for less than a $1. However, problem with older references can be that although basic concepts are the same the application interface has changed quite a bit.