Work has informed me that I need to run reports a certain way. I need to pull data out of the system database, and compile a report.
I have a database with 3 tables. Personel (contains employee data), Q1 (1st quarter data) and Q2 (second quarter data).
I'm trying to align the colums in the datagrid like:
Personel.EmpNumber | Personel.SS | Q1.Wages | Q2.Wages

I need all four columns as some people worked in the second quarter but not the first, and vise versa. But when I run it, only the first three columns show.
Here's my code:

Option Strict On

Public Class Form1
    Dim conn As New OleDb.OleDbConnection
    Dim ds As New DataSet
    Dim da As New OleDb.OleDbDataAdapter
    Dim sql As String
    Dim strConn As String = "PROVIDER=microsoft.ace.oledb.12.0;Data Source = C:\users\fantus\documents\Database1.accdb"
    Dim query As String = "SELECT Personel.AcctNum, (personel.SS1 + personel.SS2 + personel.SS3) AS SS, Q1.Q1Wages FROM Personel, Q1 WHERE q1.acctnum = personel.acctnum UNION SELECT Personel.AcctNum, (personel.SS1 + personel.SS2 + personel.SS3) AS SS, Q2.Q2Wages FROM Personel, Q2 WHERE q2.acctnum = personel.acctnum"

    Dim command As New OleDb.OleDbCommand(query, conn)
    Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        conn.ConnectionString = strConn

        da.SelectCommand = command
        Dim dt As New DataTable
        DataGridView1.DataSource = dt

    End Sub
End Class
3 Years
Discussion Span
Last Post by G_Waddell

i dont know , in which database your are working , here is a query you can use for mssql server. hope this will help you

Select p.EmpNumber,p.ss,q1.wages,q2.wages
From Personel p 
left outer join q1 on p.personelid = q1.personelid
left outer join q2 on p.personelid = q2.personelid



Thanks, M.Wagas. I'm using Access from Visual Basic.
The line I put it was SELECT Personel.AcctNum, Personel.SS, Q1.Q1Wages, Q2.Q2Wages From Personel Left Outer Join Q1 on Personel.AcctNum = Q1.AcctNum Left Outer Join Q2 on Personel.AcctNum = Q2.AcctNum
And my DGV is empty. What did the p at the end of From Personel p mean?

Edited by 404notfound


the "p" at the end was to allow him to refer to the Personel table as p instead of writing out Personel all the time i.e. p.PersonelID instead of Personel.PersonelID. Try generating the query inside your Access using the QBE, make sure your happy with the results then switch over to the SQL designer view and you will get the correctly formatted SQL for Access.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.