Count Totals etc. (1 Viewer)

Ilovexfiles · Jul 26, 2017

Hello
I would like to make a report that lists totals...such as:
Total X records: ###
Total Criteria Y records: ###

I have been unsuccessfully trying this via DCount control source in an unbound text box.

Are there other more efficient ways to achieve this?

pbaldy · Jul 26, 2017

You can use a subreport based on a totals query, or this type of thing in the report footer:

=Sum(IIf(Criteria = "X", 1, 0))

isladogs · Jul 26, 2017

I have been unsuccessfully trying this via DCount control source in an unbound text box.

So what happens when you try this?
What conditions are you applying to your DCount formulas?

Ilovexfiles · Jul 27, 2017

ridders said:
So what happens when you try this?
What conditions are you applying to your DCount formulas?

I have been trying to do the following as the control source of my textbox:
=DCount([ScreeningType],[tblScreening],1)

Screening Type is a field/column in the table "tblScreening". I want to know how many in that column equal 1 (which is the Unique Id for a Drop Down List). I have also tried using the actual name of the drop down, but both come back with errors.

Ilovexfiles · Jul 27, 2017

pbaldy said:
You can use a subreport based on a totals query, or this type of thing in the report footer:

=Sum(IIf(Criteria = "X", 1, 0))

The report I have will not be listing any records at all. So I don't think I can do a footer correct.

I have been able to get individual results in a query by grouping, but it will not let me do multiple groupings (like a pivot table), so I have to do each individually it seems.

pbaldy · Jul 27, 2017

Incorrect syntax:

http://www.theaccessweb.com/general/gen0018.htm

But DCount() probably wouldn't be the tool I used. It's an extra trip to the data that isn't necessary since the report already contains it (and presumably there are several). And if appropriate, the subreport based on a totals query would be more dynamic.

isladogs · Jul 27, 2017

Ilovexfiles said:
I have been trying to do the following as the control source of my textbox:
=DCount([ScreeningType],[tblScreening],1)

Screening Type is a field/column in the table "tblScreening". I want to know how many in that column equal 1 (which is the Unique Id for a Drop Down List). I have also tried using the actual name of the drop down, but both come back with errors.

The DCount formula is incorrect. Try this:

Code:

=DCount("*","tblScreening","ScreeningType=1")

But also look into the approach suggested by pbaldy which works well
In your case, something like:

Code:

=Sum(IIf(ScreeningType=1, 1, 0))

EDIT: as Paul & I are tripping over each other here, I'll drop out. Good luck!

pbaldy · Jul 27, 2017

Ilovexfiles said:
The report I have will not be listing any records at all. So I don't think I can do a footer correct.

I have been able to get individual results in a query by grouping, but it will not let me do multiple groupings (like a pivot table), so I have to do each individually it seems.

Correct on the footer if the report is unbound. My first choice would be the totals query then, so maybe an example of the data and the expected result would help us sort that out (or rule it out).

Ilovexfiles · Aug 8, 2017

thank everyone I kept it simple by correcting D-count formula. will have to do the rest in excel

thanks

Count Totals etc. (1 Viewer)

Ilovexfiles

Registered User.

pbaldy

Wino Moderator

isladogs

MVP / VIP

Ilovexfiles

Registered User.

Ilovexfiles

Registered User.

pbaldy

Wino Moderator

isladogs

MVP / VIP

pbaldy

Wino Moderator

Ilovexfiles

Registered User.

Similar threads

Users who are viewing this thread