Most recent Date problem

J

Juan Melero

I have the same problem as one of the problems stated
earlier with the most recent date. The only problem is
that I need to Join Three tables and I think this is
giving me a problem.
This is what I have so far.

SELECT tbl_delivery_info.Dest_Unit, locn.Dest_City,
locn.Dest_St, tbl_delivery_info.Shipper,
tbl_delivery_info.Del_Su_S, tbl_delivery_info.Del_Typ_Su,
tbl_delivery_info.Del_Su_E, tbl_delivery_info.Del_Mo_S,
tbl_delivery_info.Del_Typ_Mo, tbl_delivery_info.Del_Mo_E,
tbl_delivery_info.Del_Tu_S, tbl_delivery_info.Del_Typ_Tu,
tbl_delivery_info.Del_Tu_E, tbl_delivery_info.Del_We_S,
tbl_delivery_info.Del_Typ_We, tbl_delivery_info.Del_We_E,
tbl_delivery_info.Del_Th_S, tbl_delivery_info.Del_Typ_Th,
tbl_delivery_info.Del_Th_E, tbl_delivery_info.Del_Fr_S,
tbl_delivery_info.Del_Typ_Fr, tbl_delivery_info.Del_Fr_E,
tbl_delivery_info.Del_Sa_S, tbl_delivery_info.Del_Typ_Sa,
tbl_delivery_info.Del_Sa_E, locn.District,
locn.Store_Type, tbl_delivery_info.Live_Drop,
shippers.ship_type, tbl_delivery_info.Comments,
tbl_delivery_info.Recv_Alt_Ship,
tbl_delivery_info.XC_Dest_Unit,
tbl_delivery_info.XC_Ship_Unit,
tbl_delivery_info.Batch_Number,
tbl_delivery_info.Service_Level, Max
(tbl_delivery_info.Eff_Date) AS MaxOfEff_Date,
tbl_locn_profiles.closed_date, tbl_delivery_info.End_Date
FROM (shippers INNER JOIN (locn INNER JOIN
tbl_delivery_info ON locn.Dest_Unit =
tbl_delivery_info.Dest_Unit) ON shippers.SHIPPER =
tbl_delivery_info.Shipper) INNER JOIN tbl_locn_profiles ON
tbl_delivery_info.Dest_Unit = tbl_locn_profiles.Dest_Unit
GROUP BY tbl_delivery_info.Dest_Unit, locn.Dest_City,
locn.Dest_St, tbl_delivery_info.Shipper,
tbl_delivery_info.Del_Su_S, tbl_delivery_info.Del_Typ_Su,
tbl_delivery_info.Del_Su_E, tbl_delivery_info.Del_Mo_S,
tbl_delivery_info.Del_Typ_Mo, tbl_delivery_info.Del_Mo_E,
tbl_delivery_info.Del_Tu_S, tbl_delivery_info.Del_Typ_Tu,
tbl_delivery_info.Del_Tu_E, tbl_delivery_info.Del_We_S,
tbl_delivery_info.Del_Typ_We, tbl_delivery_info.Del_We_E,
tbl_delivery_info.Del_Th_S, tbl_delivery_info.Del_Typ_Th,
tbl_delivery_info.Del_Th_E, tbl_delivery_info.Del_Fr_S,
tbl_delivery_info.Del_Typ_Fr, tbl_delivery_info.Del_Fr_E,
tbl_delivery_info.Del_Sa_S, tbl_delivery_info.Del_Typ_Sa,
tbl_delivery_info.Del_Sa_E, locn.District,
locn.Store_Type, tbl_delivery_info.Live_Drop,
shippers.ship_type, tbl_delivery_info.Comments,
tbl_delivery_info.Recv_Alt_Ship,
tbl_delivery_info.XC_Dest_Unit,
tbl_delivery_info.XC_Ship_Unit,
tbl_delivery_info.Batch_Number,
tbl_delivery_info.Service_Level,
tbl_locn_profiles.closed_date, tbl_delivery_info.End_Date
HAVING (((locn.District)=[Enter District Number]) AND
((tbl_delivery_info.Service_Level)="1") AND
((tbl_locn_profiles.closed_date) Is Null) AND
((tbl_delivery_info.End_Date) Is Null Or
(tbl_delivery_info.End_Date)>Now()))
ORDER BY tbl_delivery_info.Dest_Unit,
tbl_delivery_info.Shipper;

