help with dates

[Juan Melero]

Here is the query I have so far and it is pretty close to
what I need except that it is still bringing me back a
couple extra lines that I dont need.
SELECT tv.Shipper, tv.Dest_Unit, tv.Del_Type,
tv.Delivery_Dt, tv.Cancel, sd.Eff_Date, sd.FD, sd.FR,
sd.F2, sd.FX
FROM tbl_scheduled_deliveries AS sd INNER JOIN
tbl_variance AS tv ON sd.Dest_Unit = tv.Dest_Unit
WHERE exists (SELECT *
FROM tbl_scheduled_deliveries ts
WHERE sd.Eff_Date <= tv.Delivery_Dt And (DateDiff("d",
sd.Eff_Date, tv.Delivery_Dt) < DateDiff("d", ts.Eff_Date,
tv.Delivery_Dt)) And ts.Eff_Date <= tv.Delivery_Dt And
sd.Dest_Unit = ts.Dest_Unit);

-----Original Message-----
Duane...
The reason I have delivery types as field names is because
this is the actual schedule that must be followed for a
particular effective period. what we are trying to do
with our database is track variance between the posted
schedule and the actual schedule that is being tracked
everyday. For example ....the posted schedule might have
Eff_date FD FR F2 FX
1/1/03 2 1 1 0
1/8/03 1 1 1 0

But the actual everyday schedule might look like this.
Delivery_Dt Del_Type
1/1/03 FD
1/1/03 F2
1/2/03 FD
1/3/03 FR
1/4/03 FD
1/8/03 FD
1/9/03 FR
1/10/03 F2
....
you see for the first effective period between 1/1/03 and
1/7/03 we actually had (3 FD's) (1 F2) (1 FR) whereas the
posted schedule had (2 FD's) (1 F2) and (1FR).
.

..