Contract Query to Count unique records based on the date.

If you remove the Description field and sum the ContractValue, you'll get three records.

SELECT DISTINCT Contract.ContractDate, Contract.ContractNumber, Sum(Contract.ContractValue) AS SumOfContractValue, Count(Contract.ContractDate) AS CountOfContractDate
FROM Contract
GROUP BY Contract.ContractDate, Contract.ContractNumber;

Select allOpen in new window

Paul

Thanks!  That does work, but unfortunately, I need to keep at least the description field.

Any "Row" in a Totals (Summary) query might contain data from multiple records, ...so you cant really get a unique "description" of a record that summarizes multiple rows (that might contain multiple descriptions).
make sense?

For example, the "$7,000" is the sum of the first two records in the table.
Each of those two records has a different description ("Line 1" and "Line 2", respectively)
So, which of these descriptions would be displayed for the summary $7,000???

That you for your comment, Jeffrey.  Line 1 would be the correct description.

Does this give you the results you're looking for?

SELECT DISTINCT Contract.ContractDate, Contract.ContractNumber, Sum(Contract.ContractValue) AS SumOfContractValue, Count(Contract.ContractDate) AS CountOfContractDate, Min(Contract.Description) AS MinOfDescription
FROM Contract
GROUP BY Contract.ContractDate, Contract.ContractNumber;

Select allOpen in new window