This query still gives me two dates. I only need the most
recent date.
Can anybody tell me what I am doing wrong? I need your
help A.S.A.P. Thanks in advance.
 
D

Dale Fye

Juan,

Explain what you are trying to accomplish with the three tables, it
will probably be easier for us to rewrite your query than to try to
decipher what you are trying to do by looking at your SQL statement.
Describe the tables and the PK/FK fields, then let us see if we can
make some sense of it.

--
HTH

Dale Fye


I have the same problem as one of the problems stated
earlier with the most recent date. The only problem is
that I need to Join Three tables and I think this is
giving me a problem.
This is what I have so far.

SELECT tbl_delivery_info.Dest_Unit, locn.Dest_City,
locn.Dest_St, tbl_delivery_info.Shipper,
tbl_delivery_info.Del_Su_S, tbl_delivery_info.Del_Typ_Su,
tbl_delivery_info.Del_Su_E, tbl_delivery_info.Del_Mo_S,
tbl_delivery_info.Del_Typ_Mo, tbl_delivery_info.Del_Mo_E,
tbl_delivery_info.Del_Tu_S, tbl_delivery_info.Del_Typ_Tu,
tbl_delivery_info.Del_Tu_E, tbl_delivery_info.Del_We_S,
tbl_delivery_info.Del_Typ_We, tbl_delivery_info.Del_We_E,
tbl_delivery_info.Del_Th_S, tbl_delivery_info.Del_Typ_Th,
tbl_delivery_info.Del_Th_E, tbl_delivery_info.Del_Fr_S,
tbl_delivery_info.Del_Typ_Fr, tbl_delivery_info.Del_Fr_E,
tbl_delivery_info.Del_Sa_S, tbl_delivery_info.Del_Typ_Sa,
tbl_delivery_info.Del_Sa_E, locn.District,
locn.Store_Type, tbl_delivery_info.Live_Drop,
shippers.ship_type, tbl_delivery_info.Comments,
tbl_delivery_info.Recv_Alt_Ship,
tbl_delivery_info.XC_Dest_Unit,
tbl_delivery_info.XC_Ship_Unit,
tbl_delivery_info.Batch_Number,
tbl_delivery_info.Service_Level, Max
(tbl_delivery_info.Eff_Date) AS MaxOfEff_Date,
tbl_locn_profiles.closed_date, tbl_delivery_info.End_Date
FROM (shippers INNER JOIN (locn INNER JOIN
tbl_delivery_info ON locn.Dest_Unit =
tbl_delivery_info.Dest_Unit) ON shippers.SHIPPER =
tbl_delivery_info.Shipper) INNER JOIN tbl_locn_profiles ON
tbl_delivery_info.Dest_Unit = tbl_locn_profiles.Dest_Unit
GROUP BY tbl_delivery_info.Dest_Unit, locn.Dest_City,
locn.Dest_St, tbl_delivery_info.Shipper,
tbl_delivery_info.Del_Su_S, tbl_delivery_info.Del_Typ_Su,
tbl_delivery_info.Del_Su_E, tbl_delivery_info.Del_Mo_S,
tbl_delivery_info.Del_Typ_Mo, tbl_delivery_info.Del_Mo_E,
tbl_delivery_info.Del_Tu_S, tbl_delivery_info.Del_Typ_Tu,
tbl_delivery_info.Del_Tu_E, tbl_delivery_info.Del_We_S,
tbl_delivery_info.Del_Typ_We, tbl_delivery_info.Del_We_E,
tbl_delivery_info.Del_Th_S, tbl_delivery_info.Del_Typ_Th,
tbl_delivery_info.Del_Th_E, tbl_delivery_info.Del_Fr_S,
tbl_delivery_info.Del_Typ_Fr, tbl_delivery_info.Del_Fr_E,
tbl_delivery_info.Del_Sa_S, tbl_delivery_info.Del_Typ_Sa,
tbl_delivery_info.Del_Sa_E, locn.District,
locn.Store_Type, tbl_delivery_info.Live_Drop,
shippers.ship_type, tbl_delivery_info.Comments,
tbl_delivery_info.Recv_Alt_Ship,
tbl_delivery_info.XC_Dest_Unit,
tbl_delivery_info.XC_Ship_Unit,
tbl_delivery_info.Batch_Number,
tbl_delivery_info.Service_Level,
tbl_locn_profiles.closed_date, tbl_delivery_info.End_Date
HAVING (((locn.District)=[Enter District Number]) AND
((tbl_delivery_info.Service_Level)="1") AND
((tbl_locn_profiles.closed_date) Is Null) AND
((tbl_delivery_info.End_Date) Is Null Or
(tbl_delivery_info.End_Date)>Now()))
ORDER BY tbl_delivery_info.Dest_Unit,
tbl_delivery_info.Shipper;

