Is there a formula (lack of proper word)

[wheddingsjr]

Hello all

I am new to the forum and fairly new to MS Access and I was hoping someone can assist me with a question. I have a table in an Access database that has two columns which are "MEMBERNO" and "AMOUNT" which contain dollar amounts. Each member number has several lines with different dollar amounts. Is there a way to create a function or "formula" to determine the lowest dollars per member grouping and cut those numbers in half? For example, Member 11111 has 3 lines, the highest dollar amount is untouched but all the subsequent and lower dollars for that member be reduced by 50% I tried to attach the Access database but it wouldn't allow me nor would it allow me to send just a copy of the table.

I hope I explained that sufficiently and thanks for any help provided

 

[JonXL]

Is it possible to have a member with more than one of the same dollar amounts? Like this?

MemberNo	Amount
12345	11.00
12345	11.00
12345	5.00
12345	5.00
12345	7.00

If so, how would this be handled?

 

[jackd]

"I have a table in an Access database that has two columns which are "MEMBERNO" and "AMOUNT" which contain dollar amounts. "
Are there other fields/columns in your table? For instance, is there a unique record id?

Will await your response to JonXL for clarity of requirement.

 

[JonXL]

...

I also should have asked what happens when you have a single MemberNo with only one Amount associated with it and a MemberNo with several records all for the same Amount, for example (assume these are the only entries for the indicated MemberNo):

MEMBERNO	AMOUNT
67890	15.00
67890	15.00
67890	15.00
54862	6.00

What should happen here? Because the Amounts are the same, they both are and are not the 'lowest dollars per member group', as the following query would show:

SELECT
    [MemberNo] AS g_MemberNo,
    MAX([Amount]) AS max_Amount,
    MIN([Amount]) AS min_Amount
FROM
    [WHEDD]
GROUP BY
    [MemberNo]

Yields:

g_MemberNo	max_Amount	min_Amount
54862	6.00	6.00
67890	15.00	15.00

 

[jackd]

What should happen is what is expected or required in your business in normal/typical operation.
For any given column, if the values are the same in each related record, the Max and Min will be that common value. Also, if there is only 1 record in your set/group, then that 1 value will be both Max and Min. In many situations there are many varied values for records in the set/group and the Max and Min are the largest and smallest value respectively. I think the example is not the typical set up, but is real for the values presented. It is a condition that must be considered when dealing with Max and Min values.

Each record in a table should have a unique primary key.

Good luck with your project.