Compound Query Challenge

[wdsnews]

Our school is reporting demographics to the district and I'd like to build a
query that is stumping me.

We have a Yes/No field called "Free Lunch" and another called "Reduced
Lunch". I'd like to add the total count of both lunch fields, separated by
"In District" and "Non-District" residents. The final result would
calculate the totals and look like this:

In District Non-District
X Y

Three fields are involved. The "District of Residence" field is a text
field. The query would evaluate its content for "OC". A content of "OC" is
in district. Anything else is non-District.

The Yes/No fields are "Free Lunch" and "Reduced Lunch". I simply want to
count all the records that are true for either of those two fields,
according to the "District of Residence".

Your suggestions and help are appreciated.

 

[strive4peace]

Hi wdsnews (what is your name?)

firstly, I would suggest a LunchType field since FreeLunch and
ReducedLunch are mutually exclusive

set up a table:

LunchTypes
- LTypID, autonumber
- LunchType, text

with records like this:
1, Free Lunch
2, Reduced Lunch
3, Normal Lunch

in related tables:
- LTypID, number, Field Size --> Long, Default Value --> null,
Description --> Lunch Type (Free, Reduced, Normal)

then, you could use a combobox on the form to collect lunch type

ControlSource --> LTypID
Rowsource -->
SELECT LTypID, LunchType
FROM LunchTypes
ORDER BY LunchType

then, to add Free and Reduced lunches in the district, add the following
calculated field to a query to get a 0 or 1 for each row:

field --> NumDistrict: IIF([District of Residence] = "OC" AND LTypID
<=2, 1, 0)
field --> NumNonDistrict: IIF([District of Residence] <> "OC" AND LTypID
<=2, 1, 0)

on a form (or report), you can make textbox controls in the form (or
report) (group) footer

Name --> TotalDistrict
ControlSource -->
= Sum( IIF([District of Residence] = "OC" AND LTypID <=2, 1, 0))

Name --> TotalNonDistrict
ControlSource -->
= Sum( IIF([District of Residence] <> "OC" AND LTypID <=2, 1, 0))
~~~~~~~~~~~

if you choose not to set up a lunch types table and still need help, I
will answer your question as you asked


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

*
have an awesome day
*