Need to count records 1

[tarena]

I am using Access 97 and I need to count the number of records by part number. Can this be done with one query? The Fields I have in my table are "PartNum" and "SerialNum". I need to count how many "SerialNum" entries are made for each "PartNum". I tried the DCount but it only added the total records. Any help?

Thank you
Tarena

 

[Kiwiman]

Yoiu can do in simply select query. Use the totals (sigma) option on the Query Design toolbar

Group by PartNum and count SerialNum

 

[BigRed1212]

Or try punching in SQL something like

select partnum, count(serialnum)
from yourdatabase
group by partnum
order by partnum

 

[genomon]

I tried the DCount but it only added the total records.

Just curious - did you use the WHERE parameter in your DCount? Seems like it could have worked, although I like the above suggestions better...

"Artificial Intelligence is no match for Natural Stupidity"

 

[tarena]

Alright, to do the simple query do I add any fields or just add the table and then just build the expression?

 

[Golom]

What BigRed1212 gave you will do it although you probably want to give the count a field alias.

Select partnum, count(serialnum) [blue]As [CountofSerialnum][/blue]
from yourdatabase
group by partnum
order by partnum

Change "yourdatabase" to the name of the table.

 

[Kiwiman]

Your table needs to be added to the table first, then you need to add your two fields to the design grid, then apply the totals as above

Take it Easy
Man with one chopstick go hungry