"Marking" the largest sale

[snipe]

Hey guys,



I feel a little dumb with this problem.

The snippet is of a list of all of the sales of that each sales person has.

What I need to accomplish is to have a new field titled "Max." In this field, for each salesperson, their largest sale will just have an "X." It needs to be the largest sale (based off [Ticket Total]) where STAT_CD is null, ORD_TP_CD is "SAL" and [Has Protection?] = "No".

What is the best way to accomplish this?

Lack of coffee, I'm assuming, is the reason for this little brain fart.

[snipe]

Made a query on top of the one pictured. Sorted Store and SLSP in ascending order and Ticket Total in descending order, while using the criteria I mentioned about. Made another query on top of that one and selected First for CUST_CD, 1st SO, and Ticket Total.

It chose the first sales in the query that is pictured, like it completely ignored the order of the sales in the query I made on top.

I'm stumped...

[ranman256]

Wouldnt you just do a pivot table for MAX?

[snipe]

The end game of this query is to be joined with another query that a report is built on. The report will still need to show every sale, but the largest one will be marked (and not including in some calculations).

I don't have much, or rather any, experience with pivot tables. I was under the impression that you can't build off of the pivot table. Its the "end of the road" so to speak.

[lfpm062010]

Create a "SALMAX" column. Base on your picture. (Code is not tested)

SALMAX: IIF(DMAX("[Ticket Total]", "[RYAN_MOTAGE_INDV_BAK 3]", "STAT_CD IS NULL AND ORD_TP_CD = ""SAL"" AND [Has Protection?] = ""No""") = [RYAN_MOTAGE_INDV_BAK 3].[Ticket Total], "X", "")

[snipe]

You definitely have the right idea with that. Unfortunately it found, and marked, the largest sale out of everyone, instead of the largest sale for each salesperson.

[June7]

Can try TOP N http://allenbrowne.com/subquery-01.html#TopN

Then maybe include that query in another with an expression that compares fields and if matching the 'largest', not to include in calculation.

Otherwise, maybe DMax() domain aggregate function but beware they can be slow.

[lfpm062010]

It looks like it should also include the salesperson's info as part of the criteria (SLSP and maybe SO_STORE_CD if there is more than one store). It is still not tested.

SALMAX: IIF(DMAX("[Ticket Total]", "[RYAN_MOTAGE_INDV_BAK 3]", "SLSP = " & [RYAN_MOTAGE_INDV_BAK 3].[SLSP] & " AND STAT_CD IS NULL AND ORD_TP_CD = ""SAL"" AND [Has Protection?] = ""No""") = [RYAN_MOTAGE_INDV_BAK 3].[Ticket Total], "X", "")

Here is another way. (Code is not tested)

1) Build a summary query (SUMQRY) of (Store - if more than one store), sales person, and max of the ticket total. Something likes this,
SELECT SO_STORE_CD, SLSP, MAX([Ticket Total])
FROM [RYAN_MOTAGE_INDV_BAK 3]
GROUP BY SO_STORE_CD, SLSP;
2) Then use the query above left join with [RYAN_MOTAGE_INDV_BAK 3] ON SO_STORE_CD, SLSP, and MaxOfTicketTotal.
Add a column to check the [RYAN_MOTAGE_INDV_BAK 3].[Ticket Total]
SALMAX: IIF(SUMQRY.[MaxOfTicketTotal] = [RYAN_MOTAGE_INDV_BAK 3].[Ticket Total]), "X", "")