Counter

[JE]

I want to add a column to an existing query that is a counter. Example of
data and desired result below. The criteria is: If Name, Symbol and Date are
different, counter changes; if Name, Symbol and Date are same, same counter
as line above.

I read about DCount but was unsuccessful. Any insight or direction to
reference material is most appreciated.

Name Account# Symbol Date
ANDREW 12345 AAA 08-Jan-09
ANDREW 12345 AAA 20-Jan-09
ANDREW 12345 BBB 20-Jan-09
ANDREW 12345 BBB 28-Jan-09
BOB 98765 CCC 30-Jan-09
BOB 98765 CCC 30-Jan-09
BOB 98765 CCC 30-Jan-09


Want
Counter Name Account# Symbol Date
1 ANDREW 12345 AAA 08-Jan-09
2 ANDREW 12345 AAA 20-Jan-09
2 ANDREW 12345 BBB 20-Jan-09
3 ANDREW 12345 BBB 28-Jan-09
4 BOB 98765 CCC 30-Jan-09
4 BOB 98765 CCC 30-Jan-09
4 BOB 98765 CCC 30-Jan-09

 

[KARL DEWEY]

Try these three queries --
JE_1 --
SELECT JE.Name, JE.[Account#], JE.Symbol, JE.Date, (SELECT Count(*) FROM JE
AS [XX] WHERE [XX].[Name] & [XX].[Account#] & [XX].[Symbol] & [XX].[Date] <=
[JE].[Name] & [JE].[Account#] & [JE].[Symbol] & [JE].[Date]) AS [Counter]
FROM JE
ORDER BY JE.Name, JE.[Account#], JE.Symbol, JE.Date;

JE_2 --
SELECT JE_1.Name, JE_1.[Account#], JE_1.Symbol, JE_1.Date, Count(*) AS ZZ
FROM JE_1
GROUP BY JE_1.Name, JE_1.[Account#], JE_1.Symbol, JE_1.Date
ORDER BY JE_1.Name, JE_1.[Account#], JE_1.Symbol, JE_1.Date;

SELECT JE_1.Name, JE_1.[Account#], JE_1.Symbol, JE_1.Date,
IIf([JE_1].[Counter]-[ZZ]=0,1,[Counter]-[ZZ]) AS Count_X
FROM JE_1 INNER JOIN JE_2 ON (JE_1.Date = JE_2.Date) AND (JE_1.Symbol =
JE_2.Symbol) AND (JE_1.[Account#] = JE_2.[Account#]) AND (JE_1.Name =
JE_2.Name);