Re: [MSAccessProfessionals] Count records on a report

Hi Duane,

Thanks for the info below.

I do see a more realistic total at the end of the report

This certainly solves my problems.

Thanks again.

On Monday, July 13, 2020, 05:42:11 PM GMT-4, Duane Hookom <duanehookom@hotmail.com> wrote:

You would add a group header for the idnum and add a text box to the group header:

Name: txtAge55

Control Source: =Abs([Age]>=55)

Running Sum: Over All

Then add a text box to the Report Footer section:

Control Source: =[txtAge55]

The txtAge55 doesn't have to be visible and I expect you would rather not see it once you have finished testing.

Regards,

Duane

---

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> on behalf of M Garcia via groups.io <toukey1=yahoo.com@groups.io>
Sent: Monday, July 13, 2020 3:43 PM
To: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io>
Subject: Re: [MSAccessProfessionals] Count records on a report

 

hmmm......

Can you provide an example of a running sum in the following scenario?

idnum         Age

--------         ------

101               55

169               70

169               50

200               25

Let's say I group on idnum and get the following:

idnum         Age

--------         ------

101               55

169               70

200               25

so number of people >= 55 should be 2 but I will get 3 unless I use a running sum.

Regards

On Monday, July 13, 2020, 04:22:56 PM AST, Duane Hookom <duanehookom@hotmail.com> wrote:

If your report's record source has duplicate id numbers, any count will include the duplicates. To avoid this, you can typically use a Running Sum text box in a group section.

Regards,

Duane

---

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> on behalf of M Garcia via groups.io <toukey1=yahoo.com@groups.io>
Sent: Monday, July 13, 2020 3:17 PM
To: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io>
Subject: Re: [MSAccessProfessionals] Count records on a report

 

Just a quick question before this issue is closed.

If the report had duplicate id numbers for example and you group by id numbers on the report so that you get unique id numbers, if you do a count, would it include the id numbers that are not shown?

Regards

On Monday, July 13, 2020, 04:12:37 PM AST, M Garcia via groups.io <toukey1=yahoo.com@groups.io> wrote:

Only just got some time to confirm this.  Yes moving the text box to the Report footer resolved the error.

Thanks again for your assistance.

Regards

On Monday, July 13, 2020, 10:29:19 AM AST, Duane Hookom <duanehookom@hotmail.com> wrote:

Toukey,

Is your issue resolved?

Regards,

Duane

---

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> on behalf of M Garcia via groups.io <toukey1=yahoo.com@groups.io>
Sent: Monday, July 13, 2020 8:52 AM
To: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io>
Subject: Re: [MSAccessProfessionals] Count records on a report

 

Thanks Duane

On Jul 13, 2020, at 8:52 AM, Duane Hookom <duanehookom@hotmail.com> wrote:



Hi Toukey,

You will get an #Error if you place the text box in a Page header or footer. Placing the calculation in a report or group header or footer should work.

Your age calculation looks like at any given date, one half might be a year off. 

Regards,

Duane

---

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> on behalf of M Garcia via groups.io <toukey1=yahoo.com@groups.io>
Sent: Sunday, July 12, 2020 10:40 PM
To: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io>
Subject: Re: [MSAccessProfessionals] Count records on a report

 

I'm calculating the Age in the report query - : Age: IIf(IsNumeric([custidnum]),Year(Now())-Left([custidnum],4),0) 

I am extracting the first 4 numbers from the id number, which is the year, and subtract it from the current year to come up with the age.

On Sunday, July 12, 2020, 11:17:27 PM AST, Duane Hookom <duanehookom@hotmail.com> wrote:

What is the data type of the age and what is the calculation?

Duane

Sent from my mobile

On Jul 12, 2020, at 9:03 PM, M Garcia via groups.io <toukey1=yahoo.com@groups.io> wrote:

 Thanks for the response below.

I am getting #Error. Is it because the Age field is a calculated field?

Regards

On Jul 12, 2020, at 9:42 PM, Duane Hookom <duanehookom@hotmail.com> wrote:



Hi Toukey,

You can add a text box with a control source of:

=Abs(Sum([Age]>25))

Regards,

Duane

---

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> on behalf of M Garcia via groups.io <toukey1=yahoo.com@groups.io>
Sent: Sunday, July 12, 2020 8:35 PM
To: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io>
Subject: [MSAccessProfessionals] Count records on a report

 

Hi,

I have a report that list names and ages.  How can I count the number of persons that are over 25?

Regards
Toukey