This query still gives me two dates. I only need the most
recent date.
Can anybody tell me what I am doing wrong? I need your
help A.S.A.P. Thanks in advance.
 
J

Juan Melero

I have four tables I use in the query.
1) Table: locn
primary key= Dest_Unit
attributes:
Dest_ST
Dest_City
ZIP_CD
STORE_TYPE
REGION
District
2) Table: tbl_locn_profiles
primary key = Dest_Unit
Attribute:
close_date
3) Table: shippers
primary key = SHIPPER
attributes:
ship_st
ship_city
ship_type
4) Table: tbl_delivery_info
Foreign Keys: Dest_Unit, SHIPPER
Attributes:
Id
Dest_Unit
SHIPPER
Eff_Date
End_Date
Service_Level
//Then it has all the delivery types and information like
what I have on the query that I sent you.
** Now what I need is all the fields that I sent you in
the original query. I need the Max Eff_Date for each
Dest_Unit.



-----Original Message-----
Juan,

Explain what you are trying to accomplish with the three tables, it
will probably be easier for us to rewrite your query than to try to
decipher what you are trying to do by looking at your SQL statement.
Describe the tables and the PK/FK fields, then let us see if we can
make some sense of it.

--
HTH

Dale Fye


I have the same problem as one of the problems stated
earlier with the most recent date. The only problem is
that I need to Join Three tables and I think this is
giving me a problem.
This is what I have so far.

