A field to display mycount 1

[serino]

Hi,
Is there a way to have a query count the number of records in a table based on the ID field (auto number) and then display that count as Mycount?

ID Mycount
12 1
13 2
14 3
15 4
16 5

 

[dhookom]

Could you be more specific as to how your count is being generated? You will likely get some guess replies and someone may hit the nail on the head.

However, you should provide more detail so we don't have to make assumptions.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[lespaul]

Here's my guess:

SELECT ID, Count(*) FROM TableName GROUP BY ID

what do I win if I get it right?


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[PHV]

A ranking query ?
SELECT A.ID, Count(*) AS myCount
FROM yourTable AS A INNER JOIN yourTable AS B ON A.ID >= B.ID
GROUP BY A.ID

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[serino]

Thanks for your reply,

I just want a field to count how many records there are in a table. I dont neccessarly have to count the the auto ID field it could just be a text field in the table.

Lets say I had 10 records to begin with and I deleted those 10 records the next number would be 11 in the ID field. I would like to have a field in a table called[mycount] to begin with number 1,2,3..etc.

ID Name Mycount
11 Fred 1
12 Tom 2
13 Sam 3

 

[PHV]

SELECT A.ID, A.Name, Count(*) AS myCount
FROM yourTable AS A INNER JOIN yourTable AS B ON A.ID >= B.ID
GROUP BY A.ID, A.Name

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[serino]

Once again Thank You PHV, it worked like a charm.

 

[lespaul]

I guess PHV won again, he got another purple thingy