Results 1 to 3 of 3
  1. #1
    saccolicious is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    1

    Need help re-organizing data

    I just downloaded a data set that has over 60 million entries, since it was so big I split it into many files. The data includes all previous earnings of a 1% sample of workers and beneficiaries covered under Social Security.

    I would much rather be using this data set in Excel, but because it is so big, and I need to do many calculations, Excel kept freezing up. I don't know how to use Access, but all I want to do is to be able to do calculations using this earnings data.

    Unfortunately the data is presented in a very unhelpful and inconvenient way. Instead of having one row per individual, and a column for each year, it is presented like this
    ID Year Earnings
    1 1998 2400
    1 1999 3000
    1 2000 3200


    2 1970 12000
    2 1971 14400
    etc.
    where the ID# indicates the individual.

    but I need it presented like this.
    ID 1998 1999 2000
    1 2400 3000 3200
    etc

    I really need there to be columns for every year from 1951 to 2006, and since many people won't have earnings from those years, I would like there to be a 0 in the cell if they did not work that year. Again, I don't know how to use MS Access because I usually use Excel. This may be pretty simple, but it's not what I'm used to.

    I would really appreciate any help in trying to use and re-organize this data. It is for research that I am currently working on.

    Thanks,
    Anthony

  2. #2
    recyan's Avatar
    recyan is offline Expert
    Windows 2K Access 2000
    Join Date
    Dec 2011
    Posts
    662
    Check out keywords : crosstab queries & Nz() function, in case you have not done it already.

    Thanks

  3. #3
    alansidman's Avatar
    alansidman is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    This link should help also.

    http://allenbrowne.com/ser-67.html

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 17
    Last Post: 01-12-2012, 11:25 AM
  2. Replies: 1
    Last Post: 12-21-2011, 02:11 PM
  3. organizing serial numbers and quantities
    By Diomeneus in forum Access
    Replies: 0
    Last Post: 11-14-2008, 03:17 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums