Counter
in [Getting Started]
|
From: JE on 16 Feb 2010 14:20 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
From: KARL DEWEY on 16 Feb 2010 23:25 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 >
From: KenSheridan via AccessMonster.com on 18 Feb 2010 13:59 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/Forums.aspx/access-gettingstarted/201002/1
|
Pages: 1 Prev: Data type mismatch Next: Opening Forms based on Combo Box Selection (Access 2007) |