Solved

Linked Excel file in Access (financial statement format)

Posted on 2016-09-30
6
44 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
Backup Your Microsoft Windows Server®

Backup 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.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

896 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

14 Experts available now in Live!

Get 1:1 Help Now