Query Help (Access 2000)

Topic

New Reply

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

Reply | Quote

Replies

Is there any pattern as to when the prefix will have length 3 and when it will have length 4?

Reply | Quote

Hans,
Unfortuniately no. There is no pattern

Reply | Quote

Say that the reference table is named tblReference, and that the serial number is named, well, Serial_Number. The following should work, although for large amount of data it will be slow:

Model: Nz(DLookUp(“Model_num”,”tblReference”,”Prefix=” & Chr(34) & Left([Serial_Number],3) & Chr(34)),DLookUp(“Model_num”,”tblReference”,”Prefix=” & Chr(34) & Left([SerialNumber],4) & Chr(34)))

Reply | Quote

Hans,
Thanks,
I’ll try that when I get back to the office
Scott

Reply | Quote

Hans,
You’re right that is slow. I found a way to convert the all prefix to 4 characters. Is there a way to re-write this to run faster?
Sscott

Reply | Quote

In that case, the expression from my previous reply can be simplified to

Model: DLookUp(“Model_num”,”tblReference”,”Prefix=” & Chr(34) & Left([SerialNumber],4) & Chr(34))

This should be faster, because it involves only one DLookup instead of two. An alternative would be to create a query based on the table with the serial numbers. Add the fields you need, plus a calculated field

First4: Left([SerialNumber],4)

Save this query. Next, create a new query based on this query and the reference table. Join them on First4 and Prefix. Add the fields from the first query and the Model_num field from the reference table.

Reply | Quote

Thanks,
Changing it to 4 helped a lot. I will play with the other query.
scott

Reply | Quote