Question about asking several questions in a query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a query that looks at check boxes in my database, it says the following:

WHERE [Aboriginal] = TRUE
UNION ALL
SELECT UniqueID, "Reintegration to High School" As Behaviour
FROM Students
WHERE [OutcomeReintegration] = TRUE
UNION ALL
SELECT UniqueID, "Special School/Class Placement"
FROM Students
WHERE [OutcomeSpecialSchool] = TRUE
UNION ALL
SELECT UniqueID, "Community Education Program"
FROM Students
WHERE [OutcomeCommunityEduction] = TRUE
UNION ALL
SELECT UniqueID, "TAFE/ALESCO"
FROM Students
WHERE [OutcomeTAFE] = TRUE
UNION ALL
SELECT UniqueID, "Work"
FROM Students
WHERE [OutcomeWork] = TRUE
UNION ALL
SELECT UniqueID, "Juvenile Justice"
FROM Students
WHERE [OutcomeJJ] = TRUE
UNION ALL
SELECT UniqueID, "HSLO"
FROM Students
WHERE [OutcomeHSLO] = TRUE
UNION ALL
SELECT UniqueID, "Moved out of Region"
FROM Students
WHERE [OutcomeMoved] = TRUE
UNION ALL
SELECT UniqueID, "Left School/Unemployed"
FROM Students
WHERE [OutcomeLeftSchool] = TRUE
UNION ALL
SELECT UniqueID, "Home Duties (Married or Parenting)"
FROM Students
WHERE [OutcomeHomeDuties] = TRUE
UNION ALL
SELECT UniqueID, "Training Provider"
FROM Students
WHERE [OutcomeTraining] = TRUE
UNION ALL SELECT UniqueID, "Referred to District"
FROM Students
WHERE [OutcomeDistrict] = TRUE;

It works and runs the query however it's not what I'm after, what I want it
to say is, if the check box "Aboriginal" is checked then show me the outcome
but what I've written actually shows me the outcomes as well so I think I
need a query that says if it equals this then show this but I'm not sure how
to do that, if someone could help I would appreciate it.

M
 
First off you have your table as a spreadsheet and not as a relational
database.
You should one table for the student and then another table in a one-to-many
relationalship for information that relates to the student - not a bunch of
Yes/No fields.
 
I realise that, I'm new to MS Access and setup this up a while ago, I've had
help here before and they helped me to write my union queries, so are you
saying based on that I can't actually do what I need to do?

M

KARL DEWEY said:
First off you have your table as a spreadsheet and not as a relational
database.
You should one table for the student and then another table in a one-to-many
relationalship for information that relates to the student - not a bunch of
Yes/No fields.

--
KARL DEWEY
Build a little - Test a little


Monique said:
I have a query that looks at check boxes in my database, it says the following:

WHERE [Aboriginal] = TRUE
UNION ALL
SELECT UniqueID, "Reintegration to High School" As Behaviour
FROM Students
WHERE [OutcomeReintegration] = TRUE
UNION ALL
SELECT UniqueID, "Special School/Class Placement"
FROM Students
WHERE [OutcomeSpecialSchool] = TRUE
UNION ALL
SELECT UniqueID, "Community Education Program"
FROM Students
WHERE [OutcomeCommunityEduction] = TRUE
UNION ALL
SELECT UniqueID, "TAFE/ALESCO"
FROM Students
WHERE [OutcomeTAFE] = TRUE
UNION ALL
SELECT UniqueID, "Work"
FROM Students
WHERE [OutcomeWork] = TRUE
UNION ALL
SELECT UniqueID, "Juvenile Justice"
FROM Students
WHERE [OutcomeJJ] = TRUE
UNION ALL
SELECT UniqueID, "HSLO"
FROM Students
WHERE [OutcomeHSLO] = TRUE
UNION ALL
SELECT UniqueID, "Moved out of Region"
FROM Students
WHERE [OutcomeMoved] = TRUE
UNION ALL
SELECT UniqueID, "Left School/Unemployed"
FROM Students
WHERE [OutcomeLeftSchool] = TRUE
UNION ALL
SELECT UniqueID, "Home Duties (Married or Parenting)"
FROM Students
WHERE [OutcomeHomeDuties] = TRUE
UNION ALL
SELECT UniqueID, "Training Provider"
FROM Students
WHERE [OutcomeTraining] = TRUE
UNION ALL SELECT UniqueID, "Referred to District"
FROM Students
WHERE [OutcomeDistrict] = TRUE;

