Solved

Linked Excel file in Access (financial statement format)

Posted on 2016-09-30
6
39 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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
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 18
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

708 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now