Graphs, a sore head and thirty three thousand small furry animals! 1

[Blackshark]

Hi all,

Graphs - Reports. Specifically sorting out the axis and grouping settings.

I am in the middle of designing a Quality Assurance database. For any that dont know, quality assurance consists of guys going out and checking workmanship, filling in a survey form (tick boxes) and returning the said form to be read in though a scanner, in to my fabulous database (cough cough!).

Now, I have duplicated all the reports that the last system produced each month (old system used 45 Lotus 123 spreadsheets, WOWZERS!). But the graphs have stumped me.


I have produced a query that lists average defects per assesment for each group of engineers. This produces a list of the Groups and a % error rate for 18 different sections on the completed survey forms. I would like to represent this data over time, ie. For each group, a graph showing Jan to Dec how high the error rate was for each of the key areas. BTW There is a date field in the table and the wuery includes pulling out the Month. So to end up with 18 graphs over 3 pages.


The issue I have is with getting it to list Jan to Dec even though only 3 (or know 4) months worth of data is available. Is there a way to do this? It is causing my head some serious pain (due to banging of the aforementioned body part against a formica table top!).


Thanks for any and all help

Tim

PS The 33,000 fury animals comes from watching the two Tribbles episodes 1400 times while trying to figure out the above!!

PPS I had thought about exporting the data in to Excel, but I need the graph to be available in the Access Report. This si so that I can bring together between 10 and 20 seperate reports for different levels of management in to one single report and send this out via Snapshot.

 

[Blackshark]

Doh! OK, so I needed to use a crosstab query. OK, so I now have my graph looking great, with the right fields being used and the Jan to Dec formatted correctly.

HOWEVER

I would now like to have the columns of my crosstab named with the Group name rather than the Groups reference (ie Scotland rather than 1, North East rather than 2 etc..). Only issue here is I can not seem to get the columns to arrange my a second field while only showing the name field.

Any thoughts?

Thanks Tim

 

[MichaelRed]

Look into having a seperate table of the REQUIRED groups with the ID (e.g. 1. 2. 3, ...) and their names (e.g. Scotland, Ireland, Spain, France, ...). Use this as the Left outter join in the crosstab, linking the Ids. Use the names in the col transform.

You can also use the column headings property of hte crosstab query - but it isnt obvious and you DO need to rember to modify the property each time the Loc changes.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[Blackshark]

Michael,

Thanks, I will go with the second suggestion as I am looking to have 100% flexible report production built into the DB where, through seperate tables, the system builds up the reports for each layer of management etc.. It would seem sensible to go for a coded solution.

Thanks Tim