CODEWITHSUNDEEP
sqlvb.netms-access
rony
rony

Reputation: 39

vb.net and sql query

I am new to vb.net so bear with me. I have some managable tables in ms access database. I am trying to write a sql query that will give me following output:

table1 ( columnA - columnB)
table2 ( columnA - columnB)
table3 ( columnA - columnB)

I have column name "column A" and “column B" in all tables.

I want to retrieve tables name and sum of (column A – column B) of all tables. When I run the sql statement I get the following error:

The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect

Can someone help me to write a sql query please?

Here is my full code:

Dim con As New OleDbConnection(" Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\msacc.accdb")   

Dim Query2 As String = " SELECT Table1 AS Table, SUM(a) - SUM(b) AS Result FROM table1 UNION  SELECT Table2, SUM(a) - SUM(b) FROM table2  UNION SELECT Table3, SUM(a) - SUM(b) FROM table3"
Dim Da As OleDbDataAdapter, Ds As New DataSet, Dtb As New System.Data.DataTable
con.Open()
Da = New OleDbDataAdapter(Query2, con)
Da.Fill(Ds)
con.Close()
Dtb = Ds.Tables(0)
DataGridView1.DataSource = Dtb

Upvotes: 3

Views: 1340

Answers (3)

peterm
peterm

Reputation: 92785

If understand correctly you're probably looking for this

  1. Use quotes around string literals ('Table1', Table2, 'Table3')
  2. Use brackets [] around an alias that is a reserved word ([Table])
  3. Use UNION ALL instead of UNION since you don't need to eliminate any duplicates in your resultset
SELECT 'Table1' AS [Table], SUM(a) - SUM(b) AS Result 
  FROM table1 
 UNION ALL
SELECT 'Table2', SUM(a) - SUM(b) 
  FROM table2  
 UNION ALL
SELECT 'Table3', SUM(a) - SUM(b) 
  FROM table3

Here is SQLFiddle demo (SQL Server) The query itself will work in MS Access just fine

Upvotes: 4

Dinup Kandel
Dinup Kandel

Reputation: 2505

You have done mistake in this Query2. Dont use SELECT Table1 AS Table instead of this use select 'Table1' as [table]

Upvotes: 0

Edper
Edper

Reputation: 9322

SELECT Table1 AS Table

The word Table is a reserved word so used other name.

And SUM() is an aggregate function wherein you need to use a GROUP BY clause.

Upvotes: 0

Related Questions