Count multiple fields query when checkbox yes and another criteria

[saledo2000]

Hi all
I have on table Food_List where I have three columns set to checkbox as value Yes/No and fourth column for Sex (Gender). I wrote a query to count for every ticked checkbox as first criteria and sex 'Male' but result is always 0. Could you please help with this query, it is important to me. I am new in access and would appreciate your help.
Thank you

 

[plog]

...WHERE (((Food_List.Breakfast)=Yes) AND ((Food_List.Sex)="Male")) and (((Food_List.Lunch)=Yes)) and (((Food_List.Dinner)=Yes))

Your criteria only returns rows where all of those are simultaneously true. Therefore, you have no records where every meal is True and Sex="Male".

Perhaps you can post sample data from your table and then post what results you expect the query to produce.

 

[saledo2000]

Thank you for your quick reply. I want to have sum of ticked check boxes for three meals for males at the end of every day. This will help me to track IDs who took a meal. On that basis I will create for Females as well. Attached is screenshot of my continuous form and Food_List table. I have changed count function to Sum(ABS(FieldName)) and getting results only when Dinner has records but that records are replicating to Breakfast and Lunch.
Thanks

 

[plog]

I want to have sum of ticked check boxes for three meals for males at the end of every day.

To get this, remove the meal criteria from the WHERE clause and instead use an IIF expression in the SELECT for each meal like this:

SELECT SUM(iif(Breakfast, 1, 0) AS BreakfastCount
FROM Food_List
WHERE Sex="Male"

You can use BreakfastCount as a model to add the other 2 meals. If you want to know female amounts as well I would remove the WHERE and add a GROUP BY:

SELECT Sex, SUM(iif(Breakfast, 1, 0) AS BreakfastCount
FROM Food_List
GROUP BY Sex

This will help me to track IDs who took a meal.

I don't think it will. It will just give a sum of every meal, it won't let you know who took each meal.

 

[saledo2000]

HI
Yes you are right, I want sum of every gender for each day per meal. Thank you will try tomorrow and will give some feedback.

 

[saledo2000]

To get this, remove the meal criteria from the WHERE clause and instead use an IIF expression in the SELECT for each meal like this:

SELECT SUM(iif(Breakfast, 1, 0) AS BreakfastCount
FROM Food_List
WHERE Sex="Male"

You can use BreakfastCount as a model to add the other 2 meals. If you want to know female amounts as well I would remove the WHERE and add a GROUP BY:

SELECT Sex, SUM(iif(Breakfast, 1, 0) AS BreakfastCount
FROM Food_List
GROUP BY Sex

I don't think it will. It will just give a sum of every meal, it won't let you know who took each meal.

 

[saledo2000]

Hi Plog
It is wonderful. Thank you very much. Query is working exactly what I want.
I have one more question. How can I have sum of each column from this query. For example:
Sex Breakfast Lunch Dinner
Male: 57 61 60
Female: 42 45 44
Sum: ? ? ?

Is it possible to add in the same query or I need to create another.

Thanks

 

[The_Doc_Man]

Look at CrossTab queries, which should give you what you want.

 

[plog]

A query isn't really suited for producing 2 diferent levels of data (by gender and total). To get this all together I suggest you use a report based on the query you have. Each gender will have its own totals section and then at the bottom you can have a totals area which sums everything.

 

[saledo2000]

Hi Plog,
Yes I did it like you suggested. Thank you very very much.

 

[Galaxiom]

I want sum of every gender for each day per meal.

The table structure is completely wrong. The attachment to post #3 shows all the information about the person as well as the meals they had. This suggests you are repeating the information about the person each day. And where is the date in those records?

Information about the person belongs in one table. Information about the meals in another. The PersonMeals table should have the PersonID, MealDate and possibly the Boolean for the meals.

However I would not have the meals in separate fields of the PersonMeals table. Instead have a field for MealID (representing Breakfast, Lunch, Dinner) . Don't include the Boolean field at all. The existence of a record in this table indicates if they had the meal. If there is no record for that PersonID and MealID then the obviously didn't have the meal.

The structure is a Many-to-Many relationship. Search this term for more information.

 

[saledo2000]

The table structure is completely wrong. The attachment to post #3 shows all the information about the person as well as the meals they had. This suggests you are repeating the information about the person each day. And where is the date in those records?

Information about the person belongs in one table. Information about the meals in another. The PersonMeals table should have the PersonID, MealDate and possibly the Boolean for the meals.

However I would not have the meals in separate fields of the PersonMeals table. Instead have a field for MealID (representing Breakfast, Lunch, Dinner) . Don't include the Boolean field at all. The existence of a record in this table indicates if they had the meal. If there is no record for that PersonID and MealID then the obviously didn't have the meal.

The structure is a Many-to-Many relationship. Search this term for more information.

Thank you for a detailed explanation. Will arrange tables as you suggested.
Cheers