|
Patch reliability is unclear. Unless you have an immediate, pressing need to install a specific patch, don't do it. |
I have a table which contains fields ItemID and W1,W2,W3,W4….upto W53 – these fields are used to store values for week 1 to week 53 during a year.
I need to produce a graph which displays w1 to w53 as the series.
How can I rearrange the data so that i get a query which holds the data as:
itemid w1
itemid w2
itemid w3
etc
to allow me to then use this query data as the chart series……or is there another way around this?
John
The simpler approach would be to modify your design and set up and populate your your table as follows:
ItemID, Week Number, Value
Then all you would need to do in the query is group by Item and Week Number
With your current design, what happens when the weeks span multiple years? The setup you have is wasting a lot of table space. You may also run into query problems if you have many null values in data that is not populated.
If you really do not want to change your table design, you could in code loop through the weeks to extract data by week and use an append query to append the data to a new table in the format needed. (Too much work.)
Just my 
The simpler approach would be to modify your design and set up and populate your your table as follows:
ItemID, Week Number, Value
Then all you would need to do in the query is group by Item and Week Number
With your current design, what happens when the weeks span multiple years? The setup you have is wasting a lot of table space. You may also run into query problems if you have many null values in data that is not populated.
If you really do not want to change your table design, you could in code loop through the weeks to extract data by week and use an append query to append the data to a new table in the format needed. (Too much work.)
Just my
Donations from Plus members keep this site going. You can identify the people who support AskWoody by the Plus badge on their avatars.
AskWoody Plus members not only get access to all of the contents of this site -- including Susan Bradley's frequently updated Patch Watch listing -- they also receive weekly AskWoody Plus Newsletters (formerly Windows Secrets Newsletter) and AskWoody Plus Alerts, emails when there are important breaking developments.
Notifications
