Help with query

[ready4data]

I have a table with serial numbers. EG.
H5405662350
H1250804454
A3514558452

Then I have a reference table that has 2 fields:
Model_Number
Prefix

model x H540
model y A351

The prefix has the first 3 or 4 characters of the serial number field.
I'm trying to create a query that will show the serial number and the model number. I think I need to do a sub-query and have been playing with that, but I can't get the results I want.

Can anyone help?
Thanks

 

[jfgambit]

When does it have three and when does it have 4 numbers? Is it when the Serial begins with A, X, etc?? Or is it random?

 

[ready4data]

The pattern is random. I am however, trying to change them all to 4 digits

 

[namliam]

Look at attached file, i think it'll do what you want it to (maybe after a little tweaking)

Might have been better if you had taken the (little extra) time to post a sample DB with your question.

Regards

The Mailman

 

[ready4data]

Thanks, With a little tweak that worked.

thanks,
Scott