Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2014
    Posts
    1

    Question Unanswered: Join two tables with overlapping data?

    Hi all,

    I'm looking for a bit of help if possible, I've got two tables and am looking at seeing where the overlapping data is and getting the additional colums from that. I'm using MS Access and have experience with PL/SQL however i'm unable to apply that to this scenariou.

    Table export has Surname,Forename,Pnumber and Ptype
    Table dept has Surname,Forename and Department

    The tables each have overlapping data and some data is in export yet not dept and vice versa. I've currently got a sql however it matches the forename and surname of one person and gets the Pnumber, Ptype amd Department instead of matching both surname and forename. So it isn't doing its job correctly and I can't figure out why.

    My current SQL is:

    Code:
     
    
    SELECT export.Forename, export.Surname, export.Pnumber, export.Ptype, dept.Department
    FROM export INNER JOIN dept ON (export.Surname = dept.Surname) AND (export.Forename = dept.Forename)
    I'm pretty sure its a simple thing such as UNIQUE however I have tried with UNION and trying an embedded INNER JOIN however it's still not playing ball and am a tad confused as to why. Any advice/guidance is greatly appreciated.

    -Alexsig

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    its almost certainly a bracketing problem
    Code:
    SELECT export.Forename, export.Surname, export.Pnumber, export.Ptype, dept.Department
    FROM export INNER JOIN dept ON (export.Surname = dept.Surname AND export.Forename = dept.Forename)
    or

    Code:
    SELECT export.Forename, export.Surname, export.Pnumber, export.Ptype, dept.Department
    FROM export INNER JOIN dept ON ((export.Surname = dept.Surname) AND (export.Forename = dept.Forename))
    access does its own thing on JOINS sadly
    I'd rather be riding on the Tiger 800 or the Norton

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
  •