Mod to "record count" feature

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi.

With help from this Discussion Group, I have set up a way of counting
records in various Report sections. This works fine but I’ve just noticed
that it includes, in the count, records where there is no data in the
relevant section. Can this be modified so that it only counts records where
data is present?

Here’s some detail. My report has three sections, StaffID Header, SchoolID
Header and StudentID Header. In the StaffID Header I have an invisible text
box called txtCountOfStaffMembers, with control source set to =1 and Running
Sum set to Over All. In the reports footer I have a text box with control
source set to =[txtCountOfStaffMembers] which shows the count of Staff
Members.

As I say, when data is shown in the SchoolID and StudentID sections but no
Staff member name appears in the StaffID section (because one hasn't been
allocated), this is still included in the count of staff members. Is there a
way of changing the code to count only those records that have a Staff member
name appearing in the StaffID Section? Apart from the invisible text box, the
StaffID Header section has only one text field, called txtStaffName.

Thanks, JohnB
 
No need to respond. I figured this one out myself. I replaced the control
source of txtCountOfStaffMembers with the following and it works. Thanks
anyway. JohnB

=IIf([txtStaffName]>"",1,0)
 
John:

Your self-answered question nearly answered mine. But I'm still stuck. In
my report, I have a text field called LiquidatorNumber that is in the detail
section. There is no text box in the Header section.

Here's what I'm trying to accomplish. Based on what you learned, can you
suggest a solution? Thanks.

How do I count records in a report that have multiple items per record? The
report contains 100 records, each of which may have several entries per
record. My report shows a record (LiquidatorNumber), and immediately below
it,
the entries associated with that record (comment A. 5/3/2005, comment B,
6/15/2005, etc).

Access is counting all rows, but I only want to know the count of the Liquid
Number. Any help?

Liquid # Date Recd Comment
12345 6-1-2005 PM'd 5/25/2005
PM'd 5/27/2005

12346 6-2-2005 PM'd 5/29/2005
PM'd 5/29/2005

In the above example, I would like the report's record count to show
quantity 2 records, but Access displays quantity 4.



JohnB said:
No need to respond. I figured this one out myself. I replaced the control
source of txtCountOfStaffMembers with the following and it works. Thanks
anyway. JohnB

=IIf([txtStaffName]>"",1,0)

JohnB said:
Hi.

With help from this Discussion Group, I have set up a way of counting
records in various Report sections. This works fine but I’ve just noticed
that it includes, in the count, records where there is no data in the
relevant section. Can this be modified so that it only counts records where
data is present?

Here’s some detail. My report has three sections, StaffID Header, SchoolID
Header and StudentID Header. In the StaffID Header I have an invisible text
box called txtCountOfStaffMembers, with control source set to =1 and Running
Sum set to Over All. In the reports footer I have a text box with control
source set to =[txtCountOfStaffMembers] which shows the count of Staff
Members.

As I say, when data is shown in the SchoolID and StudentID sections but no
Staff member name appears in the StaffID section (because one hasn't been
allocated), this is still included in the count of staff members. Is there a
way of changing the code to count only those records that have a Staff member
name appearing in the StaffID Section? Apart from the invisible text box, the
StaffID Header section has only one text field, called txtStaffName.

Thanks, JohnB
Click to expand...
Click to expand...
 
Hi. Well, I will try to help, but I'm no MVP that's for sure.

Is your mdb arranged such that "Liquid #" , "Date Recd" and "Comment" are
all fields from the same table, or is "Liquid #" in one table and "Date Recd"
and "Comment" in a separate table, or tables? i.e are we talking "one to
many" relationships? If so, it would be normal to arrange things into
separate Headers within the report. So you would have a "liquid #ID" Header
and then a "CommentID" Header and perhaps a "Date RecdID" Header.

