Solved

Linked Excel file in Access (financial statement format)

Posted on 2016-09-30
6
57 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 50

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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 50

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

739 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