Count Unique Record in Report Footer 1

[Tamrak]

Good morning,

I have created a report based on table. The table is like the following:

Project_ID, Manager, Resources

1234, John Doe, James A.
1234, John Doe, John D.
1234, John Doe, Julie D
1234, John Doe, Juliet C.
1245, Peter T, Paul O.
1245, Peter T, John C
1256, Cory T, Ken T.
1256, Cory T, Ray S.

......

I created a simple report, grouped by Project ID.

At the Report footer, I would like to do a count on total project. Look like it counted the number of records, rather than Project ID. I have already suppressed the duplicated value.

Based on the sample above, it showed like 8 projects. It supposed to be 3 projects.

I created an unbound field called Total. When I used the control source, =Count(Project_ID), it did not seem to work. I would like it to count the unique record. I could not make it in the Expression Builder.

Can anyone show how I can use the code to summarize the unique count in the report? Thank you.

 

[dhookom]

The generally accepted method is to add a text box to your ProjectID header section:
Name: txtCountProj
Control Source: =1
Running Sum: Over All
Visible: No
Then add a text box to your report footer section:
Control Source: =txtCountProj


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Tamrak]

Thank you so much. It works perfectly.

 

[Lhuffst]

This is exactly what I need but I have to display the count in the report header not footer. If I move the 2nd txt box to the header, it just counts it as 1. Any ideas
Lhuffst

 

[dhookom]

Create a query similar to your reports query however it should be like:
== qgrpProjects ======
SELECT Project_ID
FROM tblProjectStuff
GROUP BY Project_ID;
======================

Then create another query like:
== qcntProjects ======
SELECT Count(*) as ProjCount
FROM qgrpProjects;
======================

The final query should return only a single record with your count of unique projects. Add this query to your reports record source and don't join it to any other table. Add ProjCount to the grid so you can use it anywhere in your report.


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[emmy]

This is close to what I need help with. I have State, County, Category, Allocation. The query that is already the control source of the report I am modifying (to group by state) will return results like:

State County Item Allocation
Washington, County1, Printers, $1,000.00
Washington, County1, Monitors, $1,000.00
Washington, County2, Printers, $5,000.00
Washington, County2, Monitors, $5,000.00

What I need is a total dollar allocation per state in the header of the report. In the above query result Washington was allocated $6,000.00. The results I am getting with trying to display the total allocation is either $1,000.00 or $12,000.00.

I tried adding a text box in each county footer then doing a sum of that text box in the state header, but access thinks I am trying to refer to a field, not a text box - I guess because I hasn't seen the text box yet.

Can anyone help please?

 

[dhookom]

Are you suggesting the Allocation is based on State and County and not Item? Create a query that ignores the Item and groups by State to get the total allocation.

If you can't figure this out, come back with your significant table and field names.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[emmy]

I've decided that in order to get the allocation for the state to show in the header, then I'll just have to re-create the form as main form and sub form. The original designer put a ton of formatting in the form, and I was too lazy to recreate it! Admittedly, doing so in the first place would have wasted less time....
Thanks for taking a look at it though.

 

[piovesan]

Hi;

I have a report that looks like the following:

SELECT DISTINCT LandAssets_classificationsdec18.GROUP_ID, LandGroup_Attributes.LANDNAME, LandAssets_classificationsdec18.ASSET_ID, LandAssets_classificationsdec18.PID, LandAssets_classificationsdec18.SHAPE_Area, LandAssets_classificationsdec18.CLASSIFICATION, LandAssets_classificationsdec18.CLASSHIERARCHY, LandAssets_classificationsdec18.OWNER, streetsdec17.GSA_NAME
FROM ((LandAssets_classificationsdec18 LEFT JOIN civicdec17 ON LandAssets_classificationsdec18.PID = civicdec17.PID) LEFT JOIN streetsdec17 ON civicdec17.STR_CODE = streetsdec17.STR_CODE) LEFT JOIN LandGroup_Attributes ON LandAssets_classificationsdec18.GROUP_ID = LandGroup_Attributes.GROUP_ID
ORDER BY LandAssets_classificationsdec18.GROUP_ID;


The PIDs refer to parcels of land. In the report, I list all the PIDs that relate to a Classification, and the Shape_Area of that PID (or parcel). I also sum up the Shape_Areas to get a total area for each Classification. (The report looks like below)

Classification: CEMETERY
Group_ID 418
Community Location PID Area (sq.m)
00019273 6702
HALIFAX 40823999 13902
HALIFAX 41030701 67686 Total Land Area for 'GROUP_ID' = 418 (3 parcel polygons) 21.8 Acres

My problem is, because of the join to the civicdec17 table, and streetdec17 table. Because of issues with the data in these two table, of which I have no control, I sometimes get a parcel (or PID) related to a bunch of streets and communities (GSA_NAME), so the PID shows up several times, and the area gets summed up for each of those records, giving a false total area. (See below)

Group_ID 205
Community Location PID Area (sq.m)
BEDFORD 00359034 23232
COLE HARBOUR 00359034 23232
DARTMOUTH 00359034 23232
ELMSVALE 00359034 23232
FALL RIVER 00359034 23232
GLEN MARGARET 00359034 23232
HALIFAX 00359034 23232
HARRIETSFIELD 00359034 23232
HEAD OF CHEZZETCOOK 00359034 23232
MCNABS ISLAND 00359034 23232
MOOSELAND 00359034 23232
SACKVILLE 00359034 23232
SHEARWATER 00359034 23232
Total Land Area for 'GROUP_ID' = 205 (13 parcel polygons) 74.6 Acres

What I want to do, therefore, is to sum the SHAPE_AREA on a "unique" value. (I can't use the PID as the unique search criteria because of other reasons).

Can anyone help??
Thanks!!
TP

 

[dhookom]

Can't you create a query that sums the area by whatever? Then just add this totals query to another query to provide the total area.

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[dhookom]

I reposted my reply in the new thread.

Duane MS Access MVP
Now help me support United Cerebral Palsy