Increment value for sorting

[Opal]

I need to show an increment value
on a report but I need to create it in the
underlying query for the report.

The report shows OT hours offered
and I need to sort the report by group
code and then from lowest to highest
but I need to sort the query by lowest
to highest first. I need to show an
increment # for the supervisor
to offer additional OT...

Data should look like this:

Emp# OT Offered Group Rank
1234 0.0 A 1
3241 1.0 A 3
3438 0.0 B 2
4532 2.0 B 4

Is there a way to create an increment number
in the query? I have done it in the report but
I do not get the desired results. Thanks.

 

[Jeff Boyce]

Opal

Are you saying that you try to do the same sorting in your report that you
have in your query, but it isn't working? Did you set the Grouping/Sorting
in your report design view?

Since your query is to be used in your report, what would force you to do
the sorting in the query? After all, what you care about is the output
(i.e., the report), right?

Are you saying the query has to do this because you haven't been able to
find a way to have the report do it?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[KARL DEWEY]

I need to sort the report by group code and then from lowest to highest but
I need to sort the query by lowest to highest first.
The first part of the sentence says to sort by value after group but second
phrase says to sort by value first. I do not follow what you need it to do.
You example of an output shows no 'increment #' unless it is the Rank.

Do you want a continuous Rank number starting with lowest group through
ascend values of that group and continuing with next group values in
ascending order?

If you can follow this and substitute your field and table names --
SELECT Aaron.FirstName, Aaron.LastName, (SELECT Count(*) FROM Aaron AS [XX]
WHERE [XX].FirstName & [XX].LastName <= Aaron.FirstName & Aaron.LastName) AS
Rank
FROM Aaron
GROUP BY Aaron.FirstName, Aaron.LastName, Aaron.FirstName & Aaron.LastName;

 

[Opal]

Yes, the increment # is the Rank.

I currently have only the sorting in the report, not
the query, but the report sorting will not give me a
proper rank field if I sort from lowest to highest OT
first and then by group and Emp #.

As you can see from my example
group A is together, B is together,
the emp #'s are in ascending order
within their groups. The rank is not to
be ordered, but it needs to correspond
from lowest to highest.

 

[KARL DEWEY]

Were you able to follow the example I posted?

 

[John Spencer]

UNTESTED SQL follows:

SELECT [Emp#], [OT Offered], Group]
, (SELECT Count(*)
FROM [SomeTable] as Temp
WHERE Temp.[OT Offered] <= [SomeTable].[OT Offered]
AND Temp.[Group]<= [SomeTable].[Group]) As Rank
[SomeTable]

You could end up with a rank sequence with TIES in it. For instance, adding
another row to your sample, your rank values would look like the following.
Note that there is no Rank of 2.

Emp# OT Offered Group Rank
1234 0.0 A 1
3241 1.0 A 4
3438 0.0 B 3
4532 2.0 B 5
1111 0.0 B 3


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Opal]

Karl,

If I'm not mistaken, your example is the same as
John's....?

This is what I have for my SQL:

SELECT [TMNumber],[Hours15Offered],[GLink],
(SELECT Count(*)
FROM [Qualifying] as Temp
WHERE Temp.[Hours15Offered] <= [Qualifying].[Hours15Offered]
AND Temp.[GLLink] <= [Qualifying].[GLink] ) As Rank
FROM [Qualifying];

Unfortunately, this does not give the results I need.

My original query looks like this:

SELECT Qualifying.TMNumber, Sum(Qualifying.Hours15Offered)
AS SumOfHours15Offered, Qualifying.GLLink
FROM Qualifying
GROUP BY Qualifying.TMNumber, Qualifying.GLLink
ORDER BY Sum(Qualifying.Hours15Offered) DESC;

In your example my results do not add the TMNumber total
Hours15Offered so I get multiple results for each TMNumber
and I should get 56 results (total # of TMNumbers) and the
ranking duplicates itself as follows:

Emp# OT Offered Group Rank
1234 0.0 A 10
3241 1.0 A 20
3438 0.0 B 10
4532 1.5 B 21
4532 0.5 B 15

Any further help / direction would be appreciated.

 

[Opal]

Okay.... did some more research and have come
up with the following that works:

I create a make table query using the following
which sorts the records the way I want them:

SELECT DISTINCTROW Sum(Qualifying.Hours15Offered)
AS SatOT, Qualifying.TMNumber, Qualifying.GLink INTO NW15Qualify
FROM Qualifying
GROUP BY Qualifying.TMNumber, Qualifying.GLink
ORDER BY Sum(Qualifying.Hours15Offered), Qualifying.TMNumber;

Then I execute the following:

CurrentDb.Execute "ALTER TABLE NW15Qualify ADD COLUMN Rank COUNTER
(1,1)"

Thank you for helping point me in the right direction on how to
find what I was looking for