Results 1 to 7 of 7
  1. #1
    smoothlarryhughes is offline Advanced Beginner
    Windows 7 64bit Access 2003
    Join Date
    Sep 2012
    Posts
    36

    Joining 2 tables on multiple fields

    I have 2 sets of data...one from an accounting system...one from the bank. Can someone take a look at my database and let me know if the union query is the best way to compare the 2 sets of data. I have 3 fields that need to be the same to then compare the amounts...all on the same line. Is there any other way besides joining the tables on cusip/tran type/familiar code, or am i doing this right?



    reconcile.accdb

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Looking at your database.

    You will save yourself a lot of grief/pain and syntax errors if you use a naming convention that uses only alphanumeric and "_" underscore character. No Spaces, No special characters.

    Union query is not for comparing datasets.

    I think Join on the 3 fields is best.

    Records in Ledger Not in Statement
    Code:
    SELECT tblLEDGER.ID
    , tblLEDGER.CUSIP
    , tblLEDGER.TRANTYPE
    , tblLEDGER.AMOUNT
    , tblLEDGER.[L/S]
    , tblLEDGER.[Familiar Code]
    , tblSTATEMENT.[Familiar Code]
    , tblSTATEMENT.TRANTYPE
    FROM tblLEDGER LEFT JOIN tblSTATEMENT ON
     (tblLEDGER.[CUSIP] = tblSTATEMENT.[CUSIP]) AND
     (tblLEDGER.TRANTYPE = tblSTATEMENT.TRANTYPE) AND
     (tblLEDGER.[Familiar Code] = tblSTATEMENT.[Familiar Code])
    WHERE
     (((tblSTATEMENT.CUSIP) Is Null) AND
     ((tblSTATEMENT.[Familiar Code]) Is Null) AND 
    ((tblSTATEMENT.TRANTYPE) Is Null));
    Records in Statement not in Ledger

    Code:
    SELECT tblSTATEMENT.ID
    , tblSTATEMENT.CUSIP
    , tblSTATEMENT.TRANTYPE
    , tblSTATEMENT.AMOUNT
    , tblSTATEMENT.[L/S]
    , tblSTATEMENT.[Familiar Code]
    , tblLEDGER.[Familiar Code]
    FROM tblSTATEMENT LEFT JOIN tblLEDGER ON 
    (tblSTATEMENT.[Familiar Code] = tblLEDGER.[Familiar Code]) AND
     (tblSTATEMENT.TRANTYPE = tblLEDGER.TRANTYPE) AND 
    (tblSTATEMENT.CUSIP = tblLEDGER.CUSIP)
    WHERE
     (((tblLEDGER.CUSIP) Is Null) AND 
    ((tblLEDGER.TRANTYPE) Is Null) AND
     ((tblLEDGER.[Familiar Code]) Is Null));
    I hope these help you find what you're looking for [untested].
    Last edited by orange; 12-12-2012 at 06:56 PM. Reason: adjusted the sql format

  3. #3
    smoothlarryhughes is offline Advanced Beginner
    Windows 7 64bit Access 2003
    Join Date
    Sep 2012
    Posts
    36
    Quote Originally Posted by orange View Post
    Looking at your database.

    You will save yourself a lot of grief/pain and syntax errors if you use a naming convention that uses only alphanumeric and "_" underscore character. No Spaces, No special characters.

    Union query is not for comparing datasets.
    I will remove spaces from everything. I've attached a .mbd.

    reconcile.mdb

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  5. #5
    smoothlarryhughes is offline Advanced Beginner
    Windows 7 64bit Access 2003
    Join Date
    Sep 2012
    Posts
    36
    Those queries didn't seem to work. I need to see all data from both tables and put like data in the same line. My union query works, but I was wondering if this is the only way to get the result that the query is giving.

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    When you say compare - I think of finding the exceptions -- ie What's in A that isn't in B and B not in A.

    If the union query gives you what you need -- great.

    Can you post the result you have, since it's the result you want?

  7. #7
    smoothlarryhughes is offline Advanced Beginner
    Windows 7 64bit Access 2003
    Join Date
    Sep 2012
    Posts
    36
    I basically needed a full outer join for both tables comparing on those 3 fields, which is why i originally used the union between the 2 left joins. I was just wondering if this was the best way to go about it.

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

Similar Threads

  1. Joining two tables
    By rspai in forum Queries
    Replies: 7
    Last Post: 05-22-2012, 12:21 AM
  2. Joining 2-Tables in a Query
    By djclntn in forum Queries
    Replies: 25
    Last Post: 01-13-2012, 12:54 PM
  3. joining tables
    By frozendmj in forum Queries
    Replies: 5
    Last Post: 04-19-2011, 01:45 PM
  4. Joining more than 2 tables
    By anemoskkk in forum Access
    Replies: 6
    Last Post: 04-17-2011, 04:37 AM
  5. Help - Joining Tables..
    By fabian24 in forum Forms
    Replies: 3
    Last Post: 02-17-2011, 03:43 PM

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