Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2004
    Posts
    7

    Unanswered: Migrating several queries to one column of data

    Hi - I am building a database from government data on electric utility operations and maintenance expenditures, as part of a big multi-year benchmarking study. It involves about 200 companies and about 400 data items per company per year.

    The problem is, the government data is in a dozen separate Access Tables. To analyze ten years of this data, I need to take the dozen queries I have created from these tables for one year, and somehow move them into one very long column so that I can repeat this process for the other 9 years. Each of these dozen queries has 5 to 10 columns of numbers.

    Ultimately I need to migrate to Excel to do detailed statical analyis, but my go is to do that AFTER creating database in Access for all ten years. If I have to move it into Excel one year at time, I will be spending many days or weeks fussing with VLOOKUPs and data cleansing problems.

    Any help would be greatly appreciated.

    Thanks!

  2. #2
    Join Date
    Jan 2004
    Location
    Jersey, UK
    Posts
    108

    Re: Migrating several queries to one column of data

    I'm not sure if I understand the whole problem but I'll have a stab.

    If the columns of data are all the same for the 10 years, is there any reason why a Union query won't work? The Union query adds together results from other queries as long as both sources contain the same amount of fields (and I think data types).


    Regards - Andy

  3. #3
    Join Date
    Mar 2004
    Posts
    7

    Thanks - here's what I will try

    Appreciate your advice Randy. I looked into a Union query but it won't do the trick because I need to keep the columns separate. But I think what will work is for me to create a "counter" table with a single column with the numbers 1 through 12,000 on it sequentially (for the 200 data points for 600 utilities per year). Then, for the dozen or so queries of data, I create tables that have NON-overlapping sequenced portions of this "Counter" in each of their leftmost columns. Then a master query will join the dozen tables together with the counter column on the left, utility name, data description, and the actual data in the fourth column for the year. It will look like a staircase which I can then compress into one column:

    Counter.......Data ........Data.......Data
    Table 1.......Table2........table3.....table4

    1.................. a
    2.................. b
    3..................c
    4 ............................. d
    5 ............................. e
    6 ...............................f
    7 ............................................. g
    8 .............................................h
    9 ............................................. i

    Hopefully I can then use the same Access file to reload the 9 other years' data so that it is somewhat automated.

    Thanks again!
    Last edited by krinidude; 03-18-04 at 01:20.

  4. #4
    Join Date
    Jan 2004
    Location
    Jersey, UK
    Posts
    108

    Re: Thanks - here's what I will try

    Sounds complicated and I still don't get what you're trying to acheive but I'm glad you've found a potential solution.

    Just in case you haven't looked into it already, you might find that a cross-tab query does some of the work that you need to do.


    Good luck!

  5. #5
    Join Date
    Mar 2004
    Posts
    7

    success

    Thanks for your advice Randy. After two weeks I have gotten it all to work. It ended up involving a combination of Make Table and Append queries, where I have to go back into the original multi-column tables and rel-abel one column at a time, so that I end up with everything in one long column for each year. After much struggling, I now have a process...it ended up taking 15 minutes per year, to get each of the past ten years' data into the right format.

    So I've built the final database, which has 46,000 lines per year, with 280 data points for each of 178 utilities...whew! Now it is time for number-crunching and benchmarking, and I am expecting some very interesting results.

    It all would have been a lot easier if the original gov't data added a column that uniquely identifies each record...but beggars can't be choosers and the price (free) was right.

  6. #6
    Join Date
    Jan 2004
    Location
    Jersey, UK
    Posts
    108
    Well done krinidude, sounds like you're on your way to becoming an Access guru!

Posting Permissions

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