vb.net and sql query

Question

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

peterm · Accepted Answer · 2013-07-03 05:38:43Z

4

If understand correctly you're probably looking for this

Use quotes around string literals ('Table1', Table2, 'Table3')
Use brackets [] around an alias that is a reserved word ([Table])
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

edited Jul 3, 2013 at 5:38

answered Jul 3, 2013 at 5:10

peterm

93k16 gold badges157 silver badges162 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Antonio Over a year ago

Anyway, the error he is saying is probably because of the use of Table as a name being a reserved word. You should use it as @Dinup said: [Table]

Edper · Accepted Answer · 2013-07-03 05:09:39Z

0

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.

answered Jul 3, 2013 at 5:09

Edper

9,3621 gold badge32 silver badges49 bronze badges

2 Comments

Craig T Over a year ago

You only need a GROUP BY if you are grouping by another field.

Edper Over a year ago

@CraigT I was thinking initially that the 'Table1' he was using is a field which warrants a GROUP BY until I realized he's using a constant value.

Dinup Kandel · Accepted Answer · 2013-07-03 05:12:23Z

0

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

answered Jul 3, 2013 at 5:12

Dinup Kandel

2,5054 gold badges22 silver badges39 bronze badges

Collectives™ on Stack Overflow

vb.net and sql query

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related