Condensing and grouping results from a database in Access or Excel

Question

What I have to do is take the results from a database, and condense them all into one line per date. Right now, each date has numerous amounts of rows, and the data I need is spread throughout each of the rows in different columns.

Each column has the exact data value I need, and the rest of the values in that column have 0's for the other rows, until the next date.

Is there a simple way to do this? Or a difficult way? The reporting software we use (MvQuery) generates the report correctly, however can not group by period and sum that, which we need.

I'm trying to figure out a simple way to show you a mock up of the data so you can have a better idea of what I am asking, but the idea eludes me.

Answer 1

Try an Excel Pivot Table. This will allow you to condense the data (SUM, AVG, MIN, MAX, etc) and group by date. You can base the pivot table directly on the database data using ODBC or you can paste data into a worksheet and base the pivot tabe on that.

Answer 2

You may also do this in Access, using a Group By query. I don't know what kind of database supplies your data, but you may be able to link your source table to Access either directly (e.g. from another Access DB) or through a ODBC connection. Here is an example of a query summing up columns 1-3 individually, for each period. Or you may sum up all three in a single column.