Count

[shahzadsk]

I'm creating a database on a library. As you know there are books e.g. text books, with the same title, but numerous copies. However each copy has a unique Serial No. To find the number of copies available for a certain title, I need to count the number of records of the Serial No.
So I need to know how to count the records in the Serial No table, and then store that value to a field. Can anyone help me with this

[Blood_Luster]

You mean a progam... or ... hmmn, I lost, someone else will help you, sorry

[vass0922]

ok...
So you're trying to group by serial number and get a count right?
SELECT SerialNum, Count(SerialNum)
FROM TableName
GROUP BY SerialNum

... think that works... sql is getting rusty
Then if you wanted to create a table with that information...
SELECT SerialNum, Count(SerialNum) AS CountOfSerialNum
INTO NewTableName
FROM TableName
GROUP BY SerialNum

[shahzadsk]

thanks I'll give it a shot and let you know..any other suggestions I can try though?

[shahzadsk]

Where do I put that code in? In VB, it doesn't seem to work.. Sorry I'm very new at this

[qball]

ugh, hate access.

Anyway, the SQL for the counts is correct, vass0922.

Now, you need to open the access DB, and go to 'Queries'.

5 ways to do this (or more), here's one...

double clickey 'Create query in design view'.
will ask to choose tables and other stuff in 'Show Table' dialog.
just click close. Then View, SQL view.

Cut-n-paste, or write query, then Query, Run to see results or errors. Tweak from there...

[shahzadsk]

Okay.. this is what I did.. I made a form of the books (title, author etc) and made a subform (serial nos)..and in the subform put a control =count(*)
It works fine.. except it only updates when I click on the next and previous button on the form (those triangles).. anyway I can make it update every second or so?