Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2014
    Posts
    2

    Unanswered: Help please - Novice with a basic query dilemma

    I've created an example of what I want to achieve in MS ACCESS 2010. For ease of explaining, I've placed it in an Excel spreadsheet which I've attached.
    The table on the left is the starting point and the table on the right is my desired outcome.
    I am dealing with over 30000 records which contains data for 350 people and the list of about 1000 possible courses they could have recorded against them as course codes.
    I want to change 30000 lines of data (multiple entries for each person) to unique lines of data for each individual.
    My only solution so far has been to work on creating about 300 tables which contain each individual courses data and the people who've achieved it.
    I am a novice at this and although I might achieve the correct result, it will take me days to do all the make table queries and then the same again for the update table queries.
    I know this is simple yet I can't get my head around it.
    I've tried crosstab queries but as soon as I add an extra course code, I get blank results. Maybe it's the relations setup.
    Again I say in a very kind and friendly way, HELP.
    Attached Files Attached Files

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so going down the route of normalised db design which is 'the' way to design in a relational database (virtually every database that supports SQL that is), you'd have (at least)
    a table for people defining everything pertinent to that person (name and so on)
    a table for courses (the course reference, name and so on)
    a table that defines what courses a person is doing, a so called intersection table

    when you want to generate your matrix table you'd use a pivot / crosstab query

    some links on table design and or normalisation
    Fundamentals of Relational Database Design -- r937.com
    The Relational Data Model, Normalisation and effective Database Design
    http://www.informationqualitysolutio...tionPoster.pdf
    Last edited by healdem; 09-25-14 at 06:18.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    incidentally you will have issues with your data as laid out. when sorting P100 will come before P15, so use P015 instead, or if you MUST use P15 then set a sort order column in the licence codes table.


    Code:
    ID	KnownAs	P100	P102	P105	P15	P30	P41	P52	P53	P88	P89	P91	P94	P99
    8090487	Smith, John				1	1	1		1	1				
    8097372	Jones, Ted				1		1	1		1	1	1		
    8099257	Bishop, Fred	1				1		1			1		1	1
    8142924	White, Bob	1		1	1			1			1	1		
    8143022	Wall, Anne	1	1	1		1		1
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Sep 2014
    Posts
    2
    Thanks for that, I'll work on it over the weekend.

Posting Permissions

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