Need access expert

[kashif.special2005]

Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
I want below like out put in MS-ACCESS<o></o>
<o> </o>
Name OutPut<o></o>
Kashif 1<o></o>
Kashif 2<o></o>
Michael 1<o></o>
Kashif 3<o></o>
Michael 2<o></o>
Derek 1<o></o>
Derek 2<o></o>
Kashif 4<o></o>
<o> </o>
In excel I will apply this formula =COUNTIF($A$2:A2,A2), how can I get this result in MS-ACCESS.<o></o>
<o> </o>
Thanks,<o></o>
Kashif.<o></o>

 

[SydneyGeek]

You need a subquery.

This example is based on tblPeople with these fields:
NameID (Autonumber, Primary Key)
Person (Text)

SELECT tblPeople.NameID
, tblPeople.Person
, (SELECT COUNT(*) FROM tblPeople AS T WHERE T.NameID<=tblPeople.NameID AND T.Person=tblPeople.Person) AS Tally
FROM tblPeople;

Denis

 

[kashif.special2005]

Thanks for reply but query is not working as I want

<TABLE cellSpacing=0 bgColor=#ffffff border=1><CAPTION>Name_Table</CAPTION><THEAD><TR><TH borderColor=#000000 bgColor=#c0c0c0>Name</TH><TH borderColor=#000000 bgColor=#c0c0c0>OutPut</TH></TR></THEAD><TBODY><TR vAlign=top><TD borderColor=#c0c0c0>Kashif</TD><TD borderColor=#c0c0c0 align=right>1</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0>Mahvish</TD><TD borderColor=#c0c0c0 align=right>1</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0>Kashif</TD><TD borderColor=#c0c0c0 align=right>2</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0>Mahvish</TD><TD borderColor=#c0c0c0 align=right>2</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0>Kashif</TD><TD borderColor=#c0c0c0 align=right>3</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0>Derek</TD><TD borderColor=#c0c0c0 align=right>1</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0>Kashif</TD><TD borderColor=#c0c0c0 align=right>4</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0>Derek</TD><TD borderColor=#c0c0c0 align=right>2</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0>Vishal</TD><TD borderColor=#c0c0c0 align=right>1</TD></TR></TBODY><TFOOT></TFOOT></TABLE>

I want result as above I have only Name column in a table.

Thanks & Regards,
Kashif.

 

[SydneyGeek]

You will need to add an ID field for the query to work, otherwise Access won't be able to determine the correct order for the records. As a general rule every table needs a primary key field. I almost always use an Autonumber for the primary key.

Denis