03-13-14, 11:01 #1Registered User
- Join Date
- Mar 2014
Unanswered: (Novice issue) stacked variables in single field to each their own field
Quick version: Having imported a flat text file to a new table where all variables are stacked in the same field, how do I separate the different variables into their own fields, so that I can run an append query to other tables where this is already done?
STATE ID ###
1 ___A__ 99
1 ___B__ 88
2 ___A__ 22
2 ___B__ 33
STATE ID_A ID_B
1_____ 99___ 88
2_____ 22___ 33
I am an economics graduate assistant trying to update an existing 60y historical Access database from the Census with recent data in flat text files. I have good knowledge of Excel but very limited in Access. I am willing to google around and find out how to solve the issue, but at present I don't know where to start since my first round of googling didn't yield exact results. I am looking for a solution I could pass on to future grad assistants rather than a brute force method since new data will become available every year.
The flat text file has a single column of data using fixed width separation of information (I know how to split this). In each column is a unique numeric state id, the measure name, and the amount of the measure (ex. 5100019U99999 = 51000 ; 19U ; 99,999). The issue is that all the variables are stacked in a single column in the text file; whereas, in Access they are in separate fields. How do I make the shapes conform? If I could get the new fields then I could easily run an Append query to add to the existing tables.
I know that I can concatenate the rows so that all the variables will go into a single row with commas, but that doesn't make them into separate fields nor will it make the values go under those fields.
03-13-14, 14:35 #2Jaded Developer
Provided Answers: 59
- Join Date
- Nov 2004
- out on a limb
well here's one appraoch
SELECT a.State, A.Measurement AS FirstValue, b.Measurement as SecondValue FROM mytable AS A INNER JOIN mytable AS B ON A.State = B.State WHERE a.IDENTIFIER = 'A' AND B.IDENTIFIER = 'B';I'd rather be riding on the Tiger 800 or the Norton