SELECT tbl_delivery_info.Dest_Unit, locn.Dest_City,
locn.Dest_St, tbl_delivery_info.Shipper,
tbl_delivery_info.Del_Su_S, tbl_delivery_info.Del_Typ_Su,
tbl_delivery_info.Del_Su_E, tbl_delivery_info.Del_Mo_S,
tbl_delivery_info.Del_Typ_Mo, tbl_delivery_info.Del_Mo_E,
tbl_delivery_info.Del_Tu_S, tbl_delivery_info.Del_Typ_Tu,
tbl_delivery_info.Del_Tu_E, tbl_delivery_info.Del_We_S,
tbl_delivery_info.Del_Typ_We, tbl_delivery_info.Del_We_E,
tbl_delivery_info.Del_Th_S, tbl_delivery_info.Del_Typ_Th,
tbl_delivery_info.Del_Th_E, tbl_delivery_info.Del_Fr_S,
tbl_delivery_info.Del_Typ_Fr, tbl_delivery_info.Del_Fr_E,
tbl_delivery_info.Del_Sa_S, tbl_delivery_info.Del_Typ_Sa,
tbl_delivery_info.Del_Sa_E, locn.District,
locn.Store_Type, tbl_delivery_info.Live_Drop,
shippers.ship_type, tbl_delivery_info.Comments,
tbl_delivery_info.Recv_Alt_Ship,
tbl_delivery_info.XC_Dest_Unit,
tbl_delivery_info.XC_Ship_Unit,
tbl_delivery_info.Batch_Number,
tbl_delivery_info.Service_Level, Max
(tbl_delivery_info.Eff_Date) AS MaxOfEff_Date,
tbl_locn_profiles.closed_date, tbl_delivery_info.End_Date
FROM (shippers INNER JOIN (locn INNER JOIN
tbl_delivery_info ON locn.Dest_Unit =
tbl_delivery_info.Dest_Unit) ON shippers.SHIPPER =
tbl_delivery_info.Shipper) INNER JOIN tbl_locn_profiles ON
tbl_delivery_info.Dest_Unit = tbl_locn_profiles.Dest_Unit
GROUP BY tbl_delivery_info.Dest_Unit, locn.Dest_City,
locn.Dest_St, tbl_delivery_info.Shipper,
tbl_delivery_info.Del_Su_S, tbl_delivery_info.Del_Typ_Su,
tbl_delivery_info.Del_Su_E, tbl_delivery_info.Del_Mo_S,
tbl_delivery_info.Del_Typ_Mo, tbl_delivery_info.Del_Mo_E,
tbl_delivery_info.Del_Tu_S, tbl_delivery_info.Del_Typ_Tu,
tbl_delivery_info.Del_Tu_E, tbl_delivery_info.Del_We_S,
tbl_delivery_info.Del_Typ_We, tbl_delivery_info.Del_We_E,
tbl_delivery_info.Del_Th_S, tbl_delivery_info.Del_Typ_Th,
tbl_delivery_info.Del_Th_E, tbl_delivery_info.Del_Fr_S,
tbl_delivery_info.Del_Typ_Fr, tbl_delivery_info.Del_Fr_E,
tbl_delivery_info.Del_Sa_S, tbl_delivery_info.Del_Typ_Sa,
tbl_delivery_info.Del_Sa_E, locn.District,
locn.Store_Type, tbl_delivery_info.Live_Drop,
shippers.ship_type, tbl_delivery_info.Comments,
tbl_delivery_info.Recv_Alt_Ship,
tbl_delivery_info.XC_Dest_Unit,
tbl_delivery_info.XC_Ship_Unit,
tbl_delivery_info.Batch_Number,
tbl_delivery_info.Service_Level,
tbl_locn_profiles.closed_date, tbl_delivery_info.End_Date
HAVING (((locn.District)=[Enter District Number]) AND
((tbl_delivery_info.Service_Level)="1") AND
((tbl_locn_profiles.closed_date) Is Null) AND
((tbl_delivery_info.End_Date) Is Null Or
(tbl_delivery_info.End_Date)>Now()))
ORDER BY tbl_delivery_info.Dest_Unit,
tbl_delivery_info.Shipper;

This query still gives me two dates. I only need the most
recent date.
Can anybody tell me what I am doing wrong? I need your
help A.S.A.P. Thanks in advance.


.
Click to expand...
 
J

Juan Melero

Dale this works fine....but I still need to add other
fields from my other tables how do I add to the query you
gave me to make it complete?
From table locn I need to add attributes.
Dest_City, Dest_St, District, and Store_Type.
From table tbl_locn_profiles I need to add
closed_date.
From table shippers I need to add
ship_type.
Thank you so much for the help so Far.
-----Original Message-----
Juan, I'm still not sure entirely what you are trying to accomplish.
But I'll take a shot. I would recommend is that you create a query
(qry_max_delivery_info) to identify all the information in the
tbl_delivery_info table with the most Max([Eff_Date]).

