Hello Ozgrid
Don't know if the headline above is very good, but I couldn't think of a better. I have a challenge, which is about optimizing an Access database.
Unfortunately I can't attach a sample Access file, so I hope to have summarized the essentials below. But the essense is I have an Access database with the following tables:
"Tbl_APO_Forecast_Xport", which holds data imported from an Excel file.
"Tbl_APO_Versions_4_PivotTool" holds data that are appended from the table above.
"Tbl_Hierarchy_Products" is a table holding data related to the two tables above.
Now, the first table holds some month data, and there are separate fields for each month. These data are appended to the second table, where data from alle months are consolidated to one field, however with information about what month it is to a "month" field (pls. see one of the 12 append queries – e.g. "Qry_Append_Tbl_APO_FC_Xport_YEE_2008_01_Jan". In other words, the idea is to arrange the months vertically (so to speak) instead of horizontally - hope this is understandable. (In case you are wondering why didn’t he transpose the data in Excel before importing, then this is because there are too many records for Excel. So I’m doing like this within Access instead of having some Excel VBA code create 12 Excel files/sheets that each need be imported.)
Now, this has worked fine, and it has been OK to create the 12 append-queries individually, but now I face a new challenge, which is weekly data, which I want to arrange in exactly the same way.
So I’m wondering (of course); is there another and smarter way to re-arrange or transpose my data, instead of having to create 52 individual queries. I’m thinking there HAS to be a way to hardcode this in some way in Access (vba), but my experiments so far has not succeeded.
Can anyone show me a way to get around this, using the sample month data attached?!
Thx in advance for any help. Hope I have explained myself sufficiently.
P.S: I don’t always need to append all months, which is another reason for wanting another way to control the append process.