Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query Help (Access 2000)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query Help (Access 2000)

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

  3. #3
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Help (Access 2000)

    Hans,
    Unfortuniately no. There is no pattern

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query Help (Access 2000)

    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","Prefi x=" & Chr(34) & Left([SerialNumber],4) & Chr(34)))

  5. #5
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Help (Access 2000)

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

  6. #6
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Help (Access 2000)

    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

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query Help (Access 2000)

    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.

  8. #8
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Help (Access 2000)

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •