Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2005
    Posts
    7

    Unanswered: Moving records to new table with multiple fields

    I am in dire need of help!

    I am collecting inspection data via a PDA into a MS Access database. The data is basically the part and the defect code. There are many points inspected (80+) and to keep the PDA process simple I have used drop down boxes with the part name and drop down with the code so that the table created, basically (other then order information) has a field "part" and a field "code". Imagine a simple inspection of a car, the parts would be front bumper, hood, driver's door, roof, trunk, rear bumper, passenger's door and the code would be from 1 to 11. A typical table would look like;

    part code
    front bumper 3
    roof 6
    rear bumper 4
    trunk 11


    What I want to do is take those records and move them over to a table that has every single part as a field and put the corresponding code into so it would look like the following;

    front bumper drivers door roof trunk rear bumper passengers door
    3 6 11 4

    The formatting did not come out right. The field names in the new table are front bumper, drivers door, roof, etc and the individual codes for those parts is placed in those fields - 3 is in the bumper field, 6 in roof, 11 in rear bumper, etc

    I hope my explanation make sense.

    I thank you for any comments or suggestions
    Last edited by Kennan345; 04-08-10 at 14:52. Reason: formatting did not come out right

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You can get the formatting using [code ][\code ] tags (remove the space). Normal HTML removes extra spaces and tabs.
    Code:
    SELECT MAX(SWITCH(part = "front bumper", code)) AS front_bumper
         , MAX(SWITCH(part = "roof", code)) AS roof
    FROM mytable
    I hope you have another column in your table to differentiate one inspection from another....

  3. #3
    Join Date
    Nov 2005
    Posts
    7
    Thanks pootle flump for the quick reply to both of my problems

    Your suggestion worked great. Now I have one other version of my problem that you may be able to help as well.

    Same database, same data but I want to put 2 codes into one field, Say the bumper has 2 defects on it? a 2 and a 6. Is there a way to combine them s that they show up as "2 , 6 "?? A report will be generated that would show "bumper - 2 , 6"

    Thanks again

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Difficult in SQL generally and fairly tricky in Access.
    One of the reasons there are no inbuilt functions for this is because it would (usually) be considered a violation of the principle of Atomicity. It is worth noting that atomicity has become a contested topic in recent however I would urge you not to store your data like that - it is difficult to get in your database and also becomes difficult to use.

    If you can have several instances of a defect for a part of a car then you would be better to consider a one to many relationship.
    More details about database design here:
    The Relational Data Model, Normalisation and effective Database Design

  5. #5
    Join Date
    Nov 2005
    Posts
    7
    Thanks pootle flump, I'll look over the link.

    I'm really not looking to store the data in this manner, the way it comes off of the PDA is fine for storage and I have a one to many relationship where one table stores the customer and order information and the defects link to it based on the unique sales order number.

    I'm trying to make a report that matches the look of an existing hand written sheet (see attached). Maybe I'm looking at it from the wrong direction but if the table had fields for each of the inspection points the report would fill them in easily and your code will work perfectly but there are times when we could have a part that has more then one defect. No problem when you're writing it in and I have the PDA excepting 2 defects on the same part but I'm having an issue getting it to the report. That's why I'm trying to transfer the data to a table that has every part from the inspection sheet as a field.

    But as I said maybe I'm going at it the wrong way???
    Attached Files Attached Files

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    The format data is displayed in should rarely impact the format it is stored in.

    Creating CSV lists of values for a report is a perfectly acceptable practice.
    I can't find a code example now. There almost certainly is one in the code bank though...

  7. #7
    Join Date
    Nov 2005
    Posts
    7
    Thanks for all your help on this one pootle flump.

    You've helped me out a great deal and I've learned a lot!!

Posting Permissions

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