Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Reports / Printing / December 2005

Tip: Looking for answers? Try searching our database.

Supress Counting of Duplicate Primary Keys

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Paddler - 11 Dec 2005 19:57 GMT
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)?
SA - 12 Dec 2005 11:38 GMT
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
Signature

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

>I have a report showing Building Permit primary key ID's with hide
>duplicates
[quoted text clipped - 6 lines]
> For the report summary, How do I count the true number of BPermitID's (not
> duplicates)?
Paddler - 13 Dec 2005 21:48 GMT
> Paddler:
>
[quoted text clipped - 44 lines]
>
> Steve
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
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.