Results 1 to 5 of 5

Thread: How to generate a report based on customer number (retrieving data from 3 tables)?

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Dec 2014
    Posts
    303

    How to generate a report based on customer number (retrieving data from 3 tables)?

    How to generate a report based on customer number (retrieving data from 3 tables and show it in a datagridview)?
    Attached Images Attached Images  
    Last edited by VS2013; Jul 23rd, 2022 at 01:00 PM.

  2. #2

    Thread Starter
    Hyperactive Member
    Join Date
    Dec 2014
    Posts
    303

    Re: How to generate a report based on customer number (retrieving data from 3 tables)

    The following code for joining two tables is working fine. But here I need to use 3 Tables. Please support.

    Code:
    "Select ST.CustNo, ST.CustName, Sum(ST.TotalAmt) As Total_Amount, 
                                   IIf(IsNull(TotSol), 0, TotSol) As Amount_Paid, 
                                   Sum(ST.TotalAmt) - IIf(IsNull(TotSol), 0, TotSol) As Balance_Amount
                            From SalesTable ST 
                            Left Join(select RT.CustNo, RT.CustName, sum(RT.AmountReceived) As TotSol 
                                      from ReceiptsTable RT 
                                      Group by RT.CustNo, RT.CustName
                            ) Q on ST.CustNo = Q.CustNo and ST.CustName= Q.CustName
                            GROUP BY ST.CustNo, ST.CustName,  
                                     IIf(IsNull(TotSol), 0, TotSol)"

  3. #3
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    108,838

    Re: How to generate a report based on customer number (retrieving data from 3 tables)

    This question has nothing at all to do with VB.NET. This is a SQL question and thus belongs in the Database Development forum. I have asked the mods to move the thread.

  4. #4
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    108,838

    Re: How to generate a report based on customer number (retrieving data from 3 tables)

    As with all SQL query questions, the steps are simple. Firstly, get it working in the appropriate management app first, before trying to do it in your VB project. Secondly, start with the most basic query and build it up bit by bit until it breaks and then, if you need our help, show us what you've done and explain where you're stuck. Joining multiple tables is no different to joining two tables. Each join is the same as though there were only two tables. Start with a 'SELECT *' on one table. Add a join of a second table. Add a join of a third table. Change the column list as required. Etc, etc.

  5. #5
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,854

    Re: How to generate a report based on customer number (retrieving data from 3 tables)

    I have moved this thread from the VB.Net forum to the Database Development forum

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width