Results 1 to 6 of 6
  1. #1
    alagrad94 is offline Novice
    Windows Vista Access 2007
    Join Date
    Jul 2011
    Posts
    4

    Complex Lookup Problem

    Ok experts, I have a bit of a complicated puzzle and I’m beginning to think it may be beyond Access’ capabilities, but thought I’d throw it on here and see if any of you could make this work.
    I have 4 components to the problem here:
    1) A column returned from a query containing a 14 digit code, of which, only the first two digits and the 5th digit are relevant to what I’m doing
    2) A table that contains the meaning of the first two digits
    3) A table that contains the meaning of the fifth digits
    4) A table containing images for all possible combinations of the values in the two previous tables
    I need Access to break the 14 digit code down and tell me what it represents. Example:
    Table that Defines the first two digits:
    Column 1 Column 2
    01 Dodge
    02 Chrysler
    03 Ford

    Table that defines the fifth digit:
    Column 1 Column 2
    0 Truck


    1 Sedan
    2 Convertible
    Table that contains images for each combination:
    Column 1 Column 2
    01XX2 Photo of a Dodge Convertible
    Code returned by the query:
    01XX2XXXXXXXXX

    What I need Access to do – read this 14 digit code, figure out that it is a Dodge Convertible by analyzing the first two digits and the 5th digit, and then look up the image that goes with dodge convertible in the third table.
    However, it doesn’t have to translate it to “Dodge Convertible”. I just need the image. So, if it could look at the code, find the “01” and the “3” and look up the image just using those two numbers without identifying it as a “Dodge Convertible” that would be fine as well. The ultimate goal is to get the image based on the code returned.

    I could set the third table up more like this if necessary with the ID representing the first two digits and the FLD representing the 5th digit:

    ID Fld0 Fld1 Fld2
    01 Photo-Dodge Truck Photo-Dodge Sedan Photo-Dodge Convertible
    02 Photo-ChryTruck Photo-ChrySedan Photo-ChrysConv
    03 Photo-FordTruck Photo-FordSedan Photo-FordConv

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    You want to display the image in an Image control on a form? You can use VBA code to dynamically set the Picture property of the control.

    Use string manipulation to extract the reference value.

    The photo table shows a reference value with characters between the make and model codes. Are these characters the same as in the 14-digit code? If so, simply Left([Code],5). Use this as the criteria to select the image record. Or could include in the query that returns the 14-digit code a calculated field that extracts the reference value. Then use this constructed field to join query to the photo table.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    You'd have to have a 5th component as well, the location of the image each combination can be found on your network.

    If you have that this is a relatively easy problem

    Assume you have these tables:

    Code:
    TblTypes    
    TypeID TypeName      
    0      Truck     
    1      Sedan     
    2      Convertible
    
    TblVehicles    
    VehicleID VehicleName      
    01        Dodge     
    02        Chrysler     
    03        Ford
    
    TblImages    
    ImageID VehicleID TypeID ImagePath      
    1      01      0     Path 01XX0     
    2      01      1     Path 01XX1     
    3      01      2     Path 01XX2     
    4      02      0     Path 02XX0     
    5      02      1     Path 02XX1     
    6      02      2     Path 02XX2     
    7      03      0     Path 03XX0     
    8      03      1     Path 03XX1     
    9      03      2     Path 03XX2      
    
    TblIncomingCodes    
    IncomingCode      
    01540123456789     
    01971123456789     
    02341123456789     
    03272123456789


    I made the incoming codes a table just for my ease.

    If you make this query first:

    Code:
    SELECT Left([incomingcode],2) AS VID, Mid([incomingcode],5,1) AS TID, Left([incomingcode],2) & "XX" & Mid([incomingcode],5,1) & "XXXXXXXXX" AS Code
    FROM TblIncomingCodes;
    Name it Qry_Level1

    then create this query
    Code:
    SELECT Qry_Level1.Code, TblImages.ImagePath
    FROM Qry_Level1 LEFT JOIN TblImages ON (Qry_Level1.TID = TblImages.TypeID) AND (Qry_Level1.VID = TblImages.VehicleID);
    It should give you the results you want.

  4. #4
    alagrad94 is offline Novice
    Windows Vista Access 2007
    Join Date
    Jul 2011
    Posts
    4
    rpeare - thanks. I think I might be able to make it work this way. I have the image paths, that's no issue. Let me give this a try and I'll let you know how it turns out.

  5. #5
    alagrad94 is offline Novice
    Windows Vista Access 2007
    Join Date
    Jul 2011
    Posts
    4
    rpeare - thanks this set me on the right track and I got the whole thing to work. I ended up selecting the first 2 digits and the 5th digit as you described and concatenating them into a 3 digit code. I then made a table with those codes in field 1, and made field 2 an attachment field and actually loaded the images into the table as opposed to referencing a path. Works like a charm, thanks for the help.

  6. #6
    alagrad94 is offline Novice
    Windows Vista Access 2007
    Join Date
    Jul 2011
    Posts
    4
    Oh, just for your future reference, the way I ended up doing it made it a much simpler than how you laid it out. I didn't need the multiple tables, just the one with the 3 digits codes and images. Here's what I did:

    TblThreeDigCodes
    ThreeDigCode. Image
    123. Attached Image
    234. Attached Image
    Etc....

    Query (based on the original query that returns the 14 digit code)

    SELECT Left ([Query].[14DigCode],2) & Mid([Query].[14DigCode],5,1) AS ThreeDigCode

    Then I was able to base a report on this query that return the image associated to the 3-digit code returned.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Complex query to me anyway
    By AndycompanyZ in forum Queries
    Replies: 3
    Last Post: 06-28-2011, 03:08 PM
  2. Help with complex Query
    By CEV in forum Queries
    Replies: 1
    Last Post: 03-12-2011, 06:54 AM
  3. Complex Table "Lookup" Lists
    By Rawb in forum Access
    Replies: 19
    Last Post: 03-01-2011, 02:52 PM
  4. Complex Joins??
    By objNoob in forum Queries
    Replies: 0
    Last Post: 03-16-2010, 02:42 PM
  5. Complex Lookup?
    By murphyB in forum Database Design
    Replies: 1
    Last Post: 09-17-2009, 12:43 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums