Trouble creating this graph

[tlozoot]

I have a form for 'Monthly Reviews' of patients wellbeings. The field names are such things as 'Personal Care', ' Diet', and 'Alcohol Management'. Users of the system will use the form to score patients on each area, from 1 to 9. Currently, this is done using a lookup, so the user can choose the value 1-9 from a drop-down menu, and then add any relevant comments in another field.

Once a user has filled out the form and scored the client on each (11 in total) area, I want to make a graph of the data, so it can be printed off and put in the patient file. I need the fields on the x axis to be the scoring criteria (Personal care, Diet, Alcohol management, etc), and the Y axis to go measure between 1-9.

How do I do this? The create a chart function in form design seems completely at odds with what I'm trying to achieve, and I think that this is erfectly straightforward to achieve, as a graph.

Thanks in advance!

[June7]

Your data structure is not normalized and therefore complicates. Need to rearrange the data with UNION query:

SELECT 1 AS SeqID, PatientID, "Diet" AS Category, [Diet] AS Score FROM tablename
UNION SELECT 2, PatientID, "Personal Care", [Personal Care] FROM tablename
UNION SELECT 3, PatientID, "Alcohol Management", [Alcohol Management] FROM tablename;

Then use that query as source for graph RowSource which will be like:

TRANSFORM First(Query1.Score) AS FirstOfScore
SELECT Query1.Category
FROM Query1
WHERE (((Query1.PatientID)=[enter patientID]))
GROUP BY Query1.Category
PIVOT Query1.SeqID;

Compare those results with this RowSource:

SELECT Category, Score FROM Query1 WHERE PatientID=[enter patientID];

The PIVOT seems best for a bar graph if you want each bar with different color and the simple SELECT would work for a line or scatter. Both need to be filtered to single patient, which is what you requested.