Results 1 to 5 of 5
  1. #1
    mikkip is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2012
    Posts
    3

    Newbie question: Using Dlookup to cross reference between two tables

    Hello--I'm new to access and need to modify an existing database to get a report I want to create. I'm an Excel user and could accomplish what I want to do in seconds using Excel's Lookup functions, so I'm trying to do the same with Access Dlookup but struggling.

    I have two tables in my database that tracks components in a hardware product. The first table is just a list of hardware units shipped. The second table is a list of various components (and versions thereof) used in each unit. The two tables are linked by a UNIT_ID field. I need to create a report showing each unit and what the various versions of the components are. My difficulty is that the component table is does not have a dedicated field for each component and each unit can be comprised of a different quantity of components. So the tables look something like this:

    UNITS TABLE:
    ID Customer
    1 Joe
    2 Bob
    3 Bill


    COMPONENTS TABLE:
    ID Component Revision


    1 Main Board A
    1 Blue Board B
    2 Main Board B
    2 Blue Board B
    3 Main Board A
    3 Red Board C

    I want to produce a report that looks like
    ID Customer Main Board Blue Board Red Board
    1 Joe A B --
    2 Bob B B --
    3 Bill A -- C

    I setup queries to get revisions of each Board and am trying to compile into the full report, but can't figure it out. Right now I'm trying to use Dlookup to lookup data from one of the queries and insert into a larger table. I can lookup a single value fine, but can't figure out how to get Dlookup to give me the Blue Board & Red Board values for each of the system ID numbers in a master query (which would be a snap in Excel).

    I hope this makes sense. Any help is appreciated.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    51,499
    Did you try joining the two tables in a query then use that query in a crosstab query?
    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
    mikkip is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2012
    Posts
    3
    Yes, I tried that. The problem I couldn't get around in that situation is that with the design of the database, I don't have a unique field for each board revision. That is, there's not a separate field for Red Board revision and Blue Board revision, there's just Component and Revision. So somehow I have first do a query on ID and Red Board or Blue Board, then I can easily lookup the proper revision.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    51,499
    I assume each unit will not have multiple 'Blue' or 'Red' or 'Main' components, at most one of each? I built two tables as you describe with data literally as you show. This query produces the example output:

    TRANSFORM First(Components.Revision) AS FirstOfRevision
    SELECT Components.UnitID, Units.Customer
    FROM Units LEFT JOIN Components ON Units.ID = Components.UnitID
    GROUP BY Components.UnitID, Units.Customer
    PIVOT Components.Component;
    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.

  5. #5
    mikkip is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2012
    Posts
    3
    You're my hero. That worked great, and I was even able to translate into my actual database. Thanks!

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

Similar Threads

  1. Newbie. How do I use DLookup with a variable please?
    By graffiti in forum Programming
    Replies: 2
    Last Post: 12-12-2011, 08:59 AM
  2. Cross reference
    By Anne in forum Access
    Replies: 9
    Last Post: 09-23-2011, 08:01 AM
  3. Form Design Question with DLookup (newbie)
    By wongc61 in forum Access
    Replies: 4
    Last Post: 07-08-2011, 03:22 PM
  4. Replies: 1
    Last Post: 06-18-2011, 10:00 AM
  5. How to reference data without using Dlookup()
    By HunterEngineeringCoop in forum Programming
    Replies: 7
    Last Post: 12-01-2010, 09:22 AM

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