Count in a query problem

M

Mike B

I have a query that is looking a table of part numbers.
I want to be able to see how many times each part number is in the table.

I created a new field called CountPart:[ODPART]
Turned on the Totals function.
And have the field ODPART with GroupBy.

Put all I get is 1 in the CountPart field.

What have I done wrong?
Or
Is there a better way of doing this.


My end result is to find all the part number that have been ordered more
than three times. If I can get the count field to works with the right
value. Then I can select on that field for >=3 and get just the part number
I want.

Thanks for the help,
Mike
 
J

Jeff L

Did you put Count under CountPart:[ODPART] on the Totals Row?

Your SQL should look like this:
Select ODPart, Count(ODPart) As CountPart
From YourTableName
Click to expand...
Group By OdPart
Having Count(ODPart) >= 3

Hope that helps!


Mike said:
I have a query that is looking a table of part numbers.
I want to be able to see how many times each part number is in the table.

I created a new field called CountPart:[ODPART]
Turned on the Totals function.
And have the field ODPART with GroupBy.

Put all I get is 1 in the CountPart field.

What have I done wrong?
Or
Is there a better way of doing this.


My end result is to find all the part number that have been ordered more
than three times. If I can get the count field to works with the right
value. Then I can select on that field for >=3 and get just the part number
I want.

Thanks for the help,
Mike
Click to expand...
 
M

m.bunch

Thanks, worked great.
Life saver I needed this report complete tomorrow for a meeting.

Thanks again.
Mike

~~~~~~~~~~~~~~~~~~`




Jeff L said:
Did you put Count under CountPart:[ODPART] on the Totals Row?

Your SQL should look like this:
Select ODPart, Count(ODPart) As CountPart
From YourTableName
Click to expand...
Group By OdPart
Having Count(ODPart) >= 3

Hope that helps!


Mike said:
I have a query that is looking a table of part numbers.
I want to be able to see how many times each part number is in the table.

I created a new field called CountPart:[ODPART]
Turned on the Totals function.
And have the field ODPART with GroupBy.

Put all I get is 1 in the CountPart field.

What have I done wrong?
Or
Is there a better way of doing this.


My end result is to find all the part number that have been ordered more
than three times. If I can get the count field to works with the right
value. Then I can select on that field for >=3 and get just the part
number
I want.

Thanks for the help,
Mike
Click to expand...
Click to expand...
 
Top