Create Count in Query

[StewartS]

I have a query which returns the following details

Contract_id PO_number etc
1694 1234
1694 1234
1695 1235
1695 1236

So there are incidents where a contract_id can have a single PO_Number against it or Multiple PO_Numbers I am grouping this to connect to a table to bring back some values matching on the Contract_ID with the PO_Number as a field in the new query. If I group on contract and PO then I get individual for most of the outputs but of course multiple details where there are more than one different PO_Numbers to the Contract. I think if I could have a count on the PO_number to return the data as. I can resolve the issue.

Contract_id PO_number etc Count
1694 1234 1
1694 1234 1


1695 1235 1
1695 1236 2


Can anybody help please.

StewartS

[June7]

What you show is a ranking calculation. http://allenbrowne.com/ranking.html

Try:

Query1
SELECT DISTINCT Table1.Contract_id, Table1.PO_Number FROM Table1;

Query2
SELECT Contract_id, PO_Number, (SELECT Count(Contract_id) As HowMany FROM Query1 AS Dupe
WHERE Dupe.Contract_id=Query1.Contract_id AND Dupe.PO_Number <= Query1.PO_Number) AS BeatenBy
FROM Query1;

Query3
SELECT Table1.Contract_id, Table1.PO_Number, Query2.BeatenBy
FROM Query2 INNER JOIN Table1 ON (Query2.PO_Number = Table1.PO_Number) AND (Query2.Contract_id = Table1.Contract_id);

[StewartS]

Thanks this is just what I wanted