Solved

Linked Excel Spreadsheet

Posted on 2016-09-01
13
58 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
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…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

912 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

16 Experts available now in Live!

Get 1:1 Help Now