Function to help update a Field based on previous record

[Alex]

I am trying to update a field, Class_Type. The update will be based on a
previous record different field, fund. Here is an example shown below. If
you notice the Class_type will be different if the fund value is smaller or
equal to the previous record's fund value. I have a table called Class_Type
with the list of classes and which one goes first, second, third, etc. I
would think a function would be needed to create this, but not sure where to
start.

Fund Class_Type
10 AA
15 AA
20 AA
19 PA
27 PA
10 GH
20 GH
30 GH

-Thanks, alex

 

[John W. Vinson]

I am trying to update a field, Class_Type. The update will be based on a
previous record different field, fund. Here is an example shown below. If
you notice the Class_type will be different if the fund value is smaller or
equal to the previous record's fund value. I have a table called Class_Type
with the list of classes and which one goes first, second, third, etc. I
would think a function would be needed to create this, but not sure where to
start.

Fund Class_Type
10 AA
15 AA
20 AA
19 PA
27 PA
10 GH
20 GH
30 GH

-Thanks, alex

You're thinking spreadsheet. Access (and any relational) tables *have no
order*; there is no "prior record" or "next record" - they are unordered
"bags" of data.

Do you have some other field - an incrementing autonumber, a timestamp, or
something - that will let you specify the order of records? If not, there is
no way to do what you describe.

 

[Alex]

I can create a new field that is an autonumber that will specify the order of
the records. So when I paste the data in each record will be assigned an
autonumber. So by creating an autonumber I can now distinguish one record
from the other? and a function created?

 

[John W. Vinson]

I can create a new field that is an autonumber that will specify the order of
the records. So when I paste the data in each record will be assigned an
autonumber. So by creating an autonumber I can now distinguish one record
from the other? and a function created?

Well, you can - but you might better be doing this in Excel (which can be
linked to a data store in Access if that helps).

Could you explain the real-life problem you're trying to solve? What is the
meaning of these fields? There may be another data model for the problem which
would make life easier for you.

 

[Alex]

Well there is a file that is exported from a system in an excel format. The
data though comes in one column (which text to columns can be used to fix).
It looks like this. Now there is a lot of data (4000 rows) with about 25
different headings. My colleagues go through this excel file and do
calculations based on what fund# and what headings they are. The one below
is Gross_all_dlrs. As you can see the heading comes out in different cells,
and when imported into the database there's no way to distinguish what type
they are. Their manual process takes about 3-4 hours to do and their Monarc
application is not able to strip the data properly. I decided to take a look
at the data and initially labled each one what type they are based on the
heading and then imported into Access. That proccess takes about 10-15
minutes, however they don't want to do that and rather have it imported into
Access directly. Because I'm not a programmer I was trying to figure a way
around it. Since I know the order of the headings are (never change and will
always have one row of data) I was thinking about a function where it will
assign the type based on the previous record (since the fund # is less than
the previous it is safe to assume this is a new type). Sorry for the late
response, was out sick yesterday. Thank you for your patience.

5/12/2008 20080 505 THRU 20080509 PAGE 1
MAINSTAY COMMIS SIONS GROSS TRADES-ALL DLRS
CLASS A

FUND GROSS DLRCOMM FRONT-END UWRITER ADV-COMM

72 170.00- 0 0 0 0
111 48,343.90 740 456.38 73.18 283.62

5/12/2008 20080 505 THRU 20080509 PAGE 4
MAINSTAY GR OSS TRADES- DLRS 70000
CLASS I

FUND GROSS DLRCOMM FRONT-END UWRITER ADV-COMM

101 768,551.05 0 0 0 0