asked on

Linked Excel file in Access (financial statement format)

Experts,

I have an excel spreadsheet in the format much like a financial statement
Dates along the top in rows
account along the left first column

When I link (or import), access dumps all of the accounts into a single column "Field 1" and I find it difficult to develop queries.

I think what I am looking for is how to convert the excel file to a table with all the account names as separate fields and not all dumped into one field named "Field 1". (see pic below)

Grateful for your help.

Gustav Brock

Did you walk through the import/link wizard? It should allow you to make some adjustments.

Or could you provide a sample file to play with?

/gustav

pdvsa

ASKER

Hi Gustav,

Yes, i actually did follow the wizard.
I am on the road now.
I will provide a sample file later today.

Thank you once again...

pdvsa

ASKER

Gustav, I have attached the file. let me know what you think. I thought that a solution could be a cross tab query. This one website discusses issues with importing files of this type (fmsinc.com) but I could not implement what they were referring to.

have a good day...
For-Gustav---FC-file.xlsx

ASKER CERTIFIED SOLUTION

Gustav Brock

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

crystal (strive4peace) - Microsoft MVP, Access

another method is is to have a hidden sheet that is linked to the Access table you want to fill and then write the values onto that sheet. If it is a new record, values will be created after the last row. If it is updating a record already there, find the row to fill and write values to that row.

pdvsa

ASKER

Nice! I will play around with it and see if I can extacrt what i need. thanks for the help!