Dates query.

[Juan Melero]

Heres the situation. I have two tables.
tbl_schedule_delivery has attributes
Id.....Primary key.
shipper
Dest_Unit
Eff_Date
FD
FR
F2
FX.

Then table
tbl_variance
Id....Primary Key
shipper
Dest_Unit
Del_Type
Delivery_Dt
The situation is that table tbl_variance gets values
inputted every day or every other day.
tbl_shedule_deliveries only gets update every time a
schedule changes for a particular Dest_Unit. For example
in table tbl_schedule_deliveries we would have values
Id shipper Dest_Unit Eff_Date FD FR F2 FX
1 0449 1008 1/1/03 1 1 0 0
2 0449 1009 1/1/03 1 1 1 0
3 0449 1008 3/1/03 2 1 0 0
4 0449 1007 1/1/03 1 0 0 0
5 0449 1009 4/3/03 1 1 0 1

Now in table tbl_variance we would have these values
Id shipper Dest_Unit Del_Type Delivery_Dt Cancel
201 0449 1008 FD 1/1/03 No
202 0449 1009 FR 1/1/03 No
203 0449 1008 FR 1/2/03 No
204 0449 1007 FD 1/2/03 No
205 0449 1008 FD 1/3/03 Yes
..
..
..
234 0449 1008 FD 2/29/03 Yes
235 0449 1008 FD 3/1/03 Yes
236 0449 1008 FR 3/2/03 Yes
So here is what I need. I need a query that ties in the
delivery_dt and the effective date for a particular
dest_unit. For example I would like to see a query that
displays the following
shipper Dest_Unit Eff_Date Delivery_Dt Del_Type
0449 1008 1/1/03 1/1/03 FD
0449 1008 1/1/03 1/2/03 FR
0449 1008 1/1/03 1/3/03 FD
0449 1008 1/1/03 1/4/03 ..
....
....
0449 1008 1/1/03 2/29/03 FD
0449 1008 3/1/03 3/1/03 FD
0449 1008 3/1/03 3/2/03 FR

I hope this example explains what I need.

 

[Duane Hookom]

It seems that your issue may be that your tbl_schedule_delivery is not
normalized. You have Delivery Types as field names.
However in your samples, you do you know which record in
tbl_schedule_delivery is related to which record(s) in tbl_Variance since
Shipper and Dest_Unit combined are not a unique combination in either table?

 

[Juan Melero]

Yes. Dest_Unit and Shipper are common. Like I said before
tbl_variance is used to track daily delivery types or
every other days. What tbl_schedule_delivery is for is to
post a temporary fixed delivery schedule for a particular
dest_unit. Tbl_variance is used to track the daily
delivery schedule. What I want is to capture variance
between the temporary fixed schedule and the actual
schedule that is being tracked on a daily basis. If you
want to see an example of this read the reply I left for
Duane Hookom. Users will actually put a schedule out for
a particular store such as the following
shipper Dest_Unit Eff_Date FD FR .....
0449 1008 1/1/03 1 1 ..

say the schedule changes for this Dest_Unit then another
entry would be added.
shipper Dest_Unit Eff_Date FD FR
0449 1008 1/22/03 2 1

Now the tbl_variance tracks the daily variance. And we
make sure the posted schedule and the actual schedule
match on a weekly basis.
shipper Dest_Unit Delivery_Dt Del_Type
0449 1008 1/2/03 FD
0449 1008 1/3/03 FR
say the week is from 1/1/03 to 1/5/03 then the above
schedule would match the posted fixed schedule.
shipper Dest_Unit Delivery_Dt Del_Type
0449 1008 1/22/03 FD
0449 1008 1/23/03 FD
0449 1008 1/23/03 FD
0449 1008 1/24/03 FR.
See now this schedule would not match for the posted
1/22/03 effective date schedule. We would have one more
FD.