DCount to count unique values

[nlkehl]

I am building a query to bring back 1 row for each ECT #. The annual amount needs to be a sum of all of the lines with the same ECT # and I want Access to count the number of unique equipment IDs on the lines with the same ECT #. I did a search in Access and it looks like I need to use the DCount function but I have not been able to set it up correctly. I continue to get a syntax error and I don't feel like the Access Help gives me much clarification.


Here's a sample of the data and what I'm looking for it to return:

ECT # Annual amount Equipment ID
01-03 $100 1234
01-05 $200 3456
01-05 $150 1487
01-03 $100 1234

So, the query should return 2 lines as follows:

ECT # Sum annual amt count equip ID
01-03 $200 1
01-05 $350 2

I can successfully sum the annual amt but I have tried to use the Expression Builder to build a DCount function without success. I want it to count the # of unique 'equipment ID' per each ECT # from the table "VendorServiceContractExtract."
Here is the expression that I've built that is not working:
=DCount("[VendorServiceContractExtract]![Equipment Id]","[VendorServiceContractExtract]","criteria=n")

[lfpm062010]

There is always more than one way to solve a problem. Let's called the table name "ETC".

I choose to use 2 queries to do the work.

1) First query select unique "ETC#", "Equipment ID" and sum the ".Annual amount"
SELECT ETC.[ECT #], ETC.[Equipment ID], Sum(ETC.[Annual amount]) AS [SumOfAnnual amount]
FROM ETC
GROUP BY ETC.[ECT #], ETC.[Equipment ID];

2) Then use the first query to get your count.
SELECT SumETC.[ECT #], Sum(SumETC.[SumOfAnnual amount]) AS [SumOfSumOfAnnual amount], Count(SumETC.[Equipment ID]) AS [CountOfEquipment ID]
FROM SumETC
GROUP BY SumETC.[ECT #];

[nlkehl]

I was hoping to avoid doing 2 queries and my help search in Access really made it sound like I could use this DCount function and nail it in 1 step. I may try that until someone else comes back with a 1 step solution. Thanks

[Bob Fitz]

Perhaps something like:

SELECT Q.ECT, Count(Q.[Equipment ID]) AS [CountOfEquipment ID], Sum(Q.SumOfAnnualAmount) AS SumOfSumOfAnnualAmountFROM [SELECT VenServContExt.ECT, VenServContExt.[Equipment ID], Sum(VenServContExt.AnnualAmount) AS SumOfAnnualAmount FROM VenServContExt GROUP BY VenServContExt.ECT, VenServContExt.[Equipment ID]]. AS Q
GROUP BY Q.ECT;