• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Access/Excel interaction

Garetjax

New Member
In my company we use SAP. Unfortunately, SAP does not quite natively provide the sorting functions that I wish it did. Accordingly, each month I pull my data from SAP, do a little bit of sorting/editing of the data in excel, and then pop that into an access database in a flat table as something I can query off of. I pull from SAP all the line items of activity for the area I am responsible for, and the data I toss into access looks something like this at the end (with many extra columns of data):

[pre]
Code:
Acc. Name  Amount    Fiscal per.   Classification
Account 1  $19.34         1        Material
Account 2  $31.48         2        Wage
Account 7  $00.32         1        Material
Account 1  $860.00        5        Overhead
Account 3  $2.00          2        Material
Account 7  $693.00        7        Salaries

This ends up being one giant access table with all data for all fiscal periods.  When I say giant, I mean I have several hundred accounts, with lots of line items worth of posting in them each month.  This is why I unfortunately cannot use Excel (even if it could support 1M lines).


I also have an excel sheet that looks like this (OH=Overhead, Mat'l=Material):


------------------PERIOD 1-----------    ----------------PERIOD 2---------------
Acc. #  Mat'l  Sal.  Wage   OH   Total   Acc. #  Mat'l  Sal.  Wage   OH   Total
Acc. 1  $xx    $xx    $xx   $xx   $xx    Acc. 1  $xx    $xx    $xx   $xx   $xx
Acc. 2  $xx    $xx    $xx   $xx   $xx    Acc. 1  $xx    $xx    $xx   $xx   $xx
Acc. 3  $xx    $xx    $xx   $xx   $xx    Acc. 1  $xx    $xx    $xx   $xx   $xx
Acc. 4  $xx    $xx    $xx   $xx   $xx    Acc. 1  $xx    $xx    $xx   $xx   $xx
[/pre]
Going all the way across for each fiscal period, with another table that totals out the information Note that $xx represents the total of the type of spending for each account.


I need to be able to present all that information in an excel sheet as backup information. Given that all the information is in the database, albeit with line item by line item detail - is there some way I can get it all into excel easily and smoothly? If you can't tell, I spent a lot of time on this post because if I could do this it would make life much easier for me. Please note: it is AWESOME if I can only get information 1 period at a time - but I was hoping to clearly convey that I would need information by period, by type of spending, by account.
 
Garetjax: If you go to Data/Import External Data/New Database Query you will be able to build an SQL Link into your Access Database.


This is a whole new experience, filled with traps and frustrations, but with bit of research and patience, you will be able to solve your problem.


I am no guru, so it took me a couple of weeks experimenting, but it was worth it in the end as I am pulling the data I need from a 800Mb Access File with 100's of 1000's of rows very quickly.
 
Back
Top