So, say you wanted to count the number of "Liquid #" records, you would put,
somewhere in the "Liquid #ID" Header section, an invisible text box called
txtCountOfLiquid#, with control source set to =1 and Running Sum set to Over
All. In the reports footer you would put a text box (name not important) with
control
source set to =[txtCountOfLiquid#] which would show the count value. You
would do similar for other Sections. That way you get to produce a separate
count for the number of rows shown in each report section.

Or you could you produce a set of queries, each using the Count function to
produce the values you want and then link them all together in one final
query that feeds the report. I'm not too hot on other methods, so if none of
this suits, it may be best to repost.

By the way, it's good practice not to have spaces in field names and I'm not
sure if Access will like the use of # as a character in a field name.

Good Luck JohnB


GrHopp said:
John:

Your self-answered question nearly answered mine. But I'm still stuck. In
my report, I have a text field called LiquidatorNumber that is in the detail
section. There is no text box in the Header section.

Here's what I'm trying to accomplish. Based on what you learned, can you
suggest a solution? Thanks.

How do I count records in a report that have multiple items per record? The
report contains 100 records, each of which may have several entries per
record. My report shows a record (LiquidatorNumber), and immediately below
it,
the entries associated with that record (comment A. 5/3/2005, comment B,
6/15/2005, etc).

Access is counting all rows, but I only want to know the count of the Liquid
Number. Any help?

Liquid # Date Recd Comment
12345 6-1-2005 PM'd 5/25/2005
PM'd 5/27/2005

12346 6-2-2005 PM'd 5/29/2005
PM'd 5/29/2005

In the above example, I would like the report's record count to show
quantity 2 records, but Access displays quantity 4.



JohnB said:
No need to respond. I figured this one out myself. I replaced the control
source of txtCountOfStaffMembers with the following and it works. Thanks
anyway. JohnB

=IIf([txtStaffName]>"",1,0)

JohnB said:
Hi.

With help from this Discussion Group, I have set up a way of counting
records in various Report sections. This works fine but I’ve just noticed
that it includes, in the count, records where there is no data in the
relevant section. Can this be modified so that it only counts records where
data is present?

Here’s some detail. My report has three sections, StaffID Header, SchoolID
Header and StudentID Header. In the StaffID Header I have an invisible text
box called txtCountOfStaffMembers, with control source set to =1 and Running
Sum set to Over All. In the reports footer I have a text box with control
source set to =[txtCountOfStaffMembers] which shows the count of Staff
Members.

As I say, when data is shown in the SchoolID and StudentID sections but no
Staff member name appears in the StaffID section (because one hasn't been
allocated), this is still included in the count of staff members. Is there a
way of changing the code to count only those records that have a Staff member
name appearing in the StaffID Section? Apart from the invisible text box, the
StaffID Header section has only one text field, called txtStaffName.

Thanks, JohnB
Click to expand...
Click to expand...
Click to expand...
 
Add a group based on Liquid. In the group footer, add a control (named
txtCountLiquid) and set its control source to =1 and running sum to over all.
Set the group footer's visible property to false.

In the report footer, add another control (named txtCountAllLiquid). Set its
control source to =txtCountLiquid.
John:

Your self-answered question nearly answered mine. But I'm still stuck. In
my report, I have a text field called LiquidatorNumber that is in the detail
section. There is no text box in the Header section.

Here's what I'm trying to accomplish. Based on what you learned, can you
suggest a solution? Thanks.

How do I count records in a report that have multiple items per record? The
report contains 100 records, each of which may have several entries per
record. My report shows a record (LiquidatorNumber), and immediately below
it,
the entries associated with that record (comment A. 5/3/2005, comment B,
6/15/2005, etc).

Access is counting all rows, but I only want to know the count of the Liquid
Number. Any help?

Liquid # Date Recd Comment
12345 6-1-2005 PM'd 5/25/2005
PM'd 5/27/2005

12346 6-2-2005 PM'd 5/29/2005
PM'd 5/29/2005

In the above example, I would like the report's record count to show
quantity 2 records, but Access displays quantity 4.

JohnB said:
No need to respond. I figured this one out myself. I replaced the control
source of txtCountOfStaffMembers with the following and it works. Thanks
anyway. JohnB

=IIf([txtStaffName]>"",1,0)

JohnB said:
Hi.

With help from this Discussion Group, I have set up a way of counting
records in various Report sections. This works fine but I’ve just noticed
that it includes, in the count, records where there is no data in the
relevant section. Can this be modified so that it only counts records where
data is present?

Here’s some detail. My report has three sections, StaffID Header, SchoolID
Header and StudentID Header. In the StaffID Header I have an invisible text
box called txtCountOfStaffMembers, with control source set to =1 and Running
Sum set to Over All. In the reports footer I have a text box with control
source set to =[txtCountOfStaffMembers] which shows the count of Staff
Members.

As I say, when data is shown in the SchoolID and StudentID sections but no
Staff member name appears in the StaffID section (because one hasn't been
allocated), this is still included in the count of staff members. Is there a
way of changing the code to count only those records that have a Staff member
name appearing in the StaffID Section? Apart from the invisible text box, the
StaffID Header section has only one text field, called txtStaffName.

Thanks, JohnB
Click to expand...
Click to expand...
Click to expand...
 
Back
Top