Count unique values (1 Viewer)

[Romio_1968]

I have a report based on the following query
<CODE>
SELECT GeneralLedger.AddDate, GeneralLedger.Inventory_No, GeneralLedger.Title_ID, GeneralLedger.Call_No, GeneralLedger.Media, GeneralLedger.Title, GeneralLedger.Publisher, GeneralLedger.PublishPlace, GeneralLedger.PrintYear, GeneralLedger.ScrapDate, GeneralLedger.AuthorNames
FROM GeneralLedger
WHERE (((GeneralLedger.AddDate) >= [Forms]![GeneralLedger_Frm]![StartDate] AND (GeneralLedger.AddDate) <= [Forms]![GeneralLedger_Frm]![EndDate]) AND ((GeneralLedger.Inventory_No) BETWEEN ([Forms]![GeneralLedger_Frm]![Start_No]) AND ([Forms]![GeneralLedger_Frm]![End_No]))) OR (((GeneralLedger.Inventory_No) BETWEEN ([Forms]![GeneralLedger_Frm]![Start_No]) AND ([Forms]![GeneralLedger_Frm]![End_No])) AND ((GeneralLedger.ScrapDate) >= [Forms]![GeneralLedger_Frm]![StartDate] AND (GeneralLedger.ScrapDate) <= [Forms]![GeneralLedger_Frm]![EndDate]))
ORDER BY GeneralLedger.Inventory_No;
</CODE>

In the footer section I have an unboundcontrol to get the total number of Inventory_No displayed by the report, using
=Count([Inventory_No])
as control source.
In this case is the total number of records, and it works.

A second control should display the total number of distinct (unique) values in the Title_ID. I tried
=DCount("[Title_ID]","GeneralLedger")
but the result is not correct. It is also indicating the total no of records, instead of unique titles (about 6000)

Please help.
Thank You

 

[June7]

CODE tags use [ ], not < > (in spite of toolbar icon).

You need to build a query that returns unique titles then DCount that query. Using the posted query as data source, like:

SELECT DISTINCT Title_ID FROM queryname;

Do you have a table Titles?

 

[cheekybuddha]

Have you tried more simply:
=Count([Title_ID])

 

[June7]

David, that will not count unique titles from the report RecordSource unless title is not in multiple invoices. Specifying a field counts records where field is not null. In this case expect would be same as Count(*).

 

[cheekybuddha]

I didn't know whether there was any grouping set in the report

 

[June7]

Okay, could give unique count for each invoice if grouping on invoice, but not an over-all unique total for the report.

 

[cheekybuddha]

I defer to your knowledge, June; reports are not my forte.

 

[Romio_1968]

CODE tags use [ ], not < > (in spite of toolbar icon).

You need to build a query that returns unique titles then DCount that query. Using the posted query as data source, like:

SELECT DISTINCT Title_ID FROM queryname;

Do you have a table Titles?

Sorry for the bracketing
Yes, I do have a table Titles

SELECT DISTINCT Title_ID FROM queryname;
Can you please indicate how to do this?

 

[June7]

That is the SQL for a query object that uses your posted query as source. Replace queryname with your actual query name.

You do this by building a query. Exactly what is not clear about that instruction?