I have 2 tables - Driver and Insurance
The insurance table holds all insurances policies (including older ones) so a driver's name will exist in a few rows but the associated insurance policy number will be different.
For my query I need only the newest policy for each driver. I am searching for this by it's expiry date.
I am using this within vb.net and an ms access database to fill a datagridview with the results.
This is what I have so far that works correctly but shows the older policies too.
Using adapter As New OleDb.OleDbDataAdapter("select Driver.DriverName, Insurance.InsurancePolicyNum, Insurance.Expiry, from Driver, Insurance where Driver.DriverID= Insurance.DriverID order by Insurance.Expiry", Conn)
Dim table As New DataTable
adapter.Fill(table)
dgvReport.DataSource = table
End Using
So the results are:
DriverName PolicyNumber Expiry
Mark 7788899 07/02/11
Ann 8788778 10/12/10
Jane 5989899 06/10/10
Mark 232323 06/02/10
I only need Mark to appear once with the newest expiry date.
Is there a way of doing this in sql or else by searching the datatable/ datagrid?
Any help would be appreciated!
Thanks
The insurance table holds all insurances policies (including older ones) so a driver's name will exist in a few rows but the associated insurance policy number will be different.
For my query I need only the newest policy for each driver. I am searching for this by it's expiry date.
I am using this within vb.net and an ms access database to fill a datagridview with the results.
This is what I have so far that works correctly but shows the older policies too.
Using adapter As New OleDb.OleDbDataAdapter("select Driver.DriverName, Insurance.InsurancePolicyNum, Insurance.Expiry, from Driver, Insurance where Driver.DriverID= Insurance.DriverID order by Insurance.Expiry", Conn)
Dim table As New DataTable
adapter.Fill(table)
dgvReport.DataSource = table
End Using
So the results are:
DriverName PolicyNumber Expiry
Mark 7788899 07/02/11
Ann 8788778 10/12/10
Jane 5989899 06/10/10
Mark 232323 06/02/10
I only need Mark to appear once with the newest expiry date.
Is there a way of doing this in sql or else by searching the datatable/ datagrid?
Any help would be appreciated!
Thanks