Count, Group By Q

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a Count Query that I would like it to only look up the 3rd Item in the
ID field.

I would like to find all the 1D1 that are in the ID field. But the field has
1D1W, 1D1S, 1D2R Ect. so the get the following:

2 1D1W
6 1D1S

I would like it to just give me 8 1D1

I think that I need to set the Criteria in the ID field but I do not know
what to put in it.

Thanks

Keith
 
KAnoe said:
I have a Count Query that I would like it to only look up the 3rd Item in the
ID field.

I would like to find all the 1D1 that are in the ID field. But the field has
1D1W, 1D1S, 1D2R Ect. so the get the following:

2 1D1W
6 1D1S

I would like it to just give me 8 1D1
Click to expand...


Maybe this will do that:

SELECT Left(ID, 3) As IDX, Sum(amount) As Total
FROM table
GROUP BY Left(ID, 3)
 
SELECT Left(ID,3) as FirstThree, Count(Id) as CountID
FROM YourTable
WHERE ID Like "1D1*"
GROUP BY Left(ID,3)

By the way, since you say you have a query already, it is usually helpful to
post the SQL of the query. That tells us whether you have other fields or other
tables involved, etc.
 
Thanks

John Spencer said:
SELECT Left(ID,3) as FirstThree, Count(Id) as CountID
FROM YourTable
WHERE ID Like "1D1*"
GROUP BY Left(ID,3)

By the way, since you say you have a query already, it is usually helpful to
post the SQL of the query. That tells us whether you have other fields or other
tables involved, etc.
Click to expand...
 
Back
Top