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);
--
Build a little, test a little.
"JE" wrote:
> 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
>
|
Similar Threads
