M
Monique
Thank you, ok I will just not put in that it's = to TRUE etc.
The date/time modified doesn't have anything to do with the charts your
right, I'm just trying to work out another thing I have to do which is show
if/when a form is modified and thought you might be able to help.
Monique
The date/time modified doesn't have anything to do with the charts your
right, I'm just trying to work out another thing I have to do which is show
if/when a form is modified and thought you might be able to help.
Monique
Duane Hookom said:I had the basic idea for the SQL a couple posts back
SELECT UniqueID, "PTSD" As Behaviour, [PTSDPresent] As Present
FROM Students
UNION ALL
SELECT UniqueID, "AggressionVerbal", [AggressionVerbalPresent]
FROM Students
... etc ...
I'm not sure what your date field has to do with the charts. The charts are
generated from aggregated data where the Behaviour and Present are the values
required.
--
Duane Hookom
Microsoft Access MVP
Monique said:Right I will try that then, where do I say in the union query true or false?
This is how part of mine is:
SELECT UniqueID, "PTSD" As Behaviour
FROM Students
WHERE [PTSDPresent] = TRUE
UNION ALL
Where do I put the false part in?
I want to put on my form a field for date/time modification so if someone
updates a record then we know when it was last modified and can put that on
the report. I've added 2 fields to the main table called DateModified and
TimeModified, then added those fields onto my form that we use to input the
data. Now what I need to find out is how the date and time is added to those
fields if one of the students information is updated?
Monique
Duane Hookom said:Did you change your union query so that you have both yes and no values for
each behaviour for each student? This would give you 2 pie pieces for each
behaviour.
I don't know what you are refferring to regarding the date/time.
--
Duane Hookom
Microsoft Access MVP
:
All the figures are coming out fine but I just need to get the percentages
going so I can graph percentage of kids showing violent tendency's etc etc,
what do you think?
Also, I've followed the instructions for adding the date/time modified but
it hasn't worked, do you know how I can do that?
Monique
:
My union query would look more like
SELECT UniqueID, [AggressionVerbalPresent] as IsPresent,
"AggressionVerbalPresent" As Behaviour
FROM Students
UNION ALL
SELECT UniqueID, [AggressionPhysicalPresent], "AggressionPhysicalPresent"
FROM Students
--etc--
;
This would result in both yes and no values which could be graphed more
easily.
--
Duane Hookom
Microsoft Access MVP
:
Duane
OK, I'll try and do that now, I just fiddled with it a little but it didn't
work, I'll try doing it the way you said. Do you know, I have this data now
on a bar graph but would also like to show percentages, for instance, there
are 15 or so headings, 57 children at the school but what I need to know is
the percentage of children that show aggression - physical for example, so my
graph shows there are 39 out of a possible 57 with aggression-physical
behaviours so is there a way to now show that in percentage. So if 39 out of
57 display the behaviours then 68% of the students display this, how can I do
this in a graph/pie chart etc?
Monique
:
I would use the union query to create a chart on a form or report. Then allow
the user to open the form or report from a command button.
--
Duane Hookom
Microsoft Access MVP
:
Duane
I have finished the query now, thanks so much, except I still need to know
how to hide those headings, the problem is I need to put the query on the
switchboard so the principal/counsellor can just click on that graph whenever
they want it but I can't choose open a query from the add new switchboard
function, any way around this?
Monique
:
SELECT UniqueID, "AggressionVerbalPresent" As Behaviour
FROM Students
WHERE [AggressionVerbalPresent] = TRUE
UNION ALL
SELECT UniqueID, "AggressionPhysicalPresent"
FROM Students
WHERE [AggressionPhysicalPresent] = TRUE
add more UNION ALL through =TRUE here
;
You can use your union query as the row source of a crosstab. I'm not sure
what you expect your graph data to look like.
--
Duane Hookom
Microsoft Access MVP
:
I have only gone so far as to do this much:
SELECT UniqueID, "AggressionVerbalPresent" As Behaviour
FROM Students
WHERE [AggressionVerbalPresent] = TRUE
UNION ALL
SELECT UniqueID, "AggressionPhysicalPresent"
FROM Students
WHERE [AggressionPhysicalPresent] = TRUE;
Then it shows this:
UniqueID Behaviour
1 AggressionVerbalPresent
2 AggressionVerbalPresent
4 AggressionVerbalPresent
6 AggressionVerbalPresent
7 AggressionVerbalPresent
8 AggressionVerbalPresent
9 AggressionVerbalPresent
10 AggressionVerbalPresent
etc it goes all the way down to 57 and then it starts from 1 again and lists
57 AggressionPhysicalPresent behaviours. I tried then creating a pivotal
chart in Access and then going over to Excel which is not what I really want
to do but it then showed up in excel with the right amounts in a chart which
is what I want.
Now I just need to add all the rest of the behaviours to the union query but
for some reason it's giving me an error, I'm not sure how to add more, do I
add it after the stuff I have above? Say I want to add
propertydamagepresent, which section of the query would I add that? Is there
a way then to do a chart in MS Access instead of having to go over to Excel?
Monique
That's working OK but I need to add all the rest of the behaviours
:
Please provide your final sql of your union query.
--
Duane Hookom
Microsoft Access MVP
:
Thanks for that, the query actually worked, once I twigged a few things, I
had to just call the table "students" as it didn't like tblstudents like you
had, then the primary key is actually UniqueID instead of StudentID as we are
only using students initals so we didn't want that to be the unique id, so I
created one.
Anyway the query ran, I only have 57 students in the db, all it showed was
the unique id and the behaviour which was "AggressionPhysicalPresent" but it
showed that for all of them, so I had 57 of each for every student and I know
that they don't all have that check box ticked, I thought the = TRUE would
get rid of any that didn't have that checkbox ticked?
Monique
:
You are close with your union query. I'm not sure if your table name is
tblStudents or "that table is just called students"...
This sql should be a good start if you have a Primary Key field StudentId
and your table name is tblStudents:
SELECT StudentID, "AggressionVerbalPresent" As Behaviour
FROM tblStudents
WHERE [AggressionVerbalPresent] = TRUE
UNION ALL
SELECT StudentID, "AggressionPhysicalPresent"
FROM tblStudents
WHERE [AggressionPhysicalPresent] = TRUE;
--
Duane Hookom
Microsoft Access MVP
:
I just tried to do a union query, I've never done them before, this is how I
did it:
SELECT StudentID, "AggressionVerbalPresent"
FROM tblStudents
WHERE [AggressionVerbalPresent] = TRUE
UNION ALL
SELECT StudentID, "AggressionPhysicalPresent"
FROM tblStudents
WHERE [AggressionPhysicalPresent] = TRUE
UNION ALL
But it didn't work, it came back with an error message "Syntex error in
query. Incomplete clause."
I'm not sure what I'm doing wrong, I have the record names exactly how they
are in the table, that table is just called students so I've done that. The
thing is when I say = TRUE, I think when a tick is used for a check box it
sees it as being -1 for some reason, I tried using both = TRUE and = -1 but
that didn't work either.
Monique
:
Your table structure sounds like a spreadsheet rather than a relational
database. Either normalize your structure or use a union query like
SELECT StudentID, "ADHD" as Behaviour
FROM tblStudentSpreadsheet
WHERE [ADHD] = True
UNION ALL
SELECT StudentID, "Autism"
FROM tblStudentSpreadsheet
WHERE [Autism] = True
UNION ALL
... etc ...
You can then create a graph based on the union query.
--
Duane Hookom
Microsoft Access MVP
:
I'm putting together a student database and to do some charts, I want to do a
pie chart with a break up of different behaviours by percentage, in the
actual form I've got check boxes against each student and have checked off
the behaviours displayed so I'm wondering how I can say 50% for example show
violence, 20% show impulsive behaviours etc etc when I don't have numbers as
such to draw from I only have check boxes against each child? Any ideas how
to do this.
I also want to do a graph as well with number of children with ADHD, number
with Autism etc etc, any help would really be appreciated.
Thanks