Auto Field

[allison]

I have a field called CAN common account number in a make
table query that changes for each transaction. I would
like to AUTOFIELD 4 new fields based on the last 4 digits
of the CAN field. Example:

CAN: RESEARCH DIVISON POOL NOTES

0944 BIOD DMID SREA Research Projects

I have 150 CAN's and each has a different Research,
Division, Pool and Notes also I have over 6500
transactions. Currently my table only contains the CAN #
in it original state (example: 8460944. Someone advised I
should write a Visual Basic event procedure that fills in
other fields automaticaly.
Example:

Private Sub CAN_AFTERUPDate
Is there another way to accomplish this??? I need your
step by step help on this!

Thanks,

 

[Allison]

Is there a solution to the situaton below?

 

[John Vinson]

Is there a solution to the situaton below?

Yes, there's a solution. Use Access as it's designed - as a relational
database!

If you can get these four fields from the CAN, then you should NOT
store them in this table, *period*. It's simply WRONG.

Store the CAN in your field, preferably split into its components
using Left([CAN], 3) to get the 846, and Right([CAN], 4) to get the
0944 portion.

Create a Query joining this transactions table to your table of 150
CAN's; include the four fields from this latter table in your query.
It is neither necessary nor beneficial to store them redundantly in
your Transactions table.