Min/Max Query? 1

[Pampers]

Hi there,
I have a (given) table with the following structure:

invoice no; amount
1 ; 200
1 ; 0
2 ; -10
2 ; 0
3 ; 50
3 ; 25

Now I want to make a query that pulls this result:

Desired Result:
invoice no; amount
1 ; 0
2 ; 0
3 ; 25

But I don't know how. Any suggestions?

Pampers
Keeping it simple can be complicated

 

[Remou]

Is that Min for each invoice, or is it based on something else?

 

[Remou]

Sorry, I should have said Min where items are greater than zero, I should also have read your heading!

 

[Remou]

Perhaps:

SELECT tblTable.Inv_No, Min(Abs([Amt])) AS MinAmt
FROM tblTable
GROUP BY tblTable.Inv_No;

 

[dhookom]

Or, do you expect to return the last Amt for Inv_No based on some unknown field that identifies the sort order?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Pampers]

Hi Remou,
Yes it is Min for items greater dan zero, Max (or something for) items smaller than zero. I will check out the code... Keep you posted.

Hi Duane,
I think you right, I forgot to look at the index field. I think a group with a max on the indexfield will return the latest status of the invoice no. I'll check it out in the afternoon when I am at the client. Let you know.

Pampers
Keeping it simple can be complicated

 

[Pampers]

Hi there,
Well, Duane strangely enough there is no such thing as a autono field, so i can not determine on that criterium which is the latest balance.

Remou, well the query selects the right records, but off course makes the negative value a positive one. If I would add them all up, that would create a wrong sum.

Pampers
Keeping it simple can be complicated

 

[traingamer]

How about

SELECT tblTable.Inv_No, Min([Amt]) AS MinAmt
FROM tblTable
[b]where [Amt] >= 0[/b]
GROUP BY tblTable.Inv_No;

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[Pampers]

Hi traingamer,
Yes, that will do halve of the business, for the other half i could use:

SELECT tblTable.Inv_No, Max([Amt]) AS MaxAmt
FROM tblTable
where [Amt] , 0
GROUP BY tblTable.Inv_No;

Pampers
Keeping it simple can be complicated

 

[traingamer]

I missed the clarification where some will all be less than zero. Just combine the two queries with a union and use the MAX of that.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[Pampers]

yes, some customers/invoice no have a negative value and a zero value...

Ok, so I can combine the two queries with a UNION, instead of running two seperate queries. Will the UNION be faster then running them seperate?

Pampers
Keeping it simple can be complicated

 

[traingamer]

Okay - an example Union Query based on table tblTest22 with this data:

[tt]InvNum  Amt
1       200
1       0
2       -10
2       0
3       50
3       25
4       -10
4       -5
3       -5 [/tt]

Union query named qryUnionTest22:

SELECT tblTest22.InvNum, [COLOR=red]Min(tblTest22.Amt) AS MAmt[/color]
FROM tblTest22
WHERE (((tblTest22.Amt)[b]>=0[/b]))
GROUP BY tblTest22.InvNum
UNION SELECT tblTest22.InvNum, [COLOR=red]Max(tblTest22.Amt) AS MAmt[/color]
FROM tblTest22
WHERE (((tblTest22.Amt)[b]<=0[/b]))
GROUP BY tblTest22.InvNum;

returns

[tt]
InvNum  MAmt
1       0
2       0
3       -5
3       25
4       -5 [/tt]

You get two returns for invoice 3, the min amount over 0 (25) and the max amount under 0 (-5).

Additional grouping query to get the maximum of the Union query

SELECT qryUnionTest22.InvNum, Max(qryUnionTest22.MAmt) AS MaxOfMAmt
FROM qryUnionTest22
GROUP BY qryUnionTest22.InvNum;

[tt]
InvNum  MaxOfMAmt
1       0
2       0
3       25
4       -5
[/tt]

Adjust as needed. Both groups include the zero amount, but you can make one select <= 0 or just <0 or whatever you actually need.


Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[traingamer]

Or a single query solution:

SELECT tblTest22.InvNum, Min(tblTest22.Amt) AS MinOfAmt, Max(tblTest22.Amt) AS MaxOfAmt,IIf(Min(tblTest22.Amt)<0,Max(tblTest22.Amt), Min(tblTest22.Amt)) AS MM
FROM tblTest22
GROUP BY tblTest22.InvNum, 4;

which returns

[tt]
InvNum  MinOfAmt        MaxOfAmt        MM
1       0               200             0
2       -10             0               0
3       -5              50              50
4       -10             -5              -5[/tt]

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[traingamer]

Forget the single query solution. It is, of course, incorrect.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[Pampers]

Tanx a lot Traingamer,
All clear... Gonna try that union.

Pampers
Keeping it simple can be complicated