Results 1 to 4 of 4

Joining multiple tables get wrong result

  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 07:42 AM. Reason: Update correct information

  2. #2
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    994
    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 online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    17,492
    Post with link was moderated, I just approved (posting to make sure Andy gets an email notification).
    Paul (wino moderator)
    MS Access MVP
    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, 07:24 AM
  2. Replies: 3
    Last Post: 05-13-2013, 09:16 PM
  3. Joining 2 tables on multiple fields
    By smoothlarryhughes in forum Queries
    Replies: 6
    Last Post: 12-13-2012, 09:40 AM
  4. Access giving wrong result using sql query
    By amlanroy in forum Access
    Replies: 3
    Last Post: 05-10-2012, 09:11 AM
  5. Replies: 1
    Last Post: 12-20-2011, 02: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
  •  
Tech Forums: Microsoft Office Forums