qry_max_delivery_info:

SELECT DI.*
FROM tbl_Delivery_info DI
INNER JOIN
[SELECT Dest_Unit, MAX(Eff_Date) as Max_Eff_Date
FROM tbl_Delivery_info
GROUP BY Dest_Unit]. as T
ON DI.Dest_Unit = T.Dest_Unit
AND DI.Eff_Date = T.Max_Eff_Date

This query will return all the fields from your delivery table, but
will only return only one record for each Dest_Unit, the one with the
maximum Eff_Date. Replace all references to tbl_delivery_info in your
original query and use this query instead. Because this will
eliminate all the duplicate records based on Dest_Unit, you should be
able to eliminate the GROUP BY clause as well.

--
HTH

Dale Fye


I have four tables I use in the query.
1) Table: locn
primary key= Dest_Unit
attributes:
Dest_ST
Dest_City
ZIP_CD
STORE_TYPE
REGION
District
2) Table: tbl_locn_profiles
primary key = Dest_Unit
Attribute:
close_date
3) Table: shippers
primary key = SHIPPER
attributes:
ship_st
ship_city
ship_type
4) Table: tbl_delivery_info
Foreign Keys: Dest_Unit, SHIPPER
Attributes:
Id
Dest_Unit
SHIPPER
Eff_Date
End_Date
Service_Level
//Then it has all the delivery types and information like
what I have on the query that I sent you.
** Now what I need is all the fields that I sent you in
the original query. I need the Max Eff_Date for each
Dest_Unit.



-----Original Message-----
Juan,

Explain what you are trying to accomplish with the three tables, it
will probably be easier for us to rewrite your query than to try to
decipher what you are trying to do by looking at your SQL statement.
Describe the tables and the PK/FK fields, then let us see if we can
make some sense of it.

--
HTH

Dale Fye


I have the same problem as one of the problems stated
earlier with the most recent date. The only problem is
that I need to Join Three tables and I think this is
giving me a problem.
This is what I have so far.

