Results 1 to 3 of 3
  1. #1
    sureshot is offline Novice
    Windows XP Access 2007
    Join Date
    Oct 2009

    need some advice building DB (noobie)


    I'm quite new to access and i was wondering if i could get some advice structuring tables and relationships so i get it right the first time. This is what my database will have to do.

    Customer information, this data will be entered once. The customer will have a name, ID and customer number

    Key - CustomerID
    Customer Number
    Customer data...

    Each customer will have 6 appointments, these appointments will be referenced by Customer Number and Date. On each appointment i will have to enter a questionnaire they fill out, this is the same questionnaire for all 6 appointments.

    On the first appointment there is a second questionnaire that is only filled out this one time.

    On the last appointment there is a questionnaire that is only filled out this one time.

    I would like to link all of the appointments by customer number and date.

    I have 6 tables, one for each appointment and each appointment table has customer Number and a date field. Am i on the right track or is there a better way.


  2. #2
    hawzmolly is offline Advanced Beginner
    Windows 10 Access 2010 32bit
    Join Date
    Oct 2008
    Green Bay, WI

    Block it up

    The best thing you can do is to draw yourself a flow chart (aka Needs Analysis) that encompasses all of your criteria. Set up your analysis using these 7 steps.

    1 - Overall system design - What will meet the needs of your end user, your coworker, and yourself?
    2 - Report Design (Output) - What are your expected outputs in the smallest level of detail?
    3 - Data Design (Fields) - Decide on the fields you will need to capture the data. Make a list of these fields noting the ones that are the same or very similar. After you have all of your fields down you will then normalize them into groups that collect the same type of data i.e., customer related, sales related, and so on.
    4 - Table Design (Relationships) - THE most important step called "Normalizing to 3rd Normal Form" - 1NF - Simple - Eliminate any repeating groups of data; 2NF - Relies on each table having a "Primary Key" that makes each record a unique record; and finally 3NF - Relies on each table having all the fields in the table directly related to the primary key field.
    5 - Field Design (Validation) - Actually create your tables and define your fields for those tables.
    6 - Form Design (Input) - You then create the forms to be used to input the data to your tables.
    7 - Menu Design (Automation) - The last step is to create a pleasing menu that your staff will use to input and output the data you are interested in.

    I did not come up with this stuff myself. I used a book to help me design the DBs I needed. Do yourself a favor and purchase a book on Access. Since you are using Access 2007 may I suggest the following:

    Microsoft Office Access 2007 Bible (ISBN 0-470-04673-2) which is available at Barnes & Noble or on line.

    I have purchased several books on Access and found the one listed as the best one for me. It is easy to understand and in my opinion has the best index of all. I am still very much a novice. You simply can not learn Access without help and I think this forum is the best one.

  3. #3
    sureshot is offline Novice
    Windows XP Access 2007
    Join Date
    Oct 2009
    outstanding, thank you for the advice.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. advice on planning tables
    By justinm1015 in forum Access
    Replies: 1
    Last Post: 09-16-2009, 02:27 AM
  2. Problem with building SQL string (VBA)
    By cdpeck in forum Programming
    Replies: 1
    Last Post: 09-15-2009, 04:25 AM
  3. Advice on How to Create these Tables
    By rochy81 in forum Access
    Replies: 2
    Last Post: 05-04-2009, 04:32 PM
  4. building a distribution package
    By BevA in forum Access
    Replies: 0
    Last Post: 05-26-2006, 07:04 AM
  5. Need advice on what I have so far
    By rumplestiltskin in forum Database Design
    Replies: 2
    Last Post: 05-25-2006, 12:48 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
Other Forums: Microsoft Office Forums - Senior Forums