advice on setting up tables

[dharkangel]

Hi everyone,

I need some advice on how best to do what I am currently doing. I have some code that generates line graphs off of some data. The tricky part is that I have to present the data to the graph in a certain way. I have one master table that is the source of my data. It contains hundreds of records, some of the fields I use are [Close Date],[Commit Date], [Status].

I run a script that calculates how many records were closed by their [Commit Date], then inserts these values, week by week in a separate table. This table looks like this:

week BarcelonaClosedByCommit BarcelonaTotalClosed, etc
1 5 10
2 2 2
3 5 4
4 1 4
5 3 3
6 2 1

Then my graph code will take these values and figure out weekly, how good people are closing their actions. Is this the best way for me to being doing this? I've learned in the past that I shouldn't store calculated values like this in a table, but I really don't know how to do it any other way. As you can see this gets VERY cumbersome everytime a new site is added (Barcelona, Tokyo, Kyoto, etc). Because I then have to add a new column to the table and modify my code to update this new column in the table above.

 

[randy700]

I don't think I'd bother storing the calculated data. Instead, just base your report/graph on the query itself. As far as adding additional locations goes, can you include the Location field in your query and Group By the location when you create your report?


Randy

 

[dharkangel]

In order to base the report on the query I would have to write a loop in my SQL that would give me the two values you see in my sample (BarcelonaClosedByCommit, BarcelonaTotalClosed) for each week, depending on a specific range of dates. Is it possible to set variables to use in a loop in an MS Access query? I looked into doing this and I couldn't figure out how to set variales in MS Access queries. I know how to do it in SQL Server, but I am stuck using Access for this project.