Results 1 to 10 of 10
  1. #1
    asaloba is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2012
    Posts
    5

    I am stuck!


    Hello Guys
    Please I do have a problem. I am trying to design a database for annual report for a school of many programs. I wanted to create a table for measuring success of Goals. I have about 5 Sections (areas of focus)and each section has about 6 categories and each categry has about 8 Measures with reponsese for each measure.
    I did create a Table that has Section Field (Look up format I manually type the list of the sections), Category Field with Look-up format my problem is what to do with the Measures and responses to the Measures. Please remember, that each category has about 8 measures and each measure with have a response.

    Please educate me.

  2. #2
    RayMilhon is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,065
    First thing I suggest is make sure you understand DB Normalization. Can't emphasize it enough. You want your structure to be in 3rd normal form. So your design as specified above should includeQuestion's that need to be answered:1. Could any single measure apply to more than 1 category?2. Could any single Category apply to more than 1 section?For each yes answer you would need a lookup table and a linking table. For exampleYes to #1 You would need a Measure table for each measure. a category table for each category and a linking table to link the category to the measureCategoryCategoryID CategoryDesc1 some desc2 some other descmeasuremeasureid measure desc1 some measure2 some other measurecatmeasurelinkcatid measureid1 11 22 1

  3. #3
    RayMilhon is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,065
    Sorry didn't display the structure the way it looked on my screen.

  4. #4
    asaloba is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2012
    Posts
    5
    Thanks a lot RayMilhon.
    Pls have a look at the excel format of the form:
    SECTION 2: RESEARCH
    Category Measures Response
    A. Faculty Scholarship 1 # of faculty peer reviewed articles
    (for ≥ 50% time) 2 # of faculty articles and book chapters
    note: If >1 faculty involved in an article, count as 1 article, not 2 articles 3 # of faculty presentations at the local level
    4 # of faculty presentations at the national level
    5 # of faculty presentations at international level
    Please attach a list of citations for all articles & books published
    B. Student Scholarship 1 # of students/alumni presentations/publications based on academic coursework
    2 # of student state and national presentations and publications (peer and non-peer reviewed)
    3 # of graduate assistantships

  5. #5
    RayMilhon is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,065
    Based on what you provided look at the structure below: (hopefully this displays the same way it looks at this moment after I post it.)Category Categoryid number PrimaryKey catdesc text Measure MeasureID number primarykey CatID number foreignkey MeasDesc text Articles ArticleID number primarykey ArtDesc text Authors PersonID type name otherfields as req Datatable ArticleID number foreign key to articles table All 4 of the foreign key fields would combine to make the primary keyPersonID number Foreign key to authors CategoryID number foreign key to category MeasureID number Foreign key to Measure response Not sure as I don't know exactly what it is your trying to save here.

  6. #6
    RayMilhon is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,065
    It didn't.
    Attached Thumbnails Attached Thumbnails datastructuresample.jpg  

  7. #7
    asaloba is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2012
    Posts
    5
    Thank you RayMilhon.from my understanding , you suggested that I should have tblsection, tblCategory, tblmeasure, tblResponse(takes both number and text) and the 5th table will be the Master tyhat will contain sectionID, CategoryID, MeasureID, ResponseID.
    I want you to take a second look at the structure in Excel format:

    Section(Text only) Total= 5 Category(Text) total=17 Measure(stores only Text) total = 67 Response(will store text and Numbers) Total=67
    1 A 1 3
    2 6
    3 0
    4 No
    5 0
    B 1 2
    2 No
    3 1
    2 A2 1 3
    2 6
    3 0
    4 yes
    5 0
    B2 1 2
    2 yes
    3 1
    3 A3 1 3
    2 6
    3 0
    4 yes
    5 0
    B3 1 2
    2 No
    3 1
    C3 1 3
    2 6
    3 0
    4 yes
    5 0
    D3 1 2
    2 No
    3 1

  8. #8
    asaloba is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2012
    Posts
    5
    That wasn't good representation
    Check this :
    Attached Files Attached Files

  9. #9
    RayMilhon is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,065
    Take a look at the attached zip file. there are 2 files 1 is a Visio Document the second is the visio document printed to xps format. In case you don't have access to visio.
    Attached Files Attached Files

  10. #10
    asaloba is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2012
    Posts
    5

    Smile Thank you

    Quote Originally Posted by RayMilhon View Post
    Take a look at the attached zip file. there are 2 files 1 is a Visio Document the second is the visio document printed to xps format. In case you don't have access to visio.
    Thank you, that was good outline. It made me a pro.

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

Similar Threads

  1. stuck at some points !!
    By almoj4ever in forum Access
    Replies: 3
    Last Post: 09-25-2011, 05:44 PM
  2. stuck on the best way to do this
    By token_remedie in forum Queries
    Replies: 1
    Last Post: 09-22-2011, 10:19 PM
  3. Can anyone help please... I am stuck
    By Casper2012 in forum Forms
    Replies: 7
    Last Post: 08-15-2011, 11:27 AM
  4. Help I am stuck
    By Darkglasses in forum Database Design
    Replies: 10
    Last Post: 03-04-2011, 09:10 PM
  5. Newbie Here & Stuck
    By FOZILD in forum Access
    Replies: 5
    Last Post: 09-24-2009, 08:26 AM

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