Solved

Linked Excel file in Access (financial statement format)

Posted on 2016-09-30
6
51 Views
Last Modified: 2016-10-01
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)

import
Grateful for your help.
0
Comment
Question by:pdvsa
  • 3
  • 2
6 Comments
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 41824542
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
0
 

Author Comment

by:pdvsa
ID: 41824655
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...
0
 

Author Comment

by:pdvsa
ID: 41824731
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
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 49

Accepted Solution

by:
Gustav Brock earned 500 total points
ID: 41824750
One trick is to create a Named Range in the Excel file covering the data area.

See the attached demo where the Named Range is labelled Test and then linked in the Access database having marked the first row as being field names. Copy the files to C:\Test.

This will preserve the coloumn/field names but you have no separation of the F1 values.

You could create several smaller Named Ranges each covering a selection of rows, but only the top area will hold the field names, the others will just have F1, F2, etc.

It will require a fair amount of work to obtain the result I think you are after. If this is a one-time task, copy-paste will probably prove most productive.

/gustav
ExcelLink.accdb
FC-file.xlsx
0
 
LVL 19
ID: 41824786
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.
0
 

Author Closing Comment

by:pdvsa
ID: 41824788
Nice!  I will play around with it and see if I can extacrt what i need.  thanks for the help!
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question