Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2011
    Posts
    33

    Unanswered: Help with a Dlookup

    Hi all,

    Hopefully I can explain this well enough for it to make sense.

    I have set up a database for a project I'm working on, I have a table "tblMain" which contains details of about 600 locations around australia - there are two different types of properties in each town, so I have a field called "SiteType" which identifies what each site is, either: "TypeOne" or "TypeTwo". Each location has a unique 3 or 4 character id which is the primary key.

    What I'm trying to do is set up a mail merge in word which would allow me to populate it with some of the data in tblmain (lease expiry, address, etc). I'm fine with setting it up with type 1 data but i need help on how to put type 2 data in also.

    In "tblMain" I have two fields called "NearestLocation" & "NearestLocationId" (so if I'm looking at a type 1 record it will give me the name and id of the closest type 2 site).

    I'm trying to use the NearestLocationId to find the data that relates to that Id, if that makes sense. So I have a table that looks like this:

    Code:
    ID               SiteType               NearestLocationID             LeaseExpiry 
    AAAA            Type 1                        ABC                         01/06/12
    ABC              Type 2                       AAAA                        20/12/12
    So when I do the mail merge it populates all the fields with the type 1 data (or type 2 if I generate the mail merge using the type 2 filter). What I'd like it to is use the NearestLocationID (ABC) to lookup the ID of type 2 (ABC) and return the lease expiry for the type 2 record (20/12/12). Is that possible?

    I've tried to use a Dlookup,
    =Dlookup("[LeaseExpiry]","tblMain","[NearestLocationId]") - however this only returns one result for all of my records. I'm trying this in a textbox.

    Is someone able to tell me what I'm doing wrong, is a Dlookup the way to go for what I'm trying to do? Or do I need to create a separate table for my typetwo data?

    Thanks for taking the time to read this

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    You would be better served by joining the table to itself in a query, and using the query as the source for your mail merge:
    Code:
    SELECT
      a.ID
    , a.SiteType
    , a.NearestLocationID
    , a.LeaseExpiry
    , b.SiteType AS NearestLocationType
    , b.LeaseExpiry AS NearestLocationExpiry
    FROM
    tblMain AS a
    INNER JOIN
    tblMain AS b
    ON
    a.NearestLocationID = b.ID
    This will give you all information about a location and its nearest neighbour on one line.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  3. #3
    Join Date
    Sep 2011
    Posts
    33
    Weejas,

    YOU ARE MY HERO!!!

    Thanks so much!

  4. #4
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    You're welcome!
    10% of magic is knowing something that no-one else does. The rest is misdirection.

Posting Permissions

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