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

x
?
Solved

Linked Excel Spreadsheet

Posted on 2016-09-01
13
Medium Priority
?
93 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 52

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
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!

 
LVL 40

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 52

Accepted Solution

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

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 52

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 52

Expert Comment

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

/gustav
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

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.
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
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…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

782 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