Query to summarize data for point in time

[cmac]

Hello,

I have a table that stores dollar amounts by months over multiple years -
both past and future. I need to be able to obtain totals through the current
month and then totals looking forward (forecast amounts). Field names in the
table are Jan_07, Feb_07, etc. through Dec_12. Need direction on the query
and other steps to achive this.

Thanks you -

 

[Duane Hookom]

If you actually have field names that are months and years, you should first
consider normalizing your data so the month/year is a value stored in a
field, not its name.

I can't picture your data since your description suggests a single record
with a value for each month. I would expect you would have at least one other
field that stores what the budget amount is associated with.

 

[cmac]

Duane,

There are more fields associated with each record. There are however the
financial fields described for each record or project. This particular table
stores the financial data by month for each project record. So, there is a
project number that has each of the month/yr fields. There is then another
master table that stores the non-financial data with the projet number as the
key. Any thoughts on how to approach?

 

[Duane Hookom]

I stick by the first statement in my first reply. Normalize your table
structure. If you can't change your structure, use a union query to normalize
the existing table.