|
wjyoung
Starting Member
2 Posts |
 Posted - 2011-10-18 : 18:02:24
|
Hello forum,I have measurement data in a table for purchased items. The data are recorded against the item ID and the lot ID. There will be any number of items, lots per item, and records per lot. For example:(myTable)ItemID, LotID, MeasurementPartA, LotA.1, DataA.1.1PartA, LotA.1, DataA.1.2PartA, LotA.2, DataA.2.1PartB, LotB.1, DataB.1.1PartB, LotB.1, DataB.1.2PartB, LotB.1, DataB.1.3I want to count the number of measurement for each distinct ItemID+LotID combination.ItemID, LotID, MeasurementCountPartA, LotA.1, 2PartA, LotA.2, 1PartB, LotB.1, 3I'm controlling which ItemID to display with combo box called "PickItemID" on a form called "Data Form". Here is my SQL: quote:
SELECT DISTINCT myTable.ItemID, myTable.LotID, DCount("ItemID","myTable","ItemID = '" & [Forms]![Data Form]![PickItemID] & "'") AS Expr1FROM myTableWHERE (((myTable.ItemID)=[Forms]![Data Form]![PickItemID]));
The (undesired) result is:ItemID, LotID, MeasurementCountPartA, LotA.1, 3PartA, LotA.2, 3PartB, LotB.1, 3It is counting all of the "PartA" records together, regardless of the LotID. How do I get a DCount for the combination of ItemID and LotID? |
|
|
wjyoung
Starting Member
2 Posts |
Posted - 2011-10-18 : 18:30:24
|
quote:
Originally posted by tkizer
Don't use DISTINCT for this, instead use GROUP BY.
Thanks. Here's the SQL I used:SELECT myTable.ItemID, myTable.LotID, Count(myTable.Measurement) AS CountOfMeasurementsFROM myTableGROUP BY myTable.ItemID, myTable.WeighDate; |
 |
|
DISTINCT and DCOUNT problem