Selecting records based on another table

  • Thread starter Thread starter Bob
  • Start date Start date
B

Bob

I have a repair database with two tables not related. One contains all of
the invoices for repairs based on truck number invoice number and date.
The other table contains the driver name truck number and the date range
that driver drove that truck. I have added a new field to the invoice
table called driver. I need to attach the invoices to a driver base on the
truck they drove and the date of the invoice. I am totally stumped as to
how to proceed. Any help would be greatly appreciated. Thanks in advance.
 
Hi Bob,

I'm not sure if I quite understand the problem, but does this help:

Consider the following Tables:

REPAIRS:
TruckNo
InvoiceNo
Date
Driver

DRIVERS
DriverName
TruckNo
FromDate
ToDate

Then you could write a query somthing like:

SELECT Drivers.DriverName, Repairs.InvoiceNo,Repairs.Date
FROM Drivers, Repairs
Where Repairs.TruckNo = Drivers.TruckNo
and Repairs.Date between Drivers.FromDate and Drivers.ToDate

This would give you a list of Drivers, invoiceNumbers and InvoiceDates
where the invoice date for a truck is during the period that the driver was
driving that truck.

You could also do:

UPDATE Drivers, Repairs SET Repairs.Driver = Drivers.DriverName
Where Repairs.TruckNo = Drivers.TruckNo
and Repairs.Date between Drivers.DateFrom and Drivers.DateTo

This would put the appropriate driver name into the Driver field of the
REPAIRS table.

Hope this helps;

ChrisM

Bob said:
I have a repair database with two tables not related. One contains all of
the invoices for repairs based on truck number invoice number and date.
The other table contains the driver name truck number and the date range
that driver drove that truck. I have added a new field to the invoice
table called driver. I need to attach the invoices to a driver base on the
truck they drove and the date of the invoice. I am totally stumped as to
how to proceed. Any help would be greatly appreciated. Thanks in
Click to expand...
advance.
 
The sql appears to be exactly what I want however it is not populating the
driver field the driver field......any suggestions?
 
Back
Top