Access chart reports

[Guest]

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

 

[Guest]

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.

 

[Guest]

Thanks so much, I'm clueless to this, I just set this up and had to be able
to choose multiply fields. I'm not sure how to do what you have said, am I
asking too much to ask for a rundown?

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

 

[Guest]

Have you done any searching and reading on normalization? Did you try create
the union query I suggested? If you have difficulty with the union query,
come back with some exact table and field names for significant fields. We
also would need to know the primary keys.

Ideally you would have a student table with a structure like:

tblStudents
==============
StudentID primary key
StudentFirstName
StudentLastName
....

You would have a table of behaviours

tblBehaviours
==============
BehaviourID autonumber primary key
BehaviourTitle ADHD, Autism,....

and then a junction table that has one record per student per behaviour:

tblStudentBehaviours
===============
StudentBehaviourID autonumber primary key
StudentID links to tblStudents.StudentID
BehaviourID links to tblBehaviours.BehaviourID
--
Duane Hookom
Microsoft Access MVP

Thanks so much, I'm clueless to this, I just set this up and had to be able
to choose multiply fields. I'm not sure how to do what you have said, am I
asking too much to ask for a rundown?

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

 

[Guest]

I have setup only 1 table, it's called Student table, in that table I have
all the fields, including the fields that I use for the check boxes, so I
have check boxes called AggressiveBehaviourPresent, PropertyDamagePresent etc
etc, there is about 10 of these, I go into the form and check off if a child
has any of those behaviours, now I want to run a report with a graph to show
the amount of kids that cause property damage, have aggression behaviour etc.

The second pie chart I want to do is a check box as well, I have a record in
the only table the same as above, tick off ADHD or Autism etc etc, now I want
to do a pie chart with a percentage of the amount of kids with ADHD, autism
etc all in the one chart.

Hope this makes sense, maybe I haven't done this right, I have no idea, I
just made it up, can you tell!

Monique

Have you done any searching and reading on normalization? Did you try create
the union query I suggested? If you have difficulty with the union query,
come back with some exact table and field names for significant fields. We
also would need to know the primary keys.

Ideally you would have a student table with a structure like:

tblStudents
==============
StudentID primary key
StudentFirstName
StudentLastName
...

You would have a table of behaviours

tblBehaviours
==============
BehaviourID autonumber primary key
BehaviourTitle ADHD, Autism,....

and then a junction table that has one record per student per behaviour:

tblStudentBehaviours
===============
StudentBehaviourID autonumber primary key
StudentID links to tblStudents.StudentID
BehaviourID links to tblBehaviours.BehaviourID
--
Duane Hookom
Microsoft Access MVP

Thanks so much, I'm clueless to this, I just set this up and had to be able
to choose multiply fields. I'm not sure how to do what you have said, am I
asking too much to ask for a rundown?

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

Thanks so much, I've ran that query and am now using a pivot chart to plot
the data, it's looking great! Do you know on a pivot chart how I hide the
"Drop filter fields here" and "Drop series fields here" headings, it shows up
on the print out, it also shows the "Behaviour" drop down and I don't want
them to show.

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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

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

 

[Guest]

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

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

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