Count multiple fields query when checkbox yes and another criteria (1 Viewer)

saledo2000

Registered User.
Local time
Today, 09:13
Joined
Jan 21, 2013
Messages
94
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
 

Attachments

  • Count query.txt
    346 bytes · Views: 132

plog

Banishment Pending
Local time
Today, 04:13
Joined
May 11, 2011
Messages
11,611
Code:
...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

Registered User.
Local time
Today, 09:13
Joined
Jan 21, 2013
Messages
94
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
 

Attachments

  • FL table and continuous form.png
    FL table and continuous form.png
    15.3 KB · Views: 166
Last edited:

plog

Banishment Pending
Local time
Today, 04:13
Joined
May 11, 2011
Messages
11,611
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:

Code:
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:

Code:
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

Registered User.
Local time
Today, 09:13
Joined
Jan 21, 2013
Messages
94
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

Registered User.
Local time
Today, 09:13
Joined
Jan 21, 2013
Messages
94
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:

Code:
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:

Code:
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

Registered User.
Local time
Today, 09:13
Joined
Jan 21, 2013
Messages
94
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
 

Attachments

  • FL_Query result.png
    FL_Query result.png
    2.9 KB · Views: 107
Last edited:

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 04:13
Joined
Feb 28, 2001
Messages
26,996
Look at CrossTab queries, which should give you what you want.
 

plog

Banishment Pending
Local time
Today, 04:13
Joined
May 11, 2011
Messages
11,611
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

Registered User.
Local time
Today, 09:13
Joined
Jan 21, 2013
Messages
94
Hi Plog,
Yes I did it like you suggested. Thank you very very much.
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 20:13
Joined
Jan 20, 2009
Messages
12,849
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

Registered User.
Local time
Today, 09:13
Joined
Jan 21, 2013
Messages
94
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
 

Users who are viewing this thread

Top Bottom