Chart a row in a form instead of columns (1 Viewer)

[Techie2k]

Hi.

I'm a bit stuck on creating a chart in Access 2010. Not sure if I've set my table up right or not

Here's my table design

And the input form

My data is laid out like so

When I insert a chart though, I'm lost. It's like it's totally ignoring my data. The data isn't mine, it says east, north and west like a 'default preview chart'. The row source is

SELECT [student_id],Count(*) AS [Count] FROM [tblProgressLevels]   GROUP BY [student_id];

Which means nothing to me :banghead: I've managed to get most of the other stuff figured out, but these charts are confusing me.

Output??

What I want it to do is show how the student is progressing but I've got it all back to front I think. How could I lay my data out so I have a single English column, a single maths and a single science and they still go up in terms like my form shows; they're all in the same table at the moment laid out the same way as I set up the English tracking.

I also just realised I'm trying to chart alphanumeric values :banghead: :banghead: stupid boy... Can Access do a conversion where I make a lookup table so that 1c = 1, 1b = 2, 1a = 3, 2c = 4, 2b = 5 etc. or would a query work? Just chucking out ideas now.

I don't mind using VBA, and can sort of figure things out but I'm not an advanced user so don't be afraid to talk in small steps - I won't be offended!

Many thanks folks, cheers for the assistance.

Chris

 

[JHB]

...
When I insert a chart though, I'm lost. It's like it's totally ignoring my data. The data isn't mine, it says east, north and west like a 'default preview chart'. ..

You've to view the report in "Print Preview" or "Report View" to show your data in the graph.

 

[Techie2k]

Conditional Formatting based on a Lookup Table

Hi all..

I've decided to abandon the line graph for the time being in favour of some coloured boxes. I've created a lookup table for the levels as it occurred to me I can't chart alphanumeric values.

So I've got my data in and displaying. I'd just like to format it like this

Here's a bit of psuedocode to try and illustrate what I'm trying to achieve.

[COLOR="SeaGreen"]# Lookup numerical equivalent of the score in the box[/COLOR]
LevelNumber = DLookup("lopLevelsValue", "tblLevelsofProgressLookup", "[lopLevel] = current progress level")

[COLOR="SeaGreen"]# Work out what colour to shade the box depending on how many levels the student has progressed.[/COLOR]
IF 'LevelNumber' = 3 > 'year 6 score' THEN
cell background = red
ELSEIF 'LevelNumber' = 6 > 'year 6 score' THEN
cell background = yellow
ELSEIF 'LevelNumber' = 9 > 'year 6 score' THEN
cell background = green
ELSEIF 'LevelNumber' = 12 > 'year 6 score' THEN
cell background = blue
END IF

Here's my setup - more or less the same as post one - but with a lookup table and form to display my table.

Basically as the child progresses their boxes colour code changes in the order red for 3 levels, yellow for 6, green when 9 levels have been passed and then blue for more than 12. All the levels are compared to the Year 6 level as this was their level when they started at secondary school and where we start tracking them from and the correct colour applied.

I'm thinking conditional formatting for each of the cells, but I'm not sure how to do that using a lookup table.

How do I get where I want Any help much appreciated

Thanks guys.

T2k