Hey everyone,
I'm currently an intern just getting started with access, which not that many in my department use. I'm currently working on prototyping a proof of concept database for some financial data, and while everything I've done has been simple enough so far I've run into a bit of a problem.
All of my data comes from an excel document that is structured with the relevant fields, accordingly:
ID January EA1 February EA1 .... January EA2 .... etc.,
1 $52,600.00 $10
2 $0 $0
x $4141.41 $4141.41
That general kind of thing. That works fine in a spreadsheet, but we want something a bit cleaner, like this:
ID Month Year Payment
1 1 (January) 2015 $52,600.00
1 2 (February) 2015 $10
That way we can store several years worth of data in a single, cleaner location and reduce total database size.
My difficulty is that I don't know how to convert all the data from the first form, to the second. Each month's payment (Jan EA 2015, Feb EA 2015, etc.,) needs to get filtered into the payment column of the second storage method, but they can't be in the same line of course- they need to be split based on what column they belong to, etc.,
I feel like this should be pretty easy to do with macros or sql, but I'm not very proficient in either (the only programming language I'm proficient in is Java, and I haven't touched it in almost 6 years). Does anyone know how I could go about pulling this off?
Also, sorry if this thread's in the wrong location. I looked for a questions subforum but couldn't find one.
I'm currently an intern just getting started with access, which not that many in my department use. I'm currently working on prototyping a proof of concept database for some financial data, and while everything I've done has been simple enough so far I've run into a bit of a problem.
All of my data comes from an excel document that is structured with the relevant fields, accordingly:
ID January EA1 February EA1 .... January EA2 .... etc.,
1 $52,600.00 $10
2 $0 $0
x $4141.41 $4141.41
That general kind of thing. That works fine in a spreadsheet, but we want something a bit cleaner, like this:
ID Month Year Payment
1 1 (January) 2015 $52,600.00
1 2 (February) 2015 $10
That way we can store several years worth of data in a single, cleaner location and reduce total database size.
My difficulty is that I don't know how to convert all the data from the first form, to the second. Each month's payment (Jan EA 2015, Feb EA 2015, etc.,) needs to get filtered into the payment column of the second storage method, but they can't be in the same line of course- they need to be split based on what column they belong to, etc.,
I feel like this should be pretty easy to do with macros or sql, but I'm not very proficient in either (the only programming language I'm proficient in is Java, and I haven't touched it in almost 6 years). Does anyone know how I could go about pulling this off?
Also, sorry if this thread's in the wrong location. I looked for a questions subforum but couldn't find one.
