Help With Access

[Bullfrog]

I am not sure if this is right group for this. I'm sorry if not. Here
is my situation:

I have aa date field that I put whatever date in (date doesn't matter)
I have 25 fields that I put a number in (1-100). I have another field
(Peak) that I put the highest number in that is in the 25 fields. I
also have another date field that I put the date of the highest number
(based on 7 days). An example:

Date Field has 03-10-2007
the 25 fileds have as follows: 50,25,3,10,15,24 Only used 6 in this
case)
Based on this, the highest is 3, so this goes in the 'Peak' field and
the 2nd date field would have 03-24-2007 (Based on 3rd week with 3-10
being the first week).

My question, is there a way to do this automatically?? right now I
have to manually update the peak field and the 2nd date field.

 

[Guest]

You are doing spreadsheet stuff. How is 3 the peak and not 50?
I recommend using a record for each reading and dating the records. Then a
query can find the maximum in any given time period,

 

[Bullfrog]

You are doing spreadsheet stuff. How is 3 the peak and not 50?
I recommend using a record for each reading and dating the records. Then a
query can find the maximum in any given time period,

Actually this part of it I do have a spreadsheet for and then I import
it into the databse. The database is actually over 100 fileds, of
which this is just a small part. I am just trying to eliminate the
spreadsheet and do it directly in the database.

The database is based on charted music, that is why 3 is higher then
50.

Bullfrog

 

[Pat Hartman \(MVP\)]

The solution is still - normalize the data. Once you have done that, Access
will be very easy for you to use. As it is now, you are trying to use
spreadsheet functionality in a relational database and there are no
equivalent functions to handle what we call repeating groups. Your
spreadsheet columns need to become rows in a table. If you have 100 columns
in your spreadsheet that constitute a repeating group, you will need 100
append queries to convert it. Once converted, do your data entry in the
database so you don't have to go through the conversion effort in an ongoing
basis.

Usually when "normalizing" a spreadsheet, I just create a single append
query which I modify and run as many times as I need to. For a 100 or more,
I would probably take the time to write a VBA routine to create the queries
on the fly and run them.