is it possible to count bools separately for comparison?

[ajk825]

Hello I'm new to access so please be gentle... I have an access database that stores recipes. 2 of my table are RECIPES and FEED_BK with the primary relationship being(RECIPE.RecipeID FEED_BK.RecipeID_FK)... in FEED_BK i have a column "IsDwnlded" which is a bool field using a checkbox. If a user chooses to download a recipe "IsDwnlded" is checked True, if not by default it is False. I need to create a query that would go thru my FEED_BK table, count how many times each one has been checked TRUE and return RECIPE.RecipeName...Is this possible?
Sorry if this is confusing.

[Bob Fitz]

I know you said a query but you could get the result using the DCount() function. Something like:
DCount("IsDwnlded","FEED_BK","IsDwnlded = True")
and
DCount("IsDwnlded","FEED_BK","IsDwnlded = False")

[June7]

FEED_BK has multiple records associated with each recipe? If recipe is not 'downloaded' how else is it obtained by user?

Maybe build a query that joins the two tables (just to pull in the RecipeName field). Then switch to Totals query and group by the RecipeName with aggregate expression:

Sum(Abs([IsDownlded])) AS CountTrue

[ajk825]

They could simply look at it online...by downloading it they would be adding it to their own personal libraries at home, work, wherever...

[ajk825]

Hi Bob,
I tried the DCOUNT and it is returning the total number of records where "IsDwnlded = True" .. Is there any way Access can compare how many times each FEED_BK.RecipeID_FK has had FEED_BK.IsDwnlded checked? So that if say... FEED_BK.RecipeID_FK "6" has FEED_BK.IsDwnlded = "True" 21 times, and FEED_BK.RecipeID_FK "21" has FEED_BK.IsDwnlded = "True" 3 times it will return FEED_BK.RecipeID = "6"?
I hope I'm making this clear...I apologize if not..

[June7]

Did you try my suggestion in post 3?

[ajk825]

Hi June7, sorry... I don't understand what you mean by "switch to Totals query and group by the RecipeName with aggregate expression:
Sum(Abs([IsDownlded])) AS CountTrue", so I've been looking into it. Still really new with Access and SQL...
Here's what I have so far:
SELECT
RECIPE.RecipeName
FROM
RECIPE INNER JOIN FEED_BK
ON RECIPE.RecipeID = FEED_BK.RecipeID_FK
WHERE FEED_BK.IsDwnlded = True;

[June7]

With query in design view, click Totals on the Design tab.

Create CountTrue (or whatever you want to call it) field with expression on the field row of grid.

The resulting sql should be like:

SELECT
RECIPE.RecipeName, Sum(Abs([IsDownlded])) AS CountTrue
FROM
RECIPE INNER JOIN FEED_BK
ON RECIPE.RecipeID = FEED_BK.RecipeID_FK
GROUP BY RecipeName;

Or open in SQL View and copy/paste the above in place of your sql then switch to Design View.

Access Help has more guidelines on using the query builder.

[ajk825]

Hi June Thanks for the help I appreciate it, I did the above and was asked for a value to input for a parameter, but this isn't what I am trying to do. I want to hit the execute button and it to return the RecipeName with the most IsDwnlded = True checks. I also tried adding IsDwnlded = "True" into it in various spots and kept being asked for an input value.

[June7]

Did you copy/paste the sql as suggested?

What parameter is requested?

Filter on IsDownlded field should not be necessary.

If you want record(s) with highest count then add the TOP N qualifier to the query. With query in design view, click in the tables area, look at the Property Sheet for the Top Values property. Enter 1.

[ajk825]

Hi June7 yes I did exactly as above. It is asking for a value for IsDwnlded when i hit execute... if I type in True i get:
This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables. (Error 3071)
Maybe using a True/False value to mark whether or not it was downloaded was not a good idea but I didn't know how else to keep track.

[June7]

I tested this expression with my data and it works. Looks like I may have misspelled IsDwnlded as IsDownlded. Use whatever is correct.

[ajk825]

Hi June7..I didn't even catch that. I tried it again with the correction and you did it. Thank you so very much.