Results 1 to 14 of 14

Thread: [RESOLVED] Need a query to show data of a customer account in a report form...

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Dec 2014
    Posts
    326

    Resolved [RESOLVED] Need a query to show data of a customer account in a report form...

    Hi,

    I have two tables:
    1. SALESTABLE
    2. RECEIPTSTABLE

    I am now retrieving data of the above tables in separate datagridviews using the below code which is working fine.

    Code:
        Private Sub LoadSales()
            Dim StrSql As String = "Select CUSTNO, CUSTNAME, INVNO, INVDATE, Sum(INVAMT) As INVAMT from SALESTABLE Where [CUSTNO] LIKE '" & txtSearchCustNo.Text & "' Group By CUSTNO, CUSTNAME, INVNO, INVDATE Order By CUSTNO ASC"
            Dim dt As New DataTable
            If Not DAL.ExecuteSql(dt, StrSql, Msg) Then MsgBox(Msg, MsgBoxStyle.Exclamation, "Error") : Exit Sub
            Me.dgvSales.DataSource = dt
            If dt Is Nothing Or dt.Rows.Count <= 0 Then
                Exit Sub
            End If
        End Sub
    Code:
    Private Sub LoadReceipts()
            Dim StrSql As String = "Select CUSTNO, CUSTNAME, RECEIPTNO, RECEIPTDATE, RECEIVEDAMT from RECEIPTSTABLE Where [CUSTNO] LIKE '" & txtSearchCustNo.Text & "' Order By RECEIPTNO ASC"
            Dim dt As New DataTable
            If Not DAL.ExecuteSql(dt, StrSql, Msg) Then MsgBox(Msg, MsgBoxStyle.Exclamation, "Error") : Exit Sub
            Me.dgvReceipts.DataSource = dt
            If dt Is Nothing Or dt.Rows.Count <= 0 Then
                Exit Sub
            End If
        End Sub
    But I need a query that fetches both tables' data in one datagridview with one extra virtual column "BALANCE" as shown in the screenshots (attached).

    Looking forward to your kind support.
    Attached Images Attached Images   
    Last edited by VS2013; Nov 28th, 2023 at 10:44 PM.

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,230

    Re: Need a query to show data of a customer account in a report form...

    If your question is about SQL then it really shouldn't involve any VB code at this stage. You should be getting your queries right in the database first, then simply copying the SQL into your application code.

    You should forget the extra column for the moment and concentrate on the existing data. Can you combine that as you want? It looks like all you need is a UNION and then sort the combined data by date. Do you know how to do that? If not, that's what you should look into for yourself first and only think about the next step once you can do that. Trying to solve multiple problems as though they were one is a great way to not solve any of them.

  3. #3

    Thread Starter
    Hyperactive Member
    Join Date
    Dec 2014
    Posts
    326

    Re: Need a query to show data of a customer account in a report form...

    Quote Originally Posted by jmcilhinney View Post
    If your question is about SQL then it really shouldn't involve any VB code at this stage. You should be getting your queries right in the database first, then simply copying the SQL into your application code.

    You should forget the extra column for the moment and concentrate on the existing data. Can you combine that as you want? It looks like all you need is a UNION and then sort the combined data by date. Do you know how to do that? If not, that's what you should look into for yourself first and only think about the next step once you can do that. Trying to solve multiple problems as though they were one is a great way to not solve any of them.
    As per your kind advice, I tried the following code which is working fine. But don't know how to get a virtual column to show the balance amount.

    Code:
    "Select CUSTNO, CUSTNAME, INVNO, INVDATE, SUM(INVAMT) from SALESTABLE Where [CUSTNO] LIKE '" & txtSearchCustNo.Text & "' Group By CUSTNO, CUSTNAME, INVNO, INVDATE
            UNION
            Select CUSTNO, CUSTNAME, RECEIPTNO, RECEIPTDATE, RECEIVEDAMT from RECEIPTSTABLE Where [CUSTNO] LIKE '" & txtSearchCustNo.Text & "'"

  4. #4
    Lively Member
    Join Date
    Dec 2021
    Posts
    100

    Re: Need a query to show data of a customer account in a report form...

    With query results, you'll get all columns in DataGridView if you re-set up the data source. In this case, you need a query that works and then see how DGV shows data.

    BTW, that is the column just like any other column. I don't see why you call it virtual. They are all created and populated once the code asks for query results.

    You can also prepare columns in the code. You are preparing each column's property if you know what data to expect for each.

    Sample:
    Code:
    With DataGridView1
                .RowHeadersVisible = True
                .Columns(0).HeaderCell.Value = "CUSTNO"
                .Columns(1).HeaderCell.Value = "CUSTNAME"
                .Columns(2).HeaderCell.Value = "TRNO"
                .Columns(3).HeaderCell.Value = "TRDATE"
                .Columns(4).HeaderCell.Value = "TRAMT"
                .Columns(5).HeaderCell.Value = "BALANCE"
                .Columns(0).Width = 40
                .Columns(2).Width = 100
                .Columns(3).Width = 100
                .Columns(4).Width = 60
                .Columns(5).Width = 60
                .Columns(0).ReadOnly = True
                .Columns(1).ReadOnly = True
                .Columns(2).ReadOnly = True
                .Columns(3).DefaultCellStyle.BackColor = Color.AliceBlue
                .Columns(4).DefaultCellStyle.BackColor = Color.AliceBlue
                .Columns(5).DefaultCellStyle.BackColor = Color.AliceBlue
    
            End With
    EDIT:

    If you want to manipulate data directly in DGV, this is a sample code.

    Code:
    Private Sub DataGridView1_CellLeave(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellLeave
            Dim column3 As Double = 0
            Dim column4 As Double = 0
            Dim balance As Double = 0
           
            For i = 0 To DataGridView1.RowCount - 1
                column3 += DataGridView1.Rows(i).Cells(3).Value()
                column4 += DataGridView1.Rows(i).Cells(4).Value()
                balance = DataGridView1.CurrentRow.Cells(3).Value * DataGridView1.CurrentRow.Cells(4).Value
             
                DataGridView1.CurrentRow.Cells(7).Value = balance
               
    
            Next
     End Sub
    Note: this code executes on cell leave event.
    Last edited by ivansmo; Nov 30th, 2023 at 04:22 PM.

  5. #5
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    11,672

    Re: Need a query to show data of a customer account in a report form...

    You can use a SUM OVER with the PARTION BY clause on the CUSTNO, e.g.:
    Code:
    SELECT
        CUSTNO
        , CUSTNAME
        , TRNO
        , TRDATE
        , TRAMT
        , SUM(CASE WHEN TRTYPE = 'INVOICE' THEN TRAMT ELSE -TRAMT END) OVER (PARTITION BY CUSTNO ORDER BY TRDATE, TRTYPE DESC) AS RUNNINGTOTAL
    FROM
    	(
        SELECT
            CUSTNO AS CUSTNO
            , CUSTNAME AS CUSTNAME
            , INVNO AS TRNO
            , INVDATE AS TRDATE
            , INVAMT AS TRAMT
          	, 'INVOICE' AS TRTYPE
        FROM
            SALESTABLE
        UNION ALL
        SELECT
            CUSTNO AS CUSTNO
            , CUSTNAME AS CUSTNAME
            , RECEIPTNO AS TRNO
            , RECEIPTDATE AS TRDATE
            , RECEIVEDAMT AS TRAMT
            , 'PAYMENT' AS TRTYPE
        FROM
            RECEIPTSTABLE
    	) AS QUERYRESULTTABLE
    WHERE
    	CUSTNO = 1
    ORDER BY
    	TRDATE;
    Fiddle: http://sqlfiddle.com/#!18/5c9745/4
    Last edited by dday9; Dec 1st, 2023 at 09:27 AM.
    "Code is like humor. When you have to explain it, it is bad." - Cory House
    VbLessons | Code Tags | Sword of Fury - Jameram

  6. #6

    Thread Starter
    Hyperactive Member
    Join Date
    Dec 2014
    Posts
    326

    Re: Need a query to show data of a customer account in a report form...

    Quote Originally Posted by ivansmo View Post
    With query results, you'll get all columns in DataGridView if you re-set up the data source. In this case, you need a query that works and then see how DGV shows data.

    BTW, that is the column just like any other column. I don't see why you call it virtual. They are all created and populated once the code asks for query results.

    You can also prepare columns in the code. You are preparing each column's property if you know what data to expect for each.

    Sample:
    Code:
    With DataGridView1
                .RowHeadersVisible = True
                .Columns(0).HeaderCell.Value = "CUSTNO"
                .Columns(1).HeaderCell.Value = "CUSTNAME"
                .Columns(2).HeaderCell.Value = "TRNO"
                .Columns(3).HeaderCell.Value = "TRDATE"
                .Columns(4).HeaderCell.Value = "TRAMT"
                .Columns(5).HeaderCell.Value = "BALANCE"
                .Columns(0).Width = 40
                .Columns(2).Width = 100
                .Columns(3).Width = 100
                .Columns(4).Width = 60
                .Columns(5).Width = 60
                .Columns(0).ReadOnly = True
                .Columns(1).ReadOnly = True
                .Columns(2).ReadOnly = True
                .Columns(3).DefaultCellStyle.BackColor = Color.AliceBlue
                .Columns(4).DefaultCellStyle.BackColor = Color.AliceBlue
                .Columns(5).DefaultCellStyle.BackColor = Color.AliceBlue
    
            End With
    EDIT:

    If you want to manipulate data directly in DGV, this is a sample code.

    Code:
    Private Sub DataGridView1_CellLeave(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellLeave
            Dim column3 As Double = 0
            Dim column4 As Double = 0
            Dim balance As Double = 0
           
            For i = 0 To DataGridView1.RowCount - 1
                column3 += DataGridView1.Rows(i).Cells(3).Value()
                column4 += DataGridView1.Rows(i).Cells(4).Value()
                balance = DataGridView1.CurrentRow.Cells(3).Value * DataGridView1.CurrentRow.Cells(4).Value
             
                DataGridView1.CurrentRow.Cells(7).Value = balance
               
    
            Next
     End Sub
    Note: this code executes on cell leave event.
    I tried it but got the error as shown in the snapshot.
    Attached Images Attached Images  

  7. #7

    Thread Starter
    Hyperactive Member
    Join Date
    Dec 2014
    Posts
    326

    Re: Need a query to show data of a customer account in a report form...

    Quote Originally Posted by dday9 View Post
    You can use a SUM OVER with the PARTION BY clause on the CUSTNO, e.g.:
    Code:
    SELECT
        CUSTNO
        , CUSTNAME
        , TRNO
        , TRDATE
        , TRAMT
        , SUM(CASE WHEN TRTYPE = 'INVOICE' THEN TRAMT ELSE -TRAMT END) OVER (PARTITION BY CUSTNO ORDER BY TRDATE, TRTYPE DESC) AS RUNNINGTOTAL
    FROM
    	(
        SELECT
            CUSTNO AS CUSTNO
            , CUSTNAME AS CUSTNAME
            , INVNO AS TRNO
            , INVDATE AS TRDATE
            , INVAMT AS TRAMT
          	, 'INVOICE' AS TRTYPE
        FROM
            SALESTABLE
        UNION ALL
        SELECT
            CUSTNO AS CUSTNO
            , CUSTNAME AS CUSTNAME
            , RECEIPTNO AS TRNO
            , RECEIPTDATE AS TRDATE
            , RECEIVEDAMT AS TRAMT
            , 'PAYMENT' AS TRTYPE
        FROM
            RECEIPTSTABLE
    	) AS QUERYRESULTTABLE
    WHERE
    	CUSTNO = 1
    ORDER BY
    	TRDATE;
    Fiddle: http://sqlfiddle.com/#!18/5c9745/4
    I am getting this error: ---------------------------
    Error
    ---------------------------
    Unrecognized keyword WHEN.
    ---------------------------
    OK
    ---------------------------
    Last edited by dday9; Dec 1st, 2023 at 09:27 AM.

  8. #8
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    11,672

    Re: Need a query to show data of a customer account in a report form...

    What SQL database are you using? I assumed it was SQL Server.
    "Code is like humor. When you have to explain it, it is bad." - Cory House
    VbLessons | Code Tags | Sword of Fury - Jameram

  9. #9

    Thread Starter
    Hyperactive Member
    Join Date
    Dec 2014
    Posts
    326

    Re: Need a query to show data of a customer account in a report form...

    Sorry. I didn't mention the database. It is MS Access Database.

  10. #10
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    11,672

    Re: Need a query to show data of a customer account in a report form...

    Just so you know, Access is a garbage database.

    You can still do what you want but you cannot use window functions or the OVER clause:
    Code:
    SELECT 
        q.CUSTNO,
        q.CUSTNAME,
        q.TRNO,
        q.TRDATE,
        q.TRAMT,
        (
            SELECT SUM(IIF(sub.TRTYPE = 'INVOICE', sub.TRAMT, -sub.TRAMT))
            FROM
                (
                    SELECT
                        CUSTNO,
                        INVNO AS TRNO,
                        INVDATE AS TRDATE,
                        INVAMT AS TRAMT,
                        'INVOICE' AS TRTYPE
                    FROM
                        SALESTABLE
                    UNION ALL
                    SELECT
                        CUSTNO,
                        RECEIPTNO AS TRNO,
                        RECEIPTDATE AS TRDATE,
                        RECEIVEDAMT AS TRAMT,
                        'PAYMENT' AS TRTYPE
                    FROM
                        RECEIPTSTABLE
                ) AS sub
            WHERE 
                sub.CUSTNO = q.CUSTNO 
                AND (sub.TRDATE < q.TRDATE OR (sub.TRDATE = q.TRDATE AND sub.TRNO <= q.TRNO))
        ) AS RUNNINGTOTAL
    FROM
        (
            SELECT
                CUSTNO,
                CUSTNAME,
                INVNO AS TRNO,
                INVDATE AS TRDATE,
                INVAMT AS TRAMT,
                'INVOICE' AS TRTYPE
            FROM
                SALESTABLE
            UNION ALL
            SELECT
                CUSTNO,
                CUSTNAME,
                RECEIPTNO AS TRNO,
                RECEIPTDATE AS TRDATE,
                RECEIVEDAMT AS TRAMT,
                'PAYMENT' AS TRTYPE
            FROM
                RECEIPTSTABLE
        ) AS q
    WHERE
        q.CUSTNO = 1
    ORDER BY
        q.TRDATE, q.TRNO;
    "Code is like humor. When you have to explain it, it is bad." - Cory House
    VbLessons | Code Tags | Sword of Fury - Jameram

  11. #11
    Lively Member
    Join Date
    Dec 2021
    Posts
    100

    Re: Need a query to show data of a customer account in a report form...

    OK, don't just C/P code sample. Exception telling you that you shouldn't use Operator '+' with date format.

    Cells(3), and Cells(4) are Cells in that column and Row(i). Make sure you Operate '+' with Cells containing decimals, integers....

  12. #12

    Thread Starter
    Hyperactive Member
    Join Date
    Dec 2014
    Posts
    326

    Re: Need a query to show data of a customer account in a report form...

    Now the code is working fine. But what I need is, instead of mentioning CUSTNO =1 in the code, I want this CUSTNO to be taken from txtSearchCustNo.text. How can I do that?

    Code:
    WHERE
        q.CUSTNO = 1
    I tried like this...

    Code:
    WHERE
                    q.CUSTNO = '" & txtSearchCustNo.Text & "'
    ---------------------------
    Getting this Error:
    ---------------------------
    Data type mismatch in criteria expression.
    ---------------------------
    OK
    ---------------------------

  13. #13
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    11,672

    Re: Need a query to show data of a customer account in a report form...

    You need to use a parameterized query.

    Your SQL related question has been answer and now you have a VB.NET question. I would suggest:
    1. Marking this thread
    2. Creating a new thread in the VB.NET forum titled How To Execute a Parameterized Query
    3. In the new thread, include the query and link to this one
    "Code is like humor. When you have to explain it, it is bad." - Cory House
    VbLessons | Code Tags | Sword of Fury - Jameram

  14. #14
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,296

    Re: Need a query to show data of a customer account in a report form...

    That setup is utter crap.
    If you have an invoice number (and hopefully it‘s unique across the board —> primary key), the you just have to look up for incoming payments to which invoice they refer, automagically giving you customer number and what not.

    and someone mentioned SUM OVER which would be the correct way.
    you mentioned MS Access…. Congratulations. Probably the worst database you could have used for your program
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

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