Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    therzakid is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2011
    Posts
    22

    Left join problem

    Hi folks,



    Thanks for taking a look at this thread. My problem is simple: I'm having problems doing a left join with 3 fields. I know I don't need the 3 fields for this example to align the data but, I deal with much more than this and need this functionality. If you could please evaluate the db I have and identify what it is I'm doing wrong I'd be very grateful. Spent hours so far but no luck on my own.

    The query object I'm looking into is named 'left join' and the sql is as follows:
    SELECT Act_Base.CustID, Act_Base.Chipset_ID, Act_Base.Product, Act_Base.Qtr, Act_Base.Qty AS Shipments, SE_Base.Qty AS SE
    FROM Act_Base LEFT JOIN SE_Base ON (Act_Base.Product = SE_Base.Product) AND (Act_Base.Qtr = SE_Base.Qtr) AND (Act_Base.CustID = SE_Base.CustID);

    I'm expecting to see 150 units in the SE.Qty field.

    http://www.datafilehost.com/d/f578590f

    Thanks again for your time.

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Did Not look at your DB but perhaps the OR operator vs. the AND operator is preferable.

  3. #3
    trevor40's Avatar
    trevor40 is offline Advanced db Manager
    Windows XP Access 2003
    Join Date
    Feb 2014
    Location
    Australia
    Posts
    402
    Coming from an electronics background how can one be better than the other as they are boolean logic?
    The same applies to query logic yes? OR am I wrong!

    'this AND that' is different to 'this OR that'

    = both or one OR the other

  4. #4
    therzakid is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2011
    Posts
    22
    Right, I need all 3 criteria met. That's to say, I want to pair the values from the two tables based on a combiation of customer/product/qtr. Can you please look at the db since the syntax seems alright. There's something very subtle that's throwing it.

  5. #5
    trevor40's Avatar
    trevor40 is offline Advanced db Manager
    Windows XP Access 2003
    Join Date
    Feb 2014
    Location
    Australia
    Posts
    402
    Sorry but I've never needed to debug queires in this way, but if you used the query grid to build it try checking the table join fields. Check the join type under the join properties.
    check by showing only records from each table by It's self, check results.
    try 2 select queries and then join them on a third query.
    Best i can offer at the moment, I was more concerned at the logic statment above throwing you off.

  6. #6
    therzakid is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2011
    Posts
    22
    Yea, I've tried that too but initially just used sql. I'm at a loss, I need someone to take the 5 minutes to have a look. Quite frustrated with this seemingly easy task.

  7. #7
    trevor40's Avatar
    trevor40 is offline Advanced db Manager
    Windows XP Access 2003
    Join Date
    Feb 2014
    Location
    Australia
    Posts
    402
    A sample for you, these were created a long time ago I only use the grid to create queries, you may find these helpfull.

    SELECT gamrep.[Customer Name], gamrep.Out, gamrep.[despatch date], gamrep.[Part Status 2], gamrep.[Quickbooks Invoice number], [Customer Names].[Billing Activated], gamrep.[Part #], [Part Number Reference].Description, gamrep.[Tag #], gamrep.[Serial #], [Customer Names].[Invoice Option]
    FROM [Part Number Reference] RIGHT JOIN ([Customer Names] RIGHT JOIN gamrep ON [Customer Names].[Customer Name] = gamrep.[Customer Name]) ON [Part Number Reference].[Part #] = gamrep.[Part #]
    WHERE (((gamrep.Out)>#1/1/2008#) AND ((gamrep.[despatch date]) Is Not Null) AND ((gamrep.[Part Status 2])="Not Repaired" Or (gamrep.[Part Status 2])="completed" Or (gamrep.[Part Status 2])="under warranty" Or (gamrep.[Part Status 2])="nff" Or (gamrep.[Part Status 2])="Completed, No Fault Found") AND ((gamrep.[Quickbooks Invoice number]) Is Null) AND (([Customer Names].[Billing Activated])=True) AND (([Customer Names].[Invoice Option])="3"))
    WITH OWNERACCESS OPTION;

    And this one

    SELECT GAMREP.*, [Part Number Reference].Description, [Customer Names].[Customer Name full]
    FROM [Customer Names] RIGHT JOIN (GAMREP LEFT JOIN [Part Number Reference] ON GAMREP.[Part #] = [Part Number Reference].[Part #]) ON [Customer Names].[Customer Name] = GAMREP.[Customer Name]
    WHERE (((GAMREP.[Rep By])=[Enter Initials]) AND ((GAMREP.[Part Status 2])="Received for Repair" Or (GAMREP.[Part Status 2])="waiting parts" Or (GAMREP.[Part Status 2])="Quarantine waiting for assesment" Or (GAMREP.[Part Status 2])="external vendor repair"));


  8. #8
    therzakid is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2011
    Posts
    22
    Thanks for trying to help but i need someone to look at my database, not post sequel syntax from one of theirs

  9. #9
    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
    Please post a jpg of your tables and relationships.

  10. #10
    therzakid is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2011
    Posts
    22
    Pictures per your request:
    Act_Base (query 1): http://postimg.org/image/mcmsnsjhf/
    SE_Base (query 2): http://postimg.org/image/3ygta04mn/
    Left Join query: http://postimg.org/image/qxxqtryjt/

  11. #11
    therzakid is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2011
    Posts
    22
    Okay, I got it working but don't know why when I left join 1 query I don't
    need an nz handler on product but I do when I left join the other query. Please
    help me understand this:



    ****This left join works just fine as expected, no NZ handler needed****

    ************************************************** *****

    SELECT Act_Base.CustID, Act_Base.Chipset_ID, Act_Base.Product, Act_Base.Qtr,
    Act_Base.Qty AS Shipments, SE_Base.Qty AS SE
    FROM Act_Base LEFT JOIN SE_Base
    ON (Act_Base.Product = SE_Base.Product) AND (Act_Base.Qtr = SE_Base.Qtr) AND
    (Act_Base.CustID = SE_Base.CustID);








    ***requires left NZ handler to work but there's no null values for product
    ***in either source table****

    ***********************

    SELECT SE_Base.CustID, SE_Base.Chipset_ID, SE_Base.Product, SE_Base.Qtr,
    Act_Base.Qty AS Shipments, SE_Base.Qty AS SE
    FROM SE_Base LEFT JOIN Act_Base
    ON (SE_Base.CustID = Act_Base.CustID) AND (SE_Base.Qtr = Act_Base.Qtr) AND
    (NZ(SE_Base.Product,"") = NZ(Act_Base.Product,""));





    I'd reallllllly like to understand what is wrong so I can avoid this problem
    in the future. Thanks.

  12. #12
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    This
    FROM Act_Base LEFT JOIN SE_Base
    Is retrieving all records from table Act_Base and only those from SE_Base where a matching record is found.

    This
    FROM SE_Base LEFT JOIN Act_Base
    Is retrieving all records from table SE_Base and only those from Act_Base where a matching record is found.

    The NZ function may not be doing anything.

  13. #13
    therzakid is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2011
    Posts
    22
    But it is though. When I omit the NZ then joined table's quantity values are all null. When I do use the NZ handling then the joined table's values populate as expected. I want to know why when I just use the NZ handling for just the product field my records correctly populate. How do I fix my underlying queries/tables integrity to work like the first join example that doesn't need NZ used?

  14. #14
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    I do not see a calculation function in the SQL but NZ will be needed to perform correct calculations. You can not include Null fields in a calculation process/function.

  15. #15
    therzakid is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2011
    Posts
    22
    Can you please elaborate on what you mean with my problem as context?

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Inner Join works but not Left Join
    By kwooten in forum Queries
    Replies: 1
    Last Post: 12-11-2012, 05:09 PM
  2. Replies: 4
    Last Post: 09-03-2012, 04:53 PM
  3. Replies: 3
    Last Post: 02-02-2011, 01:00 PM
  4. Left join problem
    By e.badin in forum Queries
    Replies: 5
    Last Post: 01-17-2011, 08:03 AM
  5. Problem with Left Join & VB6
    By msrdjan1 in forum Queries
    Replies: 0
    Last Post: 03-30-2010, 01:48 AM

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