problem with 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. What we are
trying to do is track variance for schedules. The
tbl_schedule_delivery has the fixed posted schedule for a
particular Dest_Unit. Then the table tbl_variance has the
actual schedule that is being keyed in on a daily basis.
Basically, the Del_Types that get put into the
tbl_variance are being used to see if they match up to the
schedule in the schedule_deliveries table on a weekly
basis. 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

 

[[MVP] S. Clark]

It appears that tbl_schedule_deliveries is a crosstab. Perhaps nomalizing
it will make your situation easier to work with.

--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

 

[John Verhagen]

Try modifying your query to:

SELECT tbl_variance.shipper, tbl_variance.Dest_Unit, tbl_variance.Del_Type,
tbl_variance.Delivery_Dt, tbl_scheduled_deliveries.Eff_Date,
tbl_scheduled_deliveries.FD, tbl_scheduled_deliveries.FR,
tbl_scheduled_deliveries.F2, tbl_scheduled_deliveries.FX,
tbl_variance.Cancel, Abs([Eff_Date]-[Delivery_Dt]) AS Days
FROM tbl_scheduled_deliveries INNER JOIN tbl_variance ON
tbl_scheduled_deliveries.Dest_Unit = tbl_variance.Dest_Unit
WHERE (((Abs([Eff_Date]-[Delivery_Dt]))=(select
min(Abs([Eff_Date]-[Delivery_Dt])) from tbl_scheduled_deliveries as D,
tbl_variance as V where D.Dest_unit=V.Dest_unit and V.id =
tbl_variance.id)));