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

[Daedalus]

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

[DWolff]

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.

[Daedalus]

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.

[NeoPa]

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.

[Daedalus]

ok - well - dang - color me slighly more that usually embarrassed. that simple huh?


Oh well - many a thanks for the help.

[NeoPa]

No problem.
It's gratifying too, to see another member who's received help, giving some back. Nice one DWolf.