SELECT tbl_delivery_info.Dest_Unit, locn.Dest_City,
locn.Dest_St, tbl_delivery_info.Shipper,
tbl_delivery_info.Del_Su_S, tbl_delivery_info.Del_Typ_Su,
tbl_delivery_info.Del_Su_E, tbl_delivery_info.Del_Mo_S,
tbl_delivery_info.Del_Typ_Mo, tbl_delivery_info.Del_Mo_E,
tbl_delivery_info.Del_Tu_S, tbl_delivery_info.Del_Typ_Tu,
tbl_delivery_info.Del_Tu_E, tbl_delivery_info.Del_We_S,
tbl_delivery_info.Del_Typ_We, tbl_delivery_info.Del_We_E,
tbl_delivery_info.Del_Th_S, tbl_delivery_info.Del_Typ_Th,
tbl_delivery_info.Del_Th_E, tbl_delivery_info.Del_Fr_S,
tbl_delivery_info.Del_Typ_Fr, tbl_delivery_info.Del_Fr_E,
tbl_delivery_info.Del_Sa_S, tbl_delivery_info.Del_Typ_Sa,
tbl_delivery_info.Del_Sa_E, locn.District,
locn.Store_Type, tbl_delivery_info.Live_Drop,
shippers.ship_type, tbl_delivery_info.Comments,
tbl_delivery_info.Recv_Alt_Ship,
tbl_delivery_info.XC_Dest_Unit,
tbl_delivery_info.XC_Ship_Unit,
tbl_delivery_info.Batch_Number,
tbl_delivery_info.Service_Level, Max
(tbl_delivery_info.Eff_Date) AS MaxOfEff_Date,
tbl_locn_profiles.closed_date, tbl_delivery_info.End_Date
FROM (shippers INNER JOIN (locn INNER JOIN
tbl_delivery_info ON locn.Dest_Unit =
tbl_delivery_info.Dest_Unit) ON shippers.SHIPPER =
tbl_delivery_info.Shipper) INNER JOIN tbl_locn_profiles ON
tbl_delivery_info.Dest_Unit = tbl_locn_profiles.Dest_Unit
GROUP BY tbl_delivery_info.Dest_Unit, locn.Dest_City,
locn.Dest_St, tbl_delivery_info.Shipper,
tbl_delivery_info.Del_Su_S, tbl_delivery_info.Del_Typ_Su,
tbl_delivery_info.Del_Su_E, tbl_delivery_info.Del_Mo_S,
tbl_delivery_info.Del_Typ_Mo, tbl_delivery_info.Del_Mo_E,
tbl_delivery_info.Del_Tu_S, tbl_delivery_info.Del_Typ_Tu,
tbl_delivery_info.Del_Tu_E, tbl_delivery_info.Del_We_S,
tbl_delivery_info.Del_Typ_We, tbl_delivery_info.Del_We_E,
tbl_delivery_info.Del_Th_S, tbl_delivery_info.Del_Typ_Th,
tbl_delivery_info.Del_Th_E, tbl_delivery_info.Del_Fr_S,
tbl_delivery_info.Del_Typ_Fr, tbl_delivery_info.Del_Fr_E,
tbl_delivery_info.Del_Sa_S, tbl_delivery_info.Del_Typ_Sa,
tbl_delivery_info.Del_Sa_E, locn.District,
locn.Store_Type, tbl_delivery_info.Live_Drop,
shippers.ship_type, tbl_delivery_info.Comments,
tbl_delivery_info.Recv_Alt_Ship,
tbl_delivery_info.XC_Dest_Unit,
tbl_delivery_info.XC_Ship_Unit,
tbl_delivery_info.Batch_Number,
tbl_delivery_info.Service_Level,
tbl_locn_profiles.closed_date, tbl_delivery_info.End_Date
HAVING (((locn.District)=[Enter District Number]) AND
((tbl_delivery_info.Service_Level)="1") AND
((tbl_locn_profiles.closed_date) Is Null) AND
((tbl_delivery_info.End_Date) Is Null Or
(tbl_delivery_info.End_Date)>Now()))
ORDER BY tbl_delivery_info.Dest_Unit,
tbl_delivery_info.Shipper;

This query still gives me two dates. I only need the most
recent date.
Can anybody tell me what I am doing wrong? I need your
help A.S.A.P. Thanks in advance.


.
Click to expand...


.
Click to expand...
 
D

Dale Fye

Juan,

Replace all of the references to tbl_Delivery_Info in your original
query with qry_max_Delivery_Info. Once you have done this, you should
also be able to eliminate the Group By clause and change the Having
clause to a Where clause.

