Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2014
    Posts
    1

    Unanswered: (Novice issue) stacked variables in single field to each their own field

    Howdy,

    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?

    Curent
    STATE ID ###
    1 ___A__ 99
    1 ___B__ 88
    2 ___A__ 22
    2 ___B__ 33

    Desired
    STATE ID_A ID_B
    1_____ 99___ 88
    2_____ 22___ 33

    Explanation:

    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.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    well here's one appraoch
    Code:
    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';
    however that works assuming that the ID is always A for the first value and B for the second value
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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