Converting layout of imported data

[David Saywell]

Hi,

I have an Excel Spreadsheet containing budget data that I need to import
into my access database.

The Excel "Budget" table contains the following fields:

GLAccountId
JulyBudget
AugustBudget
SeptemberBudget
OctoberBudget
etc.

I want to store the data in Access with the following fields:

GLAccountId
Month
Budget

Effectively creating twelve records for each GLAccount (one per month).

How can I use Access to automate this process?

Thanks,

David

 

[David Saywell]

Hi All,

I think microsoft.public.access.queries may be better suited for my
question.

Have just found this posting (below) and will pursue my answer there:

"
To transpose columns to rows, you might use a UNION query.

For example, if you had a table with columns A and B that looked something
like this:

A, B
1, 2
3, 4,
5, 6

and you wanted to get a result that looked something like this:

Attribute, Value
A, 1
A, 3
A, 5
B, 2
B, 4
B, 6

you might use a query whose SQL looked something like this:

SELECT
"A" AS [Attribute],
[Your Table].[A] AS [Value]
FROM
[Your Table]
UNION ALL
"B" AS [Attribute],
[Your Table]. AS [Value]
FROM
[Your Table]

UNION ALL (unlike just UNION) will preserve duplicates. So, for example, if
you had two rows in your table that had the same value of A, you would get
two corresponding rows (instead of just one) in your result. Check the Help
for details.

Hope this helps.

Can anyone tell me how to make a table that transpose columns to rows in an
access query? I don't want to have to send it excel, transpose it and then
re-import it to Access. I would like to beable to do it all in access.
Thanks for your help!!!

MG

"