Solved

Linked Excel Spreadsheet

Posted on 2016-09-01
13
53 Views
Last Modified: 2016-09-03
Experts,

I have an excel sheet that is linked.
It has dates at the top
It has accounts along the left hand side (its like an Income statement)
I thought I would be able to sum for the accounts along the left hand side'
However, what I see in the query design after dragging the linked table in are only the dates and not the field names along left side (they are under a field called "F1")
I cant really make much use of this file because I cant sum for an account.  
I imagine an expert has some experience with linking excel files of this nature with dates along top and accounts on left.

How would I be able to sum for a particular account?


2

1
0
Comment
Question by:pdvsa
13 Comments
 

Author Comment

by:pdvsa
ID: 41779558
Here is what the query design looks like:

QueryDesign
1
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 41779628
If you filter on one account (F1) only, there is only this one to "sum" - and as your values shown for the example account are Null, you don't even have one value, just Null.

This is probably not what you are after, so I guess you'll have to rephrase your question or provide an extended example and also the expected output.

/gustav
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 41779695
remove the equal "=" sign from your criteria

criteria   'Daily Draw/ (Repayment)'
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41780484
If your query is not returning any rows, it is probably because the account name is different from what you typed.  Try copy and paste since that should pick up the correct number of embedded spaces.


This file will be a royal PITA every time you use it since the column headers will change.  If you need to automate this, you will need to use VBA to normalize the data into a correctly designed table where the columns are fixed.  Do you need help with that?
0
 

Author Comment

by:pdvsa
ID: 41781057
Sorry for my tardy response..I was tied up with my real job.  Appreciate the responses.  Let me give a bit more info.  
The field names will not change but the column headers (the dates along top) could change (this is an answer to Pats question above).  ==>Yes, VBA would be interesting.  

OK so what I am looking for is to sum: Daily Draw / (Repayment) for each of the months along top but for only where it is NOT NULL and >0 (do not want to pay attention to the negatives).  .  I think the formula would need not to reference any hard coded dates (ie 9/2/2016) in the column but a more general reference (Not sure how to explain that properly).

as FYI:  I will be either linking or importing this excel file on a daily basis to get updated info for current and previous day.  Im not sure if importing or linking is better.  I do have a named range.  It works well but I think if its linked, the excel file on the network is locked so I am siding with importing the data.  

any suggestions are always welcome.  I am grateful for any info.  

here is a new screen print of the excel table in Access.  I have hidden some rows so you can see "Daily Draw / (Repayment)" has data in some of those date columns along top.  Let me know if need more of a description of what I am trying to accomplish.
 tbl screen print
0
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 500 total points
ID: 41781397
First, copy the file to a (local) work folder to not lock the original.
Second, link the Named Range in question.

Then do something like this:
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim Index As Integer
Dim MonthSum As Currency

Set db = CurrentDb
Set rs = db.OpenRecordset("Select * From NameOfYourLinkedRange Where F1 = 'Daily Draw/ (Repayment)'")
If rs.RecordCount = 1 Then
    For Index = 2 To rs.Fields.Count - 1
        If Nz(rs.Fields(Index).Value, 0) > 0 Then
            MonthSum = MonthSum + rs.Fields(Index).Value
        End If
    Next
End If
rs.Close

Set rs = Nothing
Set db = Nothing

Open in new window

/gustav
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:pdvsa
ID: 41781531
Gustav, that looks nice.  Where should I put that code (ie under a button?)  Is the code to "look" at where the copied file is?  I do have it imported into the db.
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 41781550
Yes, you could place it in the OnClick event of a button and add this line:

    MsgBox "Sum is: " & Format(MonthSum, "Currency"), vbInformation

Replace "NameOfYourLinkedRange" with the name of your table, linked or imported.

/gustav
0
 

Author Comment

by:pdvsa
ID: 41782045
gustav,

I have pasted the code on a button and added the MsBox line as well.
I click the button and the error its giving me is : runtime 3061 "too few parameters"  and highlights the yellow line as below.  

Let me know what is next.  thank you.  

error
0
 

Author Comment

by:pdvsa
ID: 41782083
I think that since I imported the table, the F1 field is now named "Field1".  I changed the code from F1 to Field1 and I didnt get the error but I also did not get a msgbox stating the sum.   Not sure if I have placed the msgbox in the correct area.


tablimported
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 41782284
Your code seems correct, and the line with the MsgBox cannot be skipped, so something else is going on.

/gustav
0
 

Author Comment

by:pdvsa
ID: 41783020
Gustav, not sure what happened but I tried it again and it worked!

thank you for the help.  I sincerely appreciate your assistance.
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 41783025
Great! You are welcome.

/gustav
0

Featured Post

Free Trending Threat Insights Every Day

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.

Join & Write a Comment

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

743 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

11 Experts available now in Live!

Get 1:1 Help Now