Return the name of a field in a query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Each record has data for 12 months. How can I return the name of the field
with the maximum value for each record?

I know how to get the maximum value but I nee dto return the field name
(month, ie Mar) not the value.

Thanks.

Shawn
 
Dear Shawn:

It sounds as though you have a table with 12 columns named for the months of
the year. Is this the case?

If so, I believe you are the victim of a table design error. If you had 2
columns instead of 12, this would be working out so well for you. Those two
columns would be the name of the month and the value. You would then have
12 rows in the table for 12 months. The process of finding the maximum
value and the month in which it is found would then be relatively easy.

As at least a temporary step, it would be possible to create a UNION query
that gives the appearance to your data which enables this to happen. I
would like to help you construct that, but I would need details on all the
necessary columns in your table on which to build this.

Tom Ellison
 
It might sound like poor database design at first, and perhaps it is.

I am actually in the middle of a process. I have approximately 3500 parcels
each with one or more utility accounts. Each utility account has usage data
for 12 months. I need to total the accounts for each month for each
property. Then I need to find out what the peak month is. I don't care what
the amount is at that point but I need to know the month. The month will be
different for each parcel. Then I need to go back and perform a calculation
on the original database (many accounts for one parcel) using the data in the
field returned as the peak for the entire property.

I have summed the parcels and can identify the peak but must return the name
of the peak field to continue.

Shawn
 
Well it certainly isn't elegant but I "Brute Forced" it. After populating
the MaxUse field I ran successive update querys to populate MaxMonth with the
month that matched the maximum.

for criteria Is Null (pp_month)
IIf([ppeak]=[JAN],"JAN")

and so on for each month of the year.

Not elegant but if you need the data, you need the data.

Thanks, Shawn
 
Back
Top