Supress Counting of Duplicate Primary Keys

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

Guest

I have a report showing Building Permit primary key ID's with hide duplicates
set to yes. On this same line I have subform data that prints the BPermit
Payments primary key ID's. When people buy BPermit extensions the data goes
on a subform and there is a new report line without the original BPermit ID
( hide duplicates).
For the report summary, How do I count the true number of BPermitID's (not
duplicates)?
 
Paddler:

It is unclear whether the Building Permit PKID is what you want to count or
the Building Permit Payment PKID to count up total payments. Either way
however, you can do this by reorganizing your report a bit and then using a
little code.

1.) Create a Group By in your report on the Building Permit PKID.
2.) Add a Group Header for this group. You need not display it, but you
could, adding the data that is in your detail area for each building permit
to the group header. You can then create a sub report for the Building
Permit Payments and add the sub report to your detail section.
3.) Then add code to your report like this:

a.) In the report general module section add two variables:

Dim lngBuildingPermits as Long
Dim varCurrentBuildingPermit as Variant 'only cause I don't know the type
of the PK

b.) In the BP group header on Print Event add code like this:
'This will count the number of unique building permits
If varCurrentBuildingPermit <> Me!YourBuildingPermitControl Then
lngBuildingPermits = lngBuildingPermits + 1
varCurrentBuildingPermit = Me!YourBuildingPermitControl
End if

c.) In report footer add an unbound control, I'll call txtCntBuildingPermits

Then in the On Print event of the report footer add code like:

Me!txtCntBuildingPermits = lngBuildingPermits

That will give you a count of the unique building permits.

HTH
 
SA said:
Paddler:

It is unclear whether the Building Permit PKID is what you want to count or
the Building Permit Payment PKID to count up total payments. Either way
however, you can do this by reorganizing your report a bit and then using a
little code.

1.) Create a Group By in your report on the Building Permit PKID.
2.) Add a Group Header for this group. You need not display it, but you
could, adding the data that is in your detail area for each building permit
to the group header. You can then create a sub report for the Building
Permit Payments and add the sub report to your detail section.
3.) Then add code to your report like this:

a.) In the report general module section add two variables:

Dim lngBuildingPermits as Long
Dim varCurrentBuildingPermit as Variant 'only cause I don't know the type
of the PK

b.) In the BP group header on Print Event add code like this:
'This will count the number of unique building permits
If varCurrentBuildingPermit <> Me!YourBuildingPermitControl Then
lngBuildingPermits = lngBuildingPermits + 1
varCurrentBuildingPermit = Me!YourBuildingPermitControl
End if

c.) In report footer add an unbound control, I'll call txtCntBuildingPermits

Then in the On Print event of the report footer add code like:

Me!txtCntBuildingPermits = lngBuildingPermits

That will give you a count of the unique building permits.

HTH
--
Steve Arbaugh
ACG Soft
http://ourworld.compuserve.com/homepages/attac-cg




Steve
Click to expand...
Thanks a lot. This solved mu problem and took me places I have never been
before. But the instructions were complete & clear.
I did have a BPermit group but no group header. Both PK's (BPermitID and
BPPaymentID are long integers.
Now on to problem #2.

Thanks again
Paddler
 
Back
Top