0

I've created an Access database of 5 tables (personalData, WorkExperience, EducationalData, SpouseData, DependantData)

I have STHN_ID as primary key in the first table and as foreign key in the others. I also have a column named DateEngaged as Date/Time.

I try to select from PersonalData where STHN_ID=00001 and DateEngaged is within a Certain date range and it works fine. this is my code is 

Dim rcmd As OleDbCommand = New OleDbCommand("Select STHN_ID FROM PersonalData WHERE ([STHN_ID]='" & STHN_ID.Text & "') and ([DateEngaged]>=CDate('" & FromDate.Value & "') AND [DateEngaged] <=CDate('" & ToDate.Value & "'))", myConnection)

My problem is after reading from PersonalData with those conditions and a record is found, it should go ahead and select all the fields in the other tables with that STHN_ID, that is, it should combine all columns in all the tables. the last thing I want to do is to read the combined columns to a datagridview.

So in the datagridview, I have all the columns from PersonalData, WorkExperience, EducationalData, SpouseData and Dependant data. so a row in the datagridview will contain all the records of that particular from the first table to the last table.

This is what I tried but it gives an error

da = New OleDbDataAdapter("Select * FROM [PersonalData] WHERE [STHN_ID]='" & STHNID.Text & "' AND ([DateEngaged]>=CDate('" & FromDate.Value & "') AND [DateEngaged]<=CDate('" & ToDate.Value & "' JOIN select * from EducationalData where STHN_ID='" & STHNID.Text & "' JOIN select * from WorkExperience where STHN_ID='" & STHNID.Text & "' JOIN select * from SpouseData where STHN_ID='" & STHNID.Text & "' JOIN select * from DependantData where STHN_ID='" & STHNID.Text & "'))", myConnection)
paisanco
  • 4,098
  • 6
  • 27
  • 33
Vincent Prince Assah
  • 31
  • 1
  • 8

1 Answers1

0

Use this query inside dataAdapter:

select PD.STHN_ID
    , PD.NAME
    , PD.DateEngaged
    , ED.NAMEOFSCHOOL
    , WE.EMPLOYER
    , SD.SPOUSENAME
    , DD.DEPENDANTNAME
from PersonalData PD
inner join EducationalData ED on PD.STHN_ID=ED.STHN_ID
inner join SPOUSEDATA SD on PD.STHN_ID=SD.STHN_ID
inner join DEPENDANTDATA DD on PD.STHN_ID=DD.STHN_ID
inner join WORKEXPERIENCE WE on PD.STHN_ID=WE.STHN_ID
WHERE ([PD.STHN_ID]='" & STHN_ID.Text & "')
    and ([PD.DateEngaged]>=CDate('" & FromDate.Value & "') 
    and [PD.DateEngaged] <=CDate('" & ToDate.Value & "'))
Binaya Shrestha
  • 1
  • 2
  • am getting a syntax error (missing operator) in query expression. BTW i am using VB and my database is access. and also should i include the PD, ED, SD, DD and WE? am lost – Vincent Prince Assah Mar 09 '16 at 05:32
  • I have changed where condition in answer according to your query and (PD, ED, SD, DD and WE) are `alias`. Read here, [Benefits of using `alias`](http://stackoverflow.com/questions/8363108/how-does-table-alias-names-affect-performance) – Binaya Shrestha Mar 09 '16 at 05:53