Complicated Query Update

[Alex]

I have a table with a field called FUND (Range from 1 to 10,000) and another
called Class_Type, which is Null. Weekly, the data is deleted and replaced
with new data. The new data is unsorted I want to update the Class_Type
based on the fund #. This is where it gets complicated. The record will
read as shown as below
The first set of records will be updated with AB57 as value in the
Class_Type. When the next record has a fund # that is below the previous
record the Class_Type will be updated to GNA and so on. I have the list of
order of the types it should be.

Fund Class_type
72 AB57
111 AB57
640 AB57
642 AB57
45 GNA
90 GNA
105 GNA
72 JA
94 JA

-Thank you, alex

 

[Jake]

let me see if I understand, you want to update Class_Type only when the falue
of Find is reduced compared to the prior record?

 

[Alex]

Yes Jake, that's exactly what I'm looking for. The Class_Type is null when
the new data is put in. So the first set of records when updated will have a
certain value, the next set of records will have a different value, and so
on. When To distinguish when to update with a different value, the value is
reduced compared to the prior record. The value being reduced compared to
the prior record happens about like 20 times. So have 20 different Class
types.

-Alex

 

[Jake]

I suspect the answer is no by the way you pose your question, but is there
anything else that distinguishes one group from another?

Once the number in Fund reduces how do you know what to put in Class_Type?

Do you have a field that could be used to refer to the order the records
were entered, such as an autonumber or datetime_entered?

Are you comfortable using VBA or would you prefer an SQL-only solution? I
think it can be done in SQL but it may be much simpler in VBA.

Are fund numbers always positive?

Jacob

 

[Alex]

I have the Types in Order, which can be put in a table. Would it be easier
if I named them in alphabetical order A B C D E F? The fund numbers are
always positive.

For example
AB57 1
GNA 2
JA 3

Are you suggesting to have a function created in VB and to use this function
in the update query (field). I know just the basics for VB, basically
manipulating to my needs.