Counter

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

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

Try:

SELECT
(SELECT COUNT(*)+1
FROM
(SELECT DISTINCT
[Name], Symbol, [Date]
FROM YourTable) AS T2
WHERE (T2.[Name] & T2.Symbol
= T1.[Name] & T1.Symbol
AND T2.[Date] T1.[Date])
OR T2.[Name] & T2.Symbol
T1.[Name] & T1.Symbol) AS Counter,
[Name], [Account#], Symbol, [Date]
FROM YourTable AS T1
ORDER BY [Name], Symbol, [Date];

BTW, avoid Name and Date as column names; they are the names of a built in
property and function in Access. Use more specific terms like ClientName,
TransactionDate

Ken Sheridan
Stafford, England

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

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...arted/201002/1