record count and ms access query

  • Thread starter Thread starter Scott
  • Start date Start date
S

Scott

I have a query (created in design view, not created directly in SQL)
and it return 10 records.

Here is the sql for reference:

SELECT caps_1pk_items.ITEM, root_INVPKGE.FINISHITEM,
root_SALESINV.DESC1, root_SALESINV.PRICE
FROM ((caps_1pk_items INNER JOIN b12_1pk_items ON caps_1pk_items.ITEM =
b12_1pk_items.ITEM) INNER JOIN root_SALESINV ON caps_1pk_items.ITEM =
root_SALESINV.ITEM) INNER JOIN root_INVPKGE ON root_SALESINV.ITEM =
root_INVPKGE.ITEM
GROUP BY caps_1pk_items.ITEM, root_INVPKGE.FINISHITEM,
root_SALESINV.DESC1, root_SALESINV.PRICE
HAVING (((caps_1pk_items.ITEM) Like "A*"));


2 of the records have the same key - ITEM - (with other fields)
2 of the records have the same key - ITEM -(with other fields)
3 of the records have the same key - ITEM -(with other fields)
3 of the records have the same key - ITEM -(with other fields)

I only want the query to return the records where the total records for
each key is 2.

Can someone please help me with this.

Thanks.
Scott
 
Hi,


Not sure I follow the problem, but technically, that sounds to be a matter
to add the criteria in the HAVING clause:

....
HAVING (((caps_1pk_items.ITEM) Like "A*"))
AND COUNT(*) = 2



Hoping it may help,
Vanderghast, Access MVP
 
Back
Top