I think it will look something like the following if you alias your
query name as qMDI (I do this because references to long table names
really bug me when I am looking at code.
SELECT qMDI.Dest_Unit, locn.Dest_City,
locn.Dest_St, qMDI.Shipper,
qMDI.Del_Su_S, qMDI.Del_Typ_Su,
qMDI.Del_Su_E, qMDI.Del_Mo_S,
qMDI.Del_Typ_Mo, qMDI.Del_Mo_E,
qMDI.Del_Tu_S, qMDI.Del_Typ_Tu,
qMDI.Del_Tu_E, qMDI.Del_We_S,
qMDI.Del_Typ_We, qMDI.Del_We_E,
qMDI.Del_Th_S, qMDI.Del_Typ_Th,
qMDI.Del_Th_E, qMDI.Del_Fr_S,
qMDI.Del_Typ_Fr, qMDI.Del_Fr_E,
qMDI.Del_Sa_S, qMDI.Del_Typ_Sa,
qMDI.Del_Sa_E, locn.District,
locn.Store_Type, qMDI.Live_Drop,
shippers.ship_type, qMDI.Comments,
qMDI.Recv_Alt_Ship, qMDI.XC_Dest_Unit,
qMDI.XC_Ship_Unit, qMDI.Batch_Number,
qMDI.Service_Level,
tbl_locn_profiles.closed_date, qMDI.End_Date
FROM (shippers INNER JOIN
(locn INNER JOIN qMDI
ON locn.Dest_Unit = qMDI.Dest_Unit)
ON shippers.SHIPPER = qMDI.Shipper)
INNER JOIN tbl_locn_profiles
ON qMDI.Dest_Unit = tbl_locn_profiles.Dest_Unit
WHERE locn.District=[Enter District Number]
AND qMDI.Service_Level="1"
AND tbl_locn_profiles.closed_date Is Null
AND (qMDI.End_Date Is Null OR qMDI.End_Date>Now())
ORDER BY qMDI.Dest_Unit, qMDI.Shipper;


--
HTH

Dale Fye


Dale this works fine....but I still need to add other
fields from my other tables how do I add to the query you
gave me to make it complete?
From table locn I need to add attributes.
Dest_City, Dest_St, District, and Store_Type.
From table tbl_locn_profiles I need to add
closed_date.
From table shippers I need to add
ship_type.
Thank you so much for the help so Far.
-----Original Message-----
Juan, I'm still not sure entirely what you are trying to accomplish.
But I'll take a shot. I would recommend is that you create a query
(qry_max_delivery_info) to identify all the information in the
tbl_delivery_info table with the most Max([Eff_Date]).

qry_max_delivery_info:

SELECT DI.*
FROM tbl_Delivery_info DI
INNER JOIN
[SELECT Dest_Unit, MAX(Eff_Date) as Max_Eff_Date
FROM tbl_Delivery_info
GROUP BY Dest_Unit]. as T
ON DI.Dest_Unit = T.Dest_Unit
AND DI.Eff_Date = T.Max_Eff_Date

This query will return all the fields from your delivery table, but
will only return only one record for each Dest_Unit, the one with the
maximum Eff_Date. Replace all references to tbl_delivery_info in your
original query and use this query instead. Because this will
eliminate all the duplicate records based on Dest_Unit, you should be
able to eliminate the GROUP BY clause as well.

--
HTH

Dale Fye


I have four tables I use in the query.
1) Table: locn
primary key= Dest_Unit
attributes:
Dest_ST
Dest_City
ZIP_CD
STORE_TYPE
REGION
District
2) Table: tbl_locn_profiles
primary key = Dest_Unit
Attribute:
close_date
3) Table: shippers
primary key = SHIPPER
attributes:
ship_st
ship_city
ship_type
4) Table: tbl_delivery_info
Foreign Keys: Dest_Unit, SHIPPER
Attributes:
Id
Dest_Unit
SHIPPER
Eff_Date
End_Date
Service_Level
//Then it has all the delivery types and information like
what I have on the query that I sent you.
** Now what I need is all the fields that I sent you in
the original query. I need the Max Eff_Date for each
Dest_Unit.



-----Original Message-----
Juan,

Explain what you are trying to accomplish with the three tables, it
will probably be easier for us to rewrite your query than to try to
decipher what you are trying to do by looking at your SQL statement.
Describe the tables and the PK/FK fields, then let us see if we can
make some sense of it.

--
HTH

Dale Fye


I have the same problem as one of the problems stated
earlier with the most recent date. The only problem is
that I need to Join Three tables and I think this is
giving me a problem.
This is what I have so far.

