Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Linked Excel file in Access (financial statement format)

Posted on 2016-09-30
6
Medium Priority
?
74 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 52

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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 52

Accepted Solution

by:
Gustav Brock earned 2000 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 22
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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

971 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