If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Counter
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 |
#3
|
|||
|
|||
Counter
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 |
Thread Tools | |
Display Modes | |
|
|