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
        conn.Open()
        MsgBox(conn.State.ToString)

        da.SelectCommand = command
        da.Fill(ds)
        Dim dt As New DataTable
        da.Fill(dt)
        DataGridView1.DataSource = dt

    End Sub
End Class

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

Regards

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 3 Years Ago by 404notfound

Hi,
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 article has been dead for over six months. Start a new discussion instead.