Alter Alpha-Numeric field data to numeric

[Haybails90]

Hey folks,

Just aquired a company and we've got some data of theirs in an MS Access table. The ID is a text field containing Alpha/Numeric data which really follows now concrete formula (some are two alpha followed by two numeric, some are four alpha followed by one numeric, etc.) All we know is that they are ordered accordingly. For example:
Acct_id
AE002
AE003
AE004
AE005
BA1
BA2
CASH02
CASH03
etc., etc., etc.

Well, we need to transfer this data over to our Gemini system which has an ID field which is fixed at 6 digit strictly numeric. I play around with Access at home - so I could follow along with your assistance I'm sure, but I'm not really an Access user. So, any elementary advice on how to make such a change? Thanx,


Haybails90

 

[GingerR]

what will be the 6-digit results? will they be coordinated in any way with the 'original' numbers? or just starting at 000001 and going up?

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SkipVought]

Hi,

Build a cross reference table with the OLD ID and the NEW ID.

Reference the cross reference as a lookup.

Skip,
[sub]
[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?" [/sub]

 

[GingerR]

Yeah...that's what I was gonna say next )

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244