Function to help update a Field based on previous record

  • Thread starter Thread starter Alex
  • Start date Start date
A

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
 
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
Click to expand...

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.
 
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?
 
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?
Click to expand...

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.
 
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
 
Back
Top