Complex Autonumber Problem in a query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Franz Carlson - 31 Mar 2006 15:28 GMT

Hi friends

Maybe this question is already somewhere answered, but as English is not my
mother language, maybe I have not found it. The problem:

I have a table with sales contracts. the relevant fields are:
contract number, shipping date, salesperson. regionalmanager

What I need is the table grouped by salesperson and within this group sorted
by shipping date and to this I need an increasing autonumber, starting again
at each salesperson.

the query will be filterd from a form by the name of the regionalmanager, so
the output will be a list, where we see the sales of each sales person of the
selected region with an autonumber, showing the ranking of the sales. The
reason for this is that the region manager gets an extra commission, which is
decreasing on the first 3 sales of each sales person (but the query must not
be limited to 3 records and don't worry about the commsionnsion calculation).
The only problem is: it should be quite quick as we have other querries
running at the same time for the total commission calculation on the form.

I know, on a report this is easy, but I need a query, for the reasons above.

Thanky for all help, it is just too complex for me

All the best from wet Prague

Signature

Franz

Reply to this Message

Tom Ellison - 31 Mar 2006 15:45 GMT

Dear Franz:

This would not technically be an "autonumber" but it is commonly called a
"ranking".

SELECT salesperson, [shipping date],
(SELECT COUNT(*)
FROM YourTable T1
WHERE T1.salesperson = T.salesperson
AND T1.[shipping date] < T.[shipping date]) + 1
AS Rank
FROM YourTable T
WHERE regionalmanager = [forms]![form name]![control name]
ORDER BY salesperson, [shipping date]

Substitute the actual name of YourTable and the correct [form name] and
[control name] as the source of the filter for regionalmanager.

The technique is common, and is called a correlated subquery. The T and T1
are essential, and are called aliases. This allow there to be 2 independent
references to the same table.

I added 1 so the ranking would start from 1. If desired, a 0 based ranking
can be formed by removing that.

Please let me know if this helped, and if I can be of any other assistance.

Tom Ellison

> Hi friends
>
[quoted text clipped - 31 lines]
>
> All the best from wet Prague

Reply to this Message

Franz Carlson - 31 Mar 2006 17:05 GMT

> Dear Franz:
>
[quoted text clipped - 60 lines]
> >
> > All the best from wet Prague

Hi Tom

It helped!!

This is the "final" version (the external references have been replaced for
easier testing by fixed values:

SELECT T.EmployeeID, [BonusID] AS Expr1, (SELECT COUNT(*)
FROM qryBonusRek T1
WHERE T1.employeeID = T.employeeID
AND T1.[BonusID] < T.[BonusID])+1 AS Rank
FROM qryBonusRek AS T
WHERE (((T.[RegManager)=10417) AND ((T.PeriodNr)=34))
ORDER BY T.EmployeeID, [BonusID];

The reason why shipped date has been replaced by a bonusID is simple, if you
have 2 orders shipped the same day, I had the same ranking. So I created a
field by multiplying the shipped date by 1000000 and adding the OrderID to
create an uniqe field. The orderID only is possible - even if it is unigque,
because it is not sorted according to the shipped date.

Many thanks form Prague

Franz

Reply to this Message