Trouble converting excel layout to query does anyone have idea

[KWhamill]

Some times the things that look good in excel don't translate well into
Access. or more importantly, I need to turn financial, information on a
spread sheet into a text file to give to an uploading utility for a database.
the cunudrum i am facing is the layout on the spreadsheet looks something
like this:

stat value trans# Account from AMT Account to Amt
Income amt
alpha# abc 12345 account#1 $$ account#2 $$
account#3 $$

the format for the Uploading utilty needs to look like this (see below) in
*.csv:
stat value trans# Account AMT
alpha# abc 12345 account#1 $$
alpha# abc 12345 account#2 $$
alpha# abc 12345 account#3 $$

Changing the Spreadsheet is a none starter that was actually my first
thought. is there a way to do this in a query or two. I'm going to have to
put it in a macro and run it 5 times. macro within a macro, there is a reason
for this.
thank you for your help.

 

[KARL DEWEY]

Somewhere along the line you must rename the 3 AMT's as AMT1, AMT2, and AMT3.
Use a union query like this --
SELECT stat, value, trans#, [Account from] AS Account, AMT1 AS AMT
FROM Table1
UNION ALL SELECT stat, value, trans#, [Account To] AS Account, AMT2 AS AMT
FROM Table1
UNION ALL SELECT stat, value, trans#, [Income] AS Account, AMT3 AS AMT
FROM Table1;

 

[KWhamill]

Thank you Karl,
I am completely unfamiliar with union queries and i'm wondering if you could
provide just a little bit more detail about how to set it up. For example is
the union query the basis for the output format or should it be the output
itself. I ask this because there are a couple of fields which in this case
are blank but they will need place holders. So should i add those to the
union query or to a subsequent query based on the union query? Also, in case
i hadn't mentioned this before, the imported data is from a spreadsheet in
excel and the out put needs to be a .csv file. does any of this have an
implication for how effective the union query is? these are just some of the
questions i thought of off the top of my head. but i thank you for your help
R,
Karl