Results 1 to 4 of 4
  1. #1
    Tony Thi is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2015
    Posts
    2

    Joining multiple tables get wrong result

    I am newbie. I have 4 different tables that I want to join, here's the details:

    1) Table: T_CODE
    Item_Code
    A
    B
    C
    D

    2) Table: T_MONTHLY_STOCK
    Item_Code Qty Month
    A 1000 201509
    B 2000 201509
    C 3000 201509

    3) Table: T_SALES
    Item_Code Qty Week
    A 5 1

    B 4 1
    A 50 2

    B 40 2
    4) Table: T_RECEIPT
    A 1 1
    B 2 1

    A 10 2
    B 20 2

    I would like to make a Query to get results as following:
    Item_Code Stock In_Week_1 Out_Week_1 Balance_Week_1


    A 1000 1 5 996
    B 2000 2 4 1998
    C 3000 0 0 3000
    D 0 0 0 0

    I have tried to make a query but I got the wrong result.

    Item_Code Stock In_Week_1 Out_Week_1 Balance_Week_1
    A 1000 210 992
    B 2000 481996
    C 3000 0 0 3000
    D 0 0 0 0


    my query as following:

    SELECT T_CODE.Item_Code
    , IIf(IsNull([T_MONTHLY_STOCK]![Item_code]),0,[T_MONTHLY_STOCK]![Qty]) AS Stock
    , Sum(IIf([T_RECEIPT]![Week]=1,[T_RECEIPT]![Qty],0)) AS In_Week_1
    , Sum(IIf([T_SALES]![Week]=1,[T_SALES]![Qty],0)) AS Out_Week_1
    , IIf(IsNull([T_CODE]![Item_Code]),0,[T_MONTHLY_STOCK]![Qty])
    +Sum(IIf([T_RECEIPT]![Week]=1,[T_RECEIPT]![Qty],0))
    -Sum(IIf([T_SALES]![Week]=1,[T_SALES]![Qty],0)) AS Balance_Week_1
    FROM
    ((T_CODE LEFT JOIN T_MONTHLY_STOCK ON T_CODE.Item_Code = T_MONTHLY_STOCK.Item_code)
    LEFT JOIN T_SALES ON T_CODE.Item_Code = T_SALES.Item_code)
    LEFT JOIN T_RECEIPT ON T_CODE.Item_Code = T_RECEIPT.Item_code


    GROUP BY
    T_CODE.Item_Code
    , IIf(IsNull([T_CODE]![Item_Code]),0,[T_MONTHLY_STOCK]![Qty]);

    Can anyone encountered this issue and knows what the fix is?
    Thanks in advance!
    Last edited by Tony Thi; 10-16-2015 at 08:42 AM. Reason: Update correct information

  2. #2
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    Please upload a sample database and give instructions on how to get the error you are getting. I cant workout how to help you from the information here.

  3. #3
    Tony Thi is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2015
    Posts
    2
    Hi Homegrownandy!
    Please kindly see following link for the sample database: http://www.mediafire.com/download/s65fe7...base.accdb
    I would like to repair the wrong result from the query. It made doubles.
    Thanks in advance!

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Post with link was moderated, I just approved (posting to make sure Andy gets an email notification).
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Multiple criteria query joining two tables
    By Carnior in forum Queries
    Replies: 15
    Last Post: 05-01-2015, 08:24 AM
  2. Replies: 3
    Last Post: 05-13-2013, 10:16 PM
  3. Joining 2 tables on multiple fields
    By smoothlarryhughes in forum Queries
    Replies: 6
    Last Post: 12-13-2012, 10:40 AM
  4. Access giving wrong result using sql query
    By amlanroy in forum Access
    Replies: 3
    Last Post: 05-10-2012, 10:11 AM
  5. Replies: 1
    Last Post: 12-20-2011, 03:32 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