Query to summarise data in multiple fields

[vanilladan2]

Good morning everyone.

I have a table with 54 fields, as follows:

1) Product Code
2) Description
3 – 54) Weeks of the year

This table holds forecast data.

What I want to do is summarise the Forecast data in 4 week blocks, e.g.

Weeks 1 – 4
Weeks 5 – 8
Weeks 9 – 12
etc.


Now, the straightforward way to do this would be to build a query and add in 13 new fields – one for each of the 4 week blocks.

However, that’s fairly time consuming. Is there a better way around this?

Thanks,

Vanilladan

 

[Joe4]

Welcome to the Board!

Actually, the main problem is that your table is not normalized. The first rule of normalization states that your table should not contain repeating fields. You have 52 fields for week values. You can see the conundrum this causes (as you have dicsovered with your problem).

If you can re-design your table as a four field table as follows, it will allow you to do what you want fairly easily:

1) Product Code
2) Description
3) Week number
4) Value

This you can use criteria on the Week Number field to get your blocks.

 

[vanilladan2]

Thanks jmiskey,

Funny, that's the way the data was originally laid out. I then used a Crosstab query to get what I wanted.

The reasons I originally looked to make a change was:

a) the feeling that I was perhaps over-looking an alternative design;
b) the row/column format in which Forecast data is submitted to me (in an Excel document).

However, the Excel data is easy to transpose, and now that you've satisfied an alternative design isn't really feasbile... I'll revert to the original.

Thanks for your help.
cheers,

Dan