472,782 Members | 1,112 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,782 software developers and data experts.

How to get the total sum of a table column in an Acces DB into and excel sheet?

I need to make it so that a DB of a membership list I have can send the sum value of a column to a cell in a Excel spreadsheet.

Basically:

I have in Access a membership database which, among other things, includes a list of dues paid per month by each member.

In Excel I have the financial statements for said group. And it would really be fantastically helpful if I can make it so that the cell in excel that holds the, let's call it: "Dues paid in January" entry for the Financial statement gets the total sum of the column "January" in the table "Membership Dues" table.

For this to work I need it to only provide the number in that one cell as the value of that cell gets used to calculate the total dues income.


I've tried using the built in query system but I can't get to only show me the value in that single cell.

Is what I'm trying to do even possible??

If so then please help me
Dec 2 '06 #1
5 3484
DWolff
16
I need to make it so that a DB of a membership list I have can send the sum value of a column to a cell in a Excel spreadsheet.

I've tried using the built in query system but I can't get to only show me the value in that single cell.

Is what I'm trying to do even possible??
I'm not sure if your problem is with the query to bring back a single sum, or if it is that you cannot bring back JUST the value without all the extraneous information (i.e. month value). It sounds to me like you've successfully queried the Access database from within Excel but can't return the single value. If this is the case, here's how I do it..

Set up a new sheet in your Excel workbook to be used only for returning the summary values. Then, write the query to sum all dues by year & month. This, then, builds a nice little lookup table of all dues collected each month that you can refresh each month and use throughout the rest of the workbook.

In the cell where you want the sum of dues for that month, use the vlookup function against the table we just built, using the month and year for that particular cell.
Dec 2 '06 #2
wow - that actually works well enough for me.

Just one question - sometimes after i hit the refresh data option in the query field I've created (just created one query with all columns and then used SUM to create a value field) the values get replaced by ### - but the actual values are correct in the field in the actual statement that uses the value field.

Should I worry about this or jsut carry on with it this way.

Thanks again for all the help so far.
Dec 2 '06 #3
NeoPa
32,534 Expert Mod 16PB
Cells that display as '#####' are simply non-text cells with some sort of formatting applied (Date, Currency etc) whose displayed value is too wide for the column. If you make the column wider you'll see the value properly. What is displayed, as you've already found out, does not affect the actual contents.
Dec 3 '06 #4
ok - well - dang - color me slighly more that usually embarrassed. that simple huh?


Oh well - many a thanks for the help.
Dec 3 '06 #5
NeoPa
32,534 Expert Mod 16PB
No problem.
It's gratifying too, to see another member who's received help, giving some back. Nice one DWolf.
Dec 3 '06 #6

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: Ronny Sigo | last post by:
Hello all, I am trying to automate Excel via vba(access). If I try to write values in the second sheet (InkNieuweleden)of the workbook the program puts these values always in the first sheet...
3
by: Mads Petersen | last post by:
I'm stuck in this code. Hope you can and will help me. I launch it from excel. I have made the following code work, but not as i whant. I need the ranges to be working with something like xlDown....
2
by: news reader | last post by:
Hi, Does anoone of you know if there is already a simple application doing something like this. I would enhance / tune the missing features, but would like to avoid to start from scratch or...
9
by: dba123 | last post by:
I need some help and direction on what classes and an example or two (article) on how to read an Excel Worksheet and insert one column into a database table column. I am using .NET 2.0 only. What...
3
by: imtmub | last post by:
Hi, I am trying to link sql table field and excel sheet column. SELECT wko_itemid, imc_qty, ima_price FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel...
0
by: Taxman | last post by:
Windows XP, MS Office Excel 2003 If the tasks, Iím trying accomplish have been addressed previously (separately or in combination). Please, provide the links or keyword search to find them. Iíve...
1
by: TG | last post by:
Hi! I have an application in which I have some checkboxes and depending which ones are checked those columns will show in the datagridview from sql server or no. After that I have 2 buttons:...
7
by: TG | last post by:
hi! I am trying to create a sql server table from an excel sheet. Here is the code I have: 'This procedure the xlsx file and dumps it to a table in SQL Server
0
by: Kelii | last post by:
Yeah, I get it; I do something similar each week at work. This is what I've done. 1. Create a simple query that shows all of your fields in your access table, name that query something like:...
0
by: erikbower65 | last post by:
Using CodiumAI's pr-agent is simple and powerful. Follow these steps: 1. Install CodiumAI CLI: Ensure Node.js is installed, then run 'npm install -g codiumai' in the terminal. 2. Connect to...
0
linyimin
by: linyimin | last post by:
Spring Startup Analyzer generates an interactive Spring application startup report that lets you understand what contributes to the application startup time and helps to optimize it. Support for...
0
by: erikbower65 | last post by:
Here's a concise step-by-step guide for manually installing IntelliJ IDEA: 1. Download: Visit the official JetBrains website and download the IntelliJ IDEA Community or Ultimate edition based on...
0
by: kcodez | last post by:
As a H5 game development enthusiast, I recently wrote a very interesting little game - Toy Claw ((http://claw.kjeek.com/))。Here I will summarize and share the development experience here, and hope it...
14
DJRhino1175
by: DJRhino1175 | last post by:
When I run this code I get an error, its Run-time error# 424 Object required...This is my first attempt at doing something like this. I test the entire code and it worked until I added this - If...
0
by: Rina0 | last post by:
I am looking for a Python code to find the longest common subsequence of two strings. I found this blog post that describes the length of longest common subsequence problem and provides a solution in...
5
by: DJRhino | last post by:
Private Sub CboDrawingID_BeforeUpdate(Cancel As Integer) If = 310029923 Or 310030138 Or 310030152 Or 310030346 Or 310030348 Or _ 310030356 Or 310030359 Or 310030362 Or...
0
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
0
by: Mushico | last post by:
How to calculate date of retirement from date of birth

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.