Trying to do a calculation in Access

[The X-Man]

Hi all

Is there a way to build an expression that will take a list of prices within multiple groups and determine the lowest price within each group? Please see example:

GROUP	AMOUNT	RESULTS
11111	$235.80	$235.80
11111	$56.01	$56.01
11111	$16.12	$8.06
22222	$17.54	$17.54
22222	$12.90	$6.45
22222	$86.19	$86.19
22222	$235.80	$235.80
33333	$161.59	$161.59
33333	$148.50	$148.50
33333	$175.00	$175.00
33333	$140.70	$70.35
44444	$161.59	$161.59
44444	$121.94	$121.94
44444	$161.59	$161.59
44444	$140.70	$140.70
44444	$235.80	$235.80
44444	$37.61	$18.81
55555	$79.37	$79.37
55555	$235.80	$235.80
55555	$145.42	$145.42
55555	$305.31	$305.31
55555	$249.79	$249.79
55555	$269.74	$269.74
55555	$12.69	$6.34
66666	$274.43	$274.43
66666	$19.03	$9.52
66666	$161.59	$161.59
66666	$36.87	$36.87
66666	$161.59	$161.59
66666	$187.68	$187.68

[The X-Man]

My apologies...I left out a crucial part. The lowest price needs to be reduced by 50% while the others remain the same.


Thanks

[ranman256]

make Q1 to get the Min
Grp, Min(AMT)

then Q2, join the data table to Q1 and do [amt]*.5

to alter the Price on the ?[result]? field?
youd have to do a Make Table query to put the results then update the master table off that.

[The X-Man]

Thanks RanMan. The results I have in my previous post is just a brief snap shot of a much larger query I ran. I wanted to incorporate or build an expression to get the results within the current query if at all possible.

[CarlettoFed]

If I understand your problem correctly, this query should be the solution:

SELECT NameTable.GROUP, Min(NameTable.AMOUNT) AS MinAMOUNT, Min(NameTable.RESULTS) AS MinRESULTS
FROM NameTable
GROUP BY NameTable.GROUP;

[ssanfu]

I read this as you have GROUP and AMOUNT and you want to calculate TheResults. ("Group" and "Result" are reserved words in Access so I changed it to "SKU" and "TheResults")


So I started out with a table named "Thexman" (well, I had to name it something) with fields SKU, Amount.
First query named "qryMinAmount"

SELECT Thexman.SKU, Min(Thexman.Amount) AS MinAMOUNT
FROM Thexman
GROUP BY Thexman.SKU;

2nd query named "qryFinalResules"

SELECT Thexman.SKU, Thexman.Amount, IIf([minamount]<>[amount],[amount],[amount]*0.5) AS TheResults
FROM Thexman INNER JOIN qryMinAmount ON Thexman.SKU = qryMinAmount.SKU;