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
|
|||
|
|||
Want a counter for Access Query results
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? |
#2
|
|||
|
|||
Want a counter for Access Query results
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? |
#3
|
|||
|
|||
Want a counter for Access Query results
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? |
#4
|
|||
|
|||
Want a counter for Access Query results
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? |
#5
|
|||
|
|||
Want a counter for Access Query results
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 |
#6
|
|||
|
|||
Want a counter for Access Query results
Date is a reserved word in Access and should not be used as a field name. I
use Req_Date instead. This query prompts for number of dates to pull. I used table name Stegra. SELECT Q.Req_no, Q.Req_Date, Q.Comments, (SELECT COUNT(*) FROM Stegra Q1 WHERE Q1.[Req_no] = Q.[Req_no] AND Q1.[Req_Date] Q.[Req_Date])+1 AS Rank FROM Stegra AS Q WHERE ((((SELECT COUNT(*) FROM Stegra Q1 WHERE Q1.[Req_no] = Q.[Req_no] AND Q1.[Req_Date] Q.[Req_Date])+1)=[Enter number of last dates])) ORDER BY Q.Req_no, Q.Req_Date DESC , Q.Comments; -- KARL DEWEY Build a little - Test a little "Stegra" wrote: 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 |
#7
|
|||
|
|||
Want a counter for Access Query results
"KARL DEWEY" wrote: Date is a reserved word in Access and should not be used as a field name. I use Req_Date instead. This query prompts for number of dates to pull. I used table name Stegra. SELECT Q.Req_no, Q.Req_Date, Q.Comments, (SELECT COUNT(*) FROM Stegra Q1 WHERE Q1.[Req_no] = Q.[Req_no] AND Q1.[Req_Date] Q.[Req_Date])+1 AS Rank FROM Stegra AS Q WHERE ((((SELECT COUNT(*) FROM Stegra Q1 WHERE Q1.[Req_no] = Q.[Req_no] AND Q1.[Req_Date] Q.[Req_Date])+1)=[Enter number of last dates])) ORDER BY Q.Req_no, Q.Req_Date DESC , Q.Comments; -- KARL DEWEY Build a little - Test a little I will try this tomorrow at work... I still don't see the field that will hold the counter information I hope that this does not just count the occurrences by Req_No I need to be able to tag each occurrence with a number – so I can reference it later |
#8
|
|||
|
|||
Want a counter for Access Query results
SELECT Q.Req_no, Q.Req_Date, Q.Comments, (SELECT COUNT(*) FROM [Counter] Q1
WHERE Q1.[Req_no] = Q.[Req_no] AND Q1.[Req_Date] Q.[Req_Date])+1 AS Rank FROM [Counter] AS Q WHERE ((((SELECT COUNT(*) FROM [Counter] Q1 WHERE Q1.[Req_no] = Q.[Req_no] AND Q1.[Req_Date] Q.[Req_Date])+1))) ORDER BY Q.Req_no, Q.Req_Date DESC , Q.Comments; Req_no Req_Date Comments Rank Want 1 30-Jun-08 Comment5 1 1 1 05-May-08 Comment3 2 2 1 05-May-08 Comment4 2 3 1 04-Apr-08 Comment2 4 4 1 25-Mar-08 Comment1 5 5 2 30-Jun-08 Comment3 1 1 2 30-Jun-08 Comment4 1 2 2 22-Jun-08 Comment2 3 3 2 20-Jun-08 Comment1 4 4 3 02-Jun-08 Comment4 1 1 3 12-May-08 Comment3 2 2 3 05-Mar-08 Comment2 3 3 3 01-Jan-08 Comment1 4 4 4 29-Jun-08 Comment1 1 1 4 29-Jun-08 Comment2 1 2 This is almost what I want, except I do not want the dup RANKS for same date... I want it to just count the entries one at a time - I can group them if needed later Thanks |
#9
|
|||
|
|||
Want a counter for Access Query results
I modified your setup and now I get exactly want I was looking for
Thank you for your help.... From this BASE I can get whatever data is requested in various setups Last Comment (Counter=1) Last Activity Day Comments (Date of last Activity & All Comments for that day) (From here I could sort by either the LAST Comment (DESC) or Last Day Comments then put them in ascending order for that day) Last X Comments (Counter = X) SELECT Q.pkCounterID, Q.Req_no, Req_Date, Q.Req_Time, Q.Comments, (SELECT COUNT(*) FROM [Counter] Q1 WHERE Q1.[Req_no] = Q.[Req_no] AND Q1.[pkCounterID] Q.[pkCounterID])+1 AS Rank FROM [Counter] AS Q ORDER BY Q.Req_no, Q.Req_Date DESC , Req_Time DESC , Q.Comments; pkCounterID Req_no Req_Date Req_Time Comments Rank 5 1 30-Jun-08 9:38:00 AM Comment5 1 4 1 05-May-08 1:28:00 PM Comment4 2 3 1 05-May-08 1:27:00 PM Comment3 3 2 1 04-Apr-08 1:25:00 PM Comment2 4 1 1 25-Mar-08 3:58:00 PM Comment1 5 9 2 30-Jun-08 4:03:00 PM Comment4 1 8 2 30-Jun-08 4:02:00 PM Comment3 2 7 2 22-Jun-08 3:12:00 PM Comment2 3 6 2 20-Jun-08 9:37:00 AM Comment1 4 13 3 02-Jun-08 4:01:00 PM Comment4 1 12 3 12-May-08 12:28:00 PM Comment3 2 11 3 05-Mar-08 8:45:00 AM Comment2 3 10 3 01-Jan-08 8:49:00 AM Comment1 4 15 4 29-Jun-08 10:06:00 AM Comment2 1 14 4 29-Jun-08 10:05:00 AM Comment1 2 |
Thread Tools | |
Display Modes | |
|
|