It works and runs the query however it's not what I'm after, what I want it
to say is, if the check box "Aboriginal" is checked then show me the outcome
but what I've written actually shows me the outcomes as well so I think I
need a query that says if it equals this then show this but I'm not sure how
to do that, if someone could help I would appreciate it.

M
Click to expand...
Click to expand...
 
Monique,
Your explanation of what you want is a bit unclear. "...that says if it
equals this then show this ..."

Can you elaborate on what "it" is and what "this" is?

Perhaps what you need is to revise your union query to show additional
fields and not filter the data at this first stage. For instance

SELECT UniqueID, "Reintegration to High School" As Behaviour,
OutcomeReintegration as Checked
FROM Students

UNION ALL
SELECT UniqueID, "Special School/Class Placement" , OutcomeSpecialSchool as
Checked
FROM Students

That will return three fields
UniqueID,
Behaviour = Description of a behaviour
Checked = Whether or not (true [-1] or false [0]) the behaviour was checked.

Now you can use a query of that query to identify persons that have a
behavior checked or not checked.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Monique said:
I realise that, I'm new to MS Access and setup this up a while ago, I've
had
help here before and they helped me to write my union queries, so are you
saying based on that I can't actually do what I need to do?

M

KARL DEWEY said:
First off you have your table as a spreadsheet and not as a relational
database.
You should one table for the student and then another table in a
one-to-many
relationalship for information that relates to the student - not a bunch
of
Yes/No fields.

--
KARL DEWEY
Build a little - Test a little


Monique said:
I have a query that looks at check boxes in my database, it says the
following:

WHERE [Aboriginal] = TRUE
UNION ALL
SELECT UniqueID, "Reintegration to High School" As Behaviour
FROM Students
WHERE [OutcomeReintegration] = TRUE
UNION ALL
SELECT UniqueID, "Special School/Class Placement"
FROM Students
WHERE [OutcomeSpecialSchool] = TRUE
UNION ALL
SELECT UniqueID, "Community Education Program"
FROM Students
WHERE [OutcomeCommunityEduction] = TRUE
UNION ALL
SELECT UniqueID, "TAFE/ALESCO"
FROM Students
WHERE [OutcomeTAFE] = TRUE
UNION ALL
SELECT UniqueID, "Work"
FROM Students
WHERE [OutcomeWork] = TRUE
UNION ALL
SELECT UniqueID, "Juvenile Justice"
FROM Students
WHERE [OutcomeJJ] = TRUE
UNION ALL
SELECT UniqueID, "HSLO"
FROM Students
WHERE [OutcomeHSLO] = TRUE
UNION ALL
SELECT UniqueID, "Moved out of Region"
FROM Students
WHERE [OutcomeMoved] = TRUE
UNION ALL
SELECT UniqueID, "Left School/Unemployed"
FROM Students
WHERE [OutcomeLeftSchool] = TRUE
UNION ALL
SELECT UniqueID, "Home Duties (Married or Parenting)"
FROM Students
WHERE [OutcomeHomeDuties] = TRUE
UNION ALL
SELECT UniqueID, "Training Provider"
FROM Students
WHERE [OutcomeTraining] = TRUE
UNION ALL SELECT UniqueID, "Referred to District"
FROM Students
WHERE [OutcomeDistrict] = TRUE;

It works and runs the query however it's not what I'm after, what I
want it
to say is, if the check box "Aboriginal" is checked then show me the
outcome
but what I've written actually shows me the outcomes as well so I think
I
need a query that says if it equals this then show this but I'm not
sure how
to do that, if someone could help I would appreciate it.

M
Click to expand...
Click to expand...
Click to expand...
 
Back
Top