Want a counter for Access Query results
in [Queries]
|
From: Stegra on 30 Jun 2008 12:31 I want to create a counter for a query by selected field results Main sort - req number Second sort - Date or Name or Time ...etc Output1 Have counter start on the main sort (req number) and count until that main sort changes. I do not want just the top x results, but the top x results by sort criteria Another reason is to identify the most recent date of activity by req (Using this I could create a second query to find all records by req and last date) Some would be same day others would be two weeks or more. I do not want all the activty up to that point for all req just the activity for the last date... there could be one record or twenty for each request result Output2 By having the counter in this query, I could then create a second query that would take the x results by Condition Example - return top 10 of condition 1, but only top 5 of condition 2 etc I know this will mean having the system understand what was the last record to compare So can this be done or do I have to run through another program to accomplish this?
From: KARL DEWEY on 30 Jun 2008 13:34 Use the Rankin by group -- SELECT Q.[Group], Q.[Points], (SELECT COUNT(*) FROM Product Q1 WHERE Q1.[Group] = Q.[Group] AND Q1.[Points] < Q.[Points])+1 AS Rank FROM Product AS Q ORDER BY Q.[Group], Q.[Points]; If you can not apply this to your table then post your table structure with field names and datatype. -- KARL DEWEY Build a little - Test a little "Stegra" wrote: > I want to create a counter for a query by selected field results > Main sort - req number > Second sort - Date or Name or Time ...etc > > Output1 > Have counter start on the main sort (req number) and count until that main > sort changes. I do not want just the top x results, but the top x results by > sort criteria > > Another reason is to identify the most recent date of activity by req > (Using this I could create a second query to find all records by req and > last date) > Some would be same day others would be two weeks or more. I do not want all > the activty up to that point for all req just the activity for the last > date... there could be one record or twenty for each request result > > Output2 > By having the counter in this query, I could then create a second query that > would take the x results by Condition > > Example - return top 10 of condition 1, but only top 5 of condition 2 etc > > I know this will mean having the system understand what was the last record > to compare > > So can this be done or do I have to run through another program to > accomplish this?
From: Stegra on 1 Jul 2008 12:53 Hello, I tried this, but I don't think that i did it correctly... Let's try this Field 1 Req_No Field 2 Date Field 3 Comments I am looking to tag each DATE & COMMENTS for each REQ_NO Result1 From this I would be able to get the Last Comment Date - (Date sorted decending count=1 ) Retrieve last Comment or Retrieve all comments that happen that day for that Req_No (Req_No Date Combination change but concept remains) Result 2 Using the counter I could retrieve the last X activities per Req_No This one would be used to get X results not caring about the dates So How would I express this to get the basic results Tag each occurance by Req_No Resetting on the new Req_no "KARL DEWEY" wrote: > Use the Rankin by group -- > SELECT Q.[Group], Q.[Points], (SELECT COUNT(*) FROM Product Q1 > WHERE Q1.[Group] = Q.[Group] > AND Q1.[Points] < Q.[Points])+1 AS Rank > FROM Product AS Q > ORDER BY Q.[Group], Q.[Points]; > > If you can not apply this to your table then post your table structure with > field names and datatype. > -- > KARL DEWEY > Build a little - Test a little > > > "Stegra" wrote: > > > I want to create a counter for a query by selected field results > > Main sort - req number > > Second sort - Date or Name or Time ...etc > > > > Output1 > > Have counter start on the main sort (req number) and count until that main > > sort changes. I do not want just the top x results, but the top x results by > > sort criteria > > > > Another reason is to identify the most recent date of activity by req > > (Using this I could create a second query to find all records by req and > > last date) > > Some would be same day others would be two weeks or more. I do not want all > > the activty up to that point for all req just the activity for the last > > date... there could be one record or twenty for each request result > > > > Output2 > > By having the counter in this query, I could then create a second query that > > would take the x results by Condition > > > > Example - return top 10 of condition 1, but only top 5 of condition 2 etc > > > > I know this will mean having the system understand what was the last record > > to compare > > > > So can this be done or do I have to run through another program to > > accomplish this?
From: KARL DEWEY on 1 Jul 2008 14:28 Post sample data and results. -- KARL DEWEY Build a little - Test a little "Stegra" wrote: > Hello, I tried this, but I don't think that i did it correctly... > > Let's try this > > Field 1 Req_No > Field 2 Date > Field 3 Comments > > I am looking to tag each DATE & COMMENTS for each REQ_NO > > Result1 > From this I would be able to get the Last Comment Date - > (Date sorted decending count=1 ) > Retrieve last Comment or > Retrieve all comments that happen that day for that Req_No > (Req_No Date Combination change but concept remains) > > Result 2 > Using the counter I could retrieve the last X activities per Req_No > This one would be used to get X results not caring about the dates > > So How would I express this to get the basic results > Tag each occurance by Req_No > Resetting on the new Req_no > > > "KARL DEWEY" wrote: > > > Use the Rankin by group -- > > SELECT Q.[Group], Q.[Points], (SELECT COUNT(*) FROM Product Q1 > > WHERE Q1.[Group] = Q.[Group] > > AND Q1.[Points] < Q.[Points])+1 AS Rank > > FROM Product AS Q > > ORDER BY Q.[Group], Q.[Points]; > > > > If you can not apply this to your table then post your table structure with > > field names and datatype. > > -- > > KARL DEWEY > > Build a little - Test a little > > > > > > "Stegra" wrote: > > > > > I want to create a counter for a query by selected field results > > > Main sort - req number > > > Second sort - Date or Name or Time ...etc > > > > > > Output1 > > > Have counter start on the main sort (req number) and count until that main > > > sort changes. I do not want just the top x results, but the top x results by > > > sort criteria > > > > > > Another reason is to identify the most recent date of activity by req > > > (Using this I could create a second query to find all records by req and > > > last date) > > > Some would be same day others would be two weeks or more. I do not want all > > > the activty up to that point for all req just the activity for the last > > > date... there could be one record or twenty for each request result > > > > > > Output2 > > > By having the counter in this query, I could then create a second query that > > > would take the x results by Condition > > > > > > Example - return top 10 of condition 1, but only top 5 of condition 2 etc > > > > > > I know this will mean having the system understand what was the last record > > > to compare > > > > > > So can this be done or do I have to run through another program to > > > accomplish this?
From: Stegra on 1 Jul 2008 16:19 Fields - Req_No, Date, Comments Sort by Req_No (Asc), Date (Dec) Basic Raw Data that I can establish a counter Req_no Date Comments 1 06/30/08 Comments…. 1 06/25/08 Comments…. 1 06/25/08 Comments…. 1 05/30/08 Comments…. 1 03/08/08 Comments…. 2 04/30/08 Comments…. 2 04/25/08 Comments…. 2 04/15/08 Comments…. 3 06/29/08 Comments…. 3 06/29/08 Comments…. 3 06/28/08 Comments…. 3 06/25/08 Comments…. This is the results that I am looking for Req_no Date Comments Counter 1 06/30/08 Comments…. 1 1 06/25/08 Comments…. 2 1 06/25/08 Comments…. 3 1 05/30/08 Comments…. 4 1 03/08/08 Comments…. 5 2 04/30/08 Comments…. 1 2 04/25/08 Comments…. 2 2 04/15/08 Comments…. 3 3 06/29/08 Comments…. 1 3 06/29/08 Comments…. 2 3 06/28/08 Comments…. 3 3 06/25/08 Comments…. 4 From this I could set another query so my results would be Counter=1 1 06/30/08 Comments…. 2 04/30/08 Comments…. 3 06/29/08 Comments…. or Counter =1 to get last date then results 1 06/30/08 Comments…. 2 04/30/08 Comments…. 3 06/29/08 Comments…. 3 06/29/08 Comments…. or counter <=3 to get last X results 1 06/30/08 Comments…. 1 1 06/25/08 Comments…. 2 1 06/25/08 Comments…. 3 2 04/30/08 Comments…. 1 2 04/25/08 Comments…. 2 2 04/15/08 Comments…. 3 3 06/29/08 Comments…. 1 3 06/29/08 Comments…. 2 3 06/28/08 Comments…. 3 "KARL DEWEY" wrote: > Post sample data and results. > -- > KARL DEWEY > Build a little - Test a little > >
|
Next
|
Last
Pages: 1 2 Prev: Queries without duplicates Next: using query returns to create new columns |