Conditional count

[jbr87]

Hi guys and girls,



I'm looking to build a querry that finds duplicate entries of a feild named "assembly_position" and contains two counts, one count that finds the number of times an entry is duplicated. The second count has to count how many of the duplicate entries have been shipped by looking into a "load" feild checking if the field is not null.

so we take my table1:

AsemPos, load
A1, l1
A1, l2
A1, 
A3, l1
A2,
A2,
A3,

the result I'm looking for is:

AsemPos, Countod unique AP, Count of shipped
A1, 3, 2
A2, 2, 0
A3 ,2 ,1

I currently am able to get the unique entries as well as the first count with the following SQL statments

SELECT Sheet1.Assembly_position, Count(Sheet1.Assembly_position) AS CountOfAssembly_position
FROM Sheet1
GROUP BY Sheet1.Assembly_position;

What I need help with is the conditional count of the Items that have been shipped.

Thanks,
Jeff

[maximus]

Tried to replicate your problem I have a Table Table4 with the Fields AssemPos and Load. I have entered the same data you have posted.

I have the following Query:

Column Dup will reflect the Count of an AssemPos Item.
Column LoadCount will show the Count of the AssemPos Item Loaded.


SELECT Table4.AssemPos, DCount("[Assempos]","Table4","[AssemPos]='" & [AssemPos] & "'") AS Dup, DCount("[Load]","Table4","[AssemPos]='" & [Assempos] & "'") AS LoadCount
FROM Table4
GROUP BY Table4.AssemPos;

[jbr87]

Tried to replicate your problem I have a Table Table4 with the Fields AssemPos and Load. I have entered the same data you have posted.

I have the following Query:

Column Dup will reflect the Count of an AssemPos Item.
Column LoadCount will show the Count of the AssemPos Item Loaded.


SELECT Table4.AssemPos, DCount("[Assempos]","Table4","[AssemPos]='" & [AssemPos] & "'") AS Dup, DCount("[Load]","Table4","[AssemPos]='" & [Assempos] & "'") AS LoadCount
FROM Table4
GROUP BY Table4.AssemPos;

Works like a charm thank you so much