1

enter image description hereenter image description hereI'm having big trouble creating Crosstab in VB.net with Access Database used. I'm new in this venture and still learning here and there. I managed to create many report with query, but I couldn't success with crosstab. My Access Database used has the following fields: Date - Description (Text) - Nature_Expense (Text) - Nature_Payment(Text) - Amount (number 18,2) and Status(text. My Requirement is to create crosstab with Rows Nature_Expense and Columns Month wise for a year with total Amount for that category. If possible I also would like to get total of columns and total of rows.

Nature Expense| Jan 2019 | Feb 2019 | ...| Dec 2019 | Total  |
--------------------------------------------------------------
Expense 1    |  2,326.50| 1,222.30 |    | 500.00   | 4,048.80| 
Expense 2    |  1,326.50| 2,222.30 |    | 600.00   | 4,148.80| 
.....
......
------------ ----------------------------------------------------
Total           3,653.00| 3,444.60 |    | 1,200.00 | 8,197.60|

I searched every where for more than a week and I find only sql query which does not work in vb.net Please Help Thank you in advance for your kind help.

This is What I am using as of now:

Using con
con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;
                            Data Source=GestionDesCharges03.accdb;
                            Persist Security Info=False;"
con.Open()
con = New OleDbConnection(con.ConnectionString)
Dim cmd As New OleDbCommand("Select ..........;" , con)
da = New OleDbDataAdapter(cmd)
ds = New DataSet
da.Fill(ds, "TransactionsList")
con.Close()
ListView1.Items.Clear()
da.Fill(ds, "MyData")

For i As Integer = 0 To ds.Tables("MyData").Rows.Count - 1
            Dim listRow As New ListViewItem
            listRow.Text = ds.Tables("MyData").Rows(i)(0).ToString()
            For j As Integer = 1 To ds.Tables("MyData").Columns.Count - 1
                listRow.SubItems.Add(ds.Tables("MyData").Rows(i)(j).ToString())
            Next
            ListView1.Items.Add(listRow)
Next

End Using

I have used Transform option by copying Query from Access to VB.Net as follows:

Dim cmd As New OleDbCommand("TRANSFORM Sum(TransactionsList.Amount) AS SumOfAmount
        SELECT TransactionsList.Nature_Expense
        FROM TransactionsList
        GROUP BY TransactionsList.Nature_Expense
        PIVOT Format([DateOper],'mm-yyyy');", con)

But the result I get is only (Expense 1) to (Expense last) as heading without any data.Screenshot of the Result of the Query

Screenshots requested Access and SQL View

Lahtag
  • 11
  • 2
  • Why does query not work in vb.net - what happens? Does this answer your question? [I need to create a CrossTab Query in my VB.net program that will produce this table](https://stackoverflow.com/questions/19012184/i-need-to-create-a-crosstab-query-in-my-vb-net-program-that-will-produce-this-ta) – June7 Jun 26 '20 at 14:00
  • All your data access code can be reduced to two lines: `Dim dt as New DataTable` and `New OleDbDataAdapter("select...", "Provider=...").Fill(dt)` - data adapter knows how to open connections, dispose etc. If you use a datagridview, it knows how to create columns etc so you can add another line `myDataGridView.DataSource = dt` to get your data into a grid. Please show us a screenshot of the query having been run in Access and prduced data. Also in the screenshot ensure you show something so we can see you're definitely using Access to run the query in the same DB file that the VB.NET code uses – Caius Jard Jun 26 '20 at 15:36
  • 1
    A frequent problem with file based DB like Access is that the file the developer opens (e.g. `c:\myprojects\awesomeprogram\myfile.accdb`) is not the same file that the program opens (e.g. `c:\myprojects\awesomeprogram\bin\debug\myfile.accdb`) but instead some empty copy etc – Caius Jard Jun 26 '20 at 15:40
  • Thank You, I Verified the path, changed it = same thing. I will adopt later two lines suggested. I edited my question to add screenshot as requested – Lahtag Jun 26 '20 at 16:47
  • As Suggested by Caius Jard, I reduced data access code to few lines not as proposed and use Datagridview.DataSource = Dt, and Hope it is working the report is coming nicely on DataGridView. So I think the problem is with ListView. Thank you for your kind support. – Lahtag Jun 26 '20 at 19:13

0 Answers0