SELECT tbl_delivery_info.Dest_Unit, locn.Dest_City,
locn.Dest_St, tbl_delivery_info.Shipper,
tbl_delivery_info.Del_Su_S, tbl_delivery_info.Del_Typ_Su,
tbl_delivery_info.Del_Su_E, tbl_delivery_info.Del_Mo_S,
tbl_delivery_info.Del_Typ_Mo, tbl_delivery_info.Del_Mo_E,
tbl_delivery_info.Del_Tu_S, tbl_delivery_info.Del_Typ_Tu,
tbl_delivery_info.Del_Tu_E, tbl_delivery_info.Del_We_S,
tbl_delivery_info.Del_Typ_We, tbl_delivery_info.Del_We_E,
tbl_delivery_info.Del_Th_S, tbl_delivery_info.Del_Typ_Th,
tbl_delivery_info.Del_Th_E, tbl_delivery_info.Del_Fr_S,
tbl_delivery_info.Del_Typ_Fr, tbl_delivery_info.Del_Fr_E,
tbl_delivery_info.Del_Sa_S, tbl_delivery_info.Del_Typ_Sa,
tbl_delivery_info.Del_Sa_E, locn.District,
locn.Store_Type, tbl_delivery_info.Live_Drop,
shippers.ship_type, tbl_delivery_info.Comments,
tbl_delivery_info.Recv_Alt_Ship,
tbl_delivery_info.XC_Dest_Unit,
tbl_delivery_info.XC_Ship_Unit,
tbl_delivery_info.Batch_Number,
tbl_delivery_info.Service_Level, Max
(tbl_delivery_info.Eff_Date) AS MaxOfEff_Date,
tbl_locn_profiles.closed_date, tbl_delivery_info.End_Date
FROM (shippers INNER JOIN (locn INNER JOIN
tbl_delivery_info ON locn.Dest_Unit =
tbl_delivery_info.Dest_Unit) ON shippers.SHIPPER =
tbl_delivery_info.Shipper) INNER JOIN tbl_locn_profiles ON
tbl_delivery_info.Dest_Unit = tbl_locn_profiles.Dest_Unit
GROUP BY tbl_delivery_info.Dest_Unit, locn.Dest_City,
locn.Dest_St, tbl_delivery_info.Shipper,
tbl_delivery_info.Del_Su_S, tbl_delivery_info.Del_Typ_Su,
tbl_delivery_info.Del_Su_E, tbl_delivery_info.Del_Mo_S,
tbl_delivery_info.Del_Typ_Mo, tbl_delivery_info.Del_Mo_E,
tbl_delivery_info.Del_Tu_S, tbl_delivery_info.Del_Typ_Tu,
tbl_delivery_info.Del_Tu_E, tbl_delivery_info.Del_We_S,
tbl_delivery_info.Del_Typ_We, tbl_delivery_info.Del_We_E,
tbl_delivery_info.Del_Th_S, tbl_delivery_info.Del_Typ_Th,
tbl_delivery_info.Del_Th_E, tbl_delivery_info.Del_Fr_S,
tbl_delivery_info.Del_Typ_Fr, tbl_delivery_info.Del_Fr_E,
tbl_delivery_info.Del_Sa_S, tbl_delivery_info.Del_Typ_Sa,
tbl_delivery_info.Del_Sa_E, locn.District,
locn.Store_Type, tbl_delivery_info.Live_Drop,
shippers.ship_type, tbl_delivery_info.Comments,
tbl_delivery_info.Recv_Alt_Ship,
tbl_delivery_info.XC_Dest_Unit,
tbl_delivery_info.XC_Ship_Unit,
tbl_delivery_info.Batch_Number,
tbl_delivery_info.Service_Level,
tbl_locn_profiles.closed_date, tbl_delivery_info.End_Date
HAVING (((locn.District)=[Enter District Number]) AND
((tbl_delivery_info.Service_Level)="1") AND
((tbl_locn_profiles.closed_date) Is Null) AND
((tbl_delivery_info.End_Date) Is Null Or
(tbl_delivery_info.End_Date)>Now()))
ORDER BY tbl_delivery_info.Dest_Unit,
tbl_delivery_info.Shipper;

This query still gives me two dates. I only need the most
recent date.
Can anybody tell me what I am doing wrong? I need your
help A.S.A.P. Thanks in advance.


.
Click to expand...


.
Click to expand...
 
Top