Access Module to report field with max value

[tekerin]

Hello,



I am fairly new to programming and so far I've only been able to create a database using queries (the drag and drop a link kind, not SQL). I am trying to find the max value from a table and return the field header, and I think I need to create that via a module, which I was hoping to get some help with. For example, in the below table, I am trying to return the month where each client had the largest value. A would return Mar while B would return Feb. Is there an easy way to create a module for this? Ideally, the output of "Mar" & "Feb" can be in a newly created column in Table 1, or if I can create a 2nd table containing this information with Customer Name filled as well, that would work.

Thanks in advance for your help. I am just clueless when it comes to programming.

Table1:

Customer Name	Jan	Feb	Mar
A	1	2	3
B	4	5	0

[pbaldy]

The design isn't normalized, but you could adapt this:

Minimum or Maximum Value Across Fields of Record

[June7]

As Paul noted, if data was normalized, code would not be needed - a query could accomplish without a custom function.

[tekerin]

Thanks guys for your help. I know it's not normalized and it's probably not the best way of doing it, it's just that I have to work with about 7 files, each with over ~100k records, so I was hoping to not normalize the data if I can. The link to the ACC2000 worked perfect! Thanks very much. One last question, how do I actually find the month that the max value occurs in? I know have:

Table1:
Customer Name, Max Value (this is showing as text for some reason, not values)
A, 3
B, 5

How do I actually report the month as well, like:
Customer Name, Max Value, Month
A, 3, Mar
B, 5, Feb

Thanks again for everyone's help.

[pbaldy]

I'll stay out of June's way.

[June7]

Since you don't want to normalize, more VBA. The code Paul referenced could be modified to return the Month fieldname and Max as a single data. Or write another function to return the month fieldname of the maximum value.

Suggest you take a crash course in VBA programming, looks like you're really going to need it.

However, another option might be UNION query. A UNION can rearrange data to an essentially normalized structure. Then use that query as you would the equivalent table for data analysis, could not edit data through the UNION.

SELECT [Customer Name], "Jan" As Mo, [Jan] As Data FROM tablename
UNION SELECT [Customer Name], "Feb", [Feb] FROM tablename
UNION SELECT [Customer Name], "Mar", [Mar] FROM tablename;

Are there multi-year records?

7 files, only 100,000 records each? I have worked with posters who described Access database with millions of records.

[tekerin]

Thanks for your response.

For the VBA code, what's the syntax you would use to grab the header field? I've had some basic experiences with VBA before, but I searched online for a bit and couldn't find how to return the header once a value is matched. Is this an easy code that can be created?

Thanks again.

[June7]

I took another look at the suggested code and modifying it would be more difficult than I first thought, so would be another function.

Why does it matter what field has the max? What if there is a tie?

Have you explored the UNION approach? No VBA needed.

[tekerin]

I need to reply the header as we want to report the month that the maximum resides in for each company. There shouldn't be tie as this is really for a stress test (just used company / month to illustrate), and each stress test is unique so the final figure will be unique as well.

I know this isn't best practice, but would it be easier if i duplicated the header into the first record always?

Thanks again.

[June7]

Sorry, don't follow the question.

Again, have you considered the UNION query? - which would eliminate need for any VBA