Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Warwick, Warwickshire, England
    Posts
    189
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Arrange data for chart (2000)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Arrange data for chart (2000)

    You can download a sample database from Roger's Access Library (thanks to <!profile=Support4John>Support4John<!/profile> for this tip) that demonstrates how to normalize and denormalize tables.

    You can also take a look at <post#=229516>post 229516</post#>. It contains a code example for a similar situation.

  3. #3
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Arrange data for chart (2000)

    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 <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15>
    Regards,

    Gary
    (It's been a while!)

  4. #4
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Warwick, Warwickshire, England
    Posts
    189
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Arrange data for chart (2000)

    Thank you both for your input.
    I realised that the table wasn't ideal - an inherited design - but didn't want to have to rearrange the data. Part of the reason for not redesigning was not being able to think of an easy way to automatically rearrange the table.
    Thanks to both of you I now have a method of rearranging and creating the chart.
    Thanks as always

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •