Want a counter for Access Query results

S

Stegra

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?
 
K

KARL DEWEY

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.
 
S

Stegra

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 said:
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 said:
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?
Click to expand...
Click to expand...
 
K

KARL DEWEY

Post sample data and results.
--
KARL DEWEY
Build a little - Test a little


Stegra said:
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 said:
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 said:
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?
Click to expand...
Click to expand...
Click to expand...
 
S

Stegra

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
 
K

KARL DEWEY

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;
 
S

Stegra

KARL DEWEY said:
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;
Click to expand...

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
 
S

Stegra

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
 
S

Stegra

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Sorting Query Results - Advice Please 5
cross tab confusion 2
Need a Dynamic Record Counter Function for Query Records!!!! 4
Query Returning Multiple Results 1
function for a counter 3
Limiting multiple results in query 7
Query with NOT IN and * 2
Query results limited 5

Top