Solved

Linked Excel Spreadsheet

Posted on 2016-09-01
13
83 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
[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
13 Comments
 

Author Comment

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

QueryDesign
1
 
LVL 51

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 120

Expert Comment

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

criteria   'Daily Draw/ (Repayment)'
0
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
LVL 38

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 51

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
 

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 51

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 51

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 51

Expert Comment

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

/gustav
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
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 …

624 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