Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Need design Ideas (A2K)

    I have been asked to create a database for site visits. The checks for the visits will be the criteria
    below. (I left out the target number values for the items.)
    The numbered items are just section headings.
    The answers for all the lettered items will be Yes/No
    There are a fixed number of sites but they will have multiple visits.
    My question is should I create 1 Site table and then a large table with all the lettered(30) questions and join them.
    or Create 1 site table and a table for each numbered section and join them somehow
    with a one -to-many or many-to-many.
    Your input is appreciated.
    <pre>Site Visit Checklist
    Site:
    Date:

    1. Site Performance Status Report
    a. Response Time:
    b. Call Back:
    c. Customer Assistance:
    d. First Time Fix:
    e. Calls per tech:
    f. Labor Hours:
    g. MA %:
    h. Parts Cost per Call
    i. Machines per Tech:
    2. Adjusted Service P&L
    a. Parts Expense:
    b. Salaries:
    c. Copy Expense:
    d. Total Cost and Expenses:
    3. Asset Management
    a. Days on Hand:
    b. Average Freight Cost per Shipment:
    c. Inventory $ discrepancy per quarter:
    4. Employee Improvement and Development
    a. # of Product Training during current year
    b. # of Self Pace Training during current year
    c. % of CSS training completed
    d. Certifications
    e. Employee Performance Improvements
    5. Corporate Initiatives
    a. Remote

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Need design Ideas (A2K)

    I'd create a table with one record per response:

    <table border=1><td align=center>SiteID</td><td align=center>Visit</td><td align=center>Question</td><td align=center>Response</td><td align=center>37</td><td align=center>1</td><td align=center>1</td><td align=center>True</td><td align=center>37</td><td align=center>1</td><td align=center>2</td><td align=center>False</td><td align=center>...</td><td align=center>...</td><td align=center>...</td><td align=center>...</td><td align=center>37</td><td align=center>2</td><td align=center>1</td><td align=center>True</td><td align=center>37</td><td align=center>2</td><td align=center>2</td><td align=center>True</td><td align=center>...</td><td align=center>...</td><td align=center>...</td><td align=center>...</td><td align=center>38</td><td align=center>1</td><td align=center>1</td><td align=center>False</td><td align=center>38</td><td align=center>1</td><td align=center>2</td><td align=center>True</td><td align=center>...</td><td align=center>...</td><td align=center>...</td><td align=center>...</td></table>
    The question numbers are linked to a Questions table with question number, question text and section number. Section number, in its turn, is linked to a Sections table with section number and section title.

    The above table setup makes it easy to tally and summarize data in various ways (across questions, across visits, across sites, ...)

  3. #3
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need design Ideas (A2K)

    Hans,
    I think I get the idea. I created the tables but I can't figure out how to relate them so I can enter data on a form
    showing the questions for different visits. I must be brain-dead today because I can't see it.
    I attached what I have so far.

    Thanks for your help
    Scott

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Need design Ideas (A2K)

    I'll be offline for a few hours, but I'll get back to you later.

  5. #5
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Need design Ideas (A2K)

    The only thing I would add to Han's suggestion is to make sure you insert the date of the survey(s) as you know someone will want to know how many surveys per month, per quarter, etc., for all sites or how often a particular site is visited. You may also want to add an id of the person that conducted the site visit linked back to a master employee id table so you can answer the question of how many sites a particular person is doing per year.

    Just curious, would calls per tech be a yes/no or number and would they want to know which techs dealt with the visit?
    Regards,

    Gary
    (It's been a while!)

  6. #6
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need design Ideas (A2K)

    Gary,
    Thanks for the additional info. Each question has a target value which I didn't include.
    If the calls per tech didn't reach the target value say 3 then the box would be checked no.
    The questions aren't about an individual, they are about the site location as a whole.
    Scott

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Need design Ideas (A2K)

    I have attached a version of your database with a slightly modified data design (I split the Visits table into a Visits table and a Responses table), and a form with two subforms. The form is *NOT* meant as a finished product, just to give you an idea. There is a bit of code behind the first subform to assign the next visit number when you create a new visit, and to create response records.

  8. #8
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need design Ideas (A2K)

    Hans,
    Thanks. I will look over the way you did this.
    Thanks.
    Scott

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •