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;
front bumper 3
rear bumper 4
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
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"
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.
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???