Min Value

R

Rafi

I have two tables with Tbl_A having close to a million records of shipments
out of 6 different warehouses (Origin) to multiple destinations (two columns
Origin and Destination) and Tbl_B which has the transit time for each
origin-Destination pair (three columns: Origin, Destination and Transit
(time).

What I need to do is as follows:

1) find out which of the records in table a will have a transit time over
two days
2) find out which of those records above can be shipped in two days if we
changed the origin to another warehouse)

I have two queries that do the job; however, my second query results in
multiple combination (origin destination) that meet the two days requirement.
I want to show only one record (Min) or if there are two records with an
identical transit time show the first of the two.


Step -1 Find the transit time for each pair
SELECT Q3_Shipments.ID, GND_TRANSIT.Origin, GND_TRANSIT.DEST_ZIP,
GND_TRANSIT.[SERVICE DAYS]
FROM GND_TRANSIT INNER JOIN Q3_Shipments ON (GND_TRANSIT.DEST_ZIP =
Q3_Shipments.CUST_ZIP) AND (GND_TRANSIT.Origin = Q3_Shipments.[Origin Zip])
ORDER BY GND_TRANSIT.Origin;

SELECT Q_OD_Transit_Time.ID, Q_OD_Transit_Time.Origin,
Q_OD_Transit_Time.DEST_ZIP, Q_OD_Transit_Time.[SERVICE DAYS]
FROM Q_OD_Transit_Time
WHERE (((Q_OD_Transit_Time.[SERVICE DAYS])>2));

Step 2 - Determine which is over two days
SELECT Q_OD_Transit_Time.ID, Q_OD_Transit_Time.Origin,
Q_OD_Transit_Time.DEST_ZIP, Q_OD_Transit_Time.[SERVICE DAYS]
FROM Q_OD_Transit_Time
WHERE (((Q_OD_Transit_Time.[SERVICE DAYS])>2));

Steo 3 - determine a new origin
SELECT [Q_TRANSIT OVER 2].Origin AS [Current], GND_TRANSIT.Origin AS
Proposed, [Q_TRANSIT OVER 2].DEST_ZIP, [Q_TRANSIT OVER 2].[SERVICE DAYS] AS
Current_Days, Min(GND_TRANSIT.[SERVICE DAYS]) AS Proposed_Days
FROM [Q_TRANSIT OVER 2] INNER JOIN GND_TRANSIT ON [Q_TRANSIT OVER
2].DEST_ZIP = GND_TRANSIT.DEST_ZIP
GROUP BY [Q_TRANSIT OVER 2].Origin, GND_TRANSIT.Origin, [Q_TRANSIT OVER
2].DEST_ZIP, [Q_TRANSIT OVER 2].[SERVICE DAYS]
HAVING (((Min(GND_TRANSIT.[SERVICE DAYS]))<=2));


Thanks for your help
 
P

Pat Hartman

When I want just one record returned from a query like this, I don't use
aggregate functions since they add complications. I order the query
descending on the selection criteria and use the TOP 1 predicate.
 
G

Gary Walter

Hi Rafi,

The "million records" make me hesitant to suggest
a subquery, so I might combine 2 other queries that
finds the MIN transit time/Origin for only the records "=<2,"

qryGetMinDays

SELECT
Q.ID,
Q.Origin,
Q.DEST_ZIP,
MIN(Q.[SERVICE DAYS]) AS MinDays
FROM
Q_OD_Transit_Time AS Q
WHERE
Q.[SERVICE DAYS])<=2
GROUP BY
Q.ID,
Q.Origin,
Q.DEST_ZIP;

qryGetMinDaysWithOrigin

SELECT
Q.ID,
Q.Origin,
Q.DEST_ZIP,
G.MinDays
FROM
Q_OD_Transit_Time AS Q
INNER JOIN
qryGetMinDays AS G
ON
Q.ID = G.ID
AND
Q.Origin = G.Origin
AND
Q.DEST_ZIP = G.DEST_ZIP
AND
Q.[SERVICE DAYS] = G.MinDays;


then join the ">2" query ("[Q_TRANSIT OVER 2]")
with "=<2" query ("qryGetMinDaysWithOrigin")
in an aggregate query that only gives

FIRST( [=<2].Origin)
and
FIRST([=<2].MinDays)

from the [=<2] query

grouping on


[>2]..ID,
[>2]..Origin,
[>2]..DEST_ZIP,
[>2]..[SERVICE DAYS]

Just not using HAVING should shave "hours"
off execution time (okay..I exagerated).

good luck,

gary

Rafi said:
I have two tables with Tbl_A having close to a million records of shipments
out of 6 different warehouses (Origin) to multiple destinations (two
columns
Origin and Destination) and Tbl_B which has the transit time for each
origin-Destination pair (three columns: Origin, Destination and Transit
(time).

What I need to do is as follows:

1) find out which of the records in table a will have a transit time over
two days
2) find out which of those records above can be shipped in two days if we
changed the origin to another warehouse)

I have two queries that do the job; however, my second query results in
multiple combination (origin destination) that meet the two days
requirement.
I want to show only one record (Min) or if there are two records with an
identical transit time show the first of the two.


Step -1 Find the transit time for each pair
SELECT Q3_Shipments.ID, GND_TRANSIT.Origin, GND_TRANSIT.DEST_ZIP,
GND_TRANSIT.[SERVICE DAYS]
FROM GND_TRANSIT INNER JOIN Q3_Shipments ON (GND_TRANSIT.DEST_ZIP =
Q3_Shipments.CUST_ZIP) AND (GND_TRANSIT.Origin = Q3_Shipments.[Origin
Zip])
ORDER BY GND_TRANSIT.Origin;

SELECT Q_OD_Transit_Time.ID, Q_OD_Transit_Time.Origin,
Q_OD_Transit_Time.DEST_ZIP, Q_OD_Transit_Time.[SERVICE DAYS]
FROM Q_OD_Transit_Time
WHERE (((Q_OD_Transit_Time.[SERVICE DAYS])>2));

Step 2 - Determine which is over two days
SELECT Q_OD_Transit_Time.ID, Q_OD_Transit_Time.Origin,
Q_OD_Transit_Time.DEST_ZIP, Q_OD_Transit_Time.[SERVICE DAYS]
FROM Q_OD_Transit_Time
WHERE (((Q_OD_Transit_Time.[SERVICE DAYS])>2));

Steo 3 - determine a new origin
SELECT [Q_TRANSIT OVER 2].Origin AS [Current], GND_TRANSIT.Origin AS
Proposed, [Q_TRANSIT OVER 2].DEST_ZIP, [Q_TRANSIT OVER 2].[SERVICE DAYS]
AS
Current_Days, Min(GND_TRANSIT.[SERVICE DAYS]) AS Proposed_Days
FROM [Q_TRANSIT OVER 2] INNER JOIN GND_TRANSIT ON [Q_TRANSIT OVER
2].DEST_ZIP = GND_TRANSIT.DEST_ZIP
GROUP BY [Q_TRANSIT OVER 2].Origin, GND_TRANSIT.Origin, [Q_TRANSIT OVER
2].DEST_ZIP, [Q_TRANSIT OVER 2].[SERVICE DAYS]
HAVING (((Min(GND_TRANSIT.[SERVICE DAYS]))<=2));


Thanks for your help
Click to expand...
 
R

Rafi

Gary,

Thank you very much for all your help. can I ask you to give me some more
insight on the join you are proposing as the final step

then join the ">2" query ("[Q_TRANSIT OVER 2]")
with "=<2" query ("qryGetMinDaysWithOrigin")
in an aggregate query that only gives

FIRST( [=<2].Origin)
and
FIRST([=<2].MinDays)

from the [=<2] query

grouping on


[>2]..ID,
[>2]..Origin,
[>2]..DEST_ZIP,
[>2]..[SERVICE DAYS]
Click to expand...

Thanks agian for all your help


Gary Walter said:
Hi Rafi,

The "million records" make me hesitant to suggest
a subquery, so I might combine 2 other queries that
finds the MIN transit time/Origin for only the records "=<2,"

qryGetMinDays

SELECT
Q.ID,
Q.Origin,
Q.DEST_ZIP,
MIN(Q.[SERVICE DAYS]) AS MinDays
FROM
Q_OD_Transit_Time AS Q
WHERE
Q.[SERVICE DAYS])<=2
GROUP BY
Q.ID,
Q.Origin,
Q.DEST_ZIP;

qryGetMinDaysWithOrigin

SELECT
Q.ID,
Q.Origin,
Q.DEST_ZIP,
G.MinDays
FROM
Q_OD_Transit_Time AS Q
INNER JOIN
qryGetMinDays AS G
ON
Q.ID = G.ID
AND
Q.Origin = G.Origin
AND
Q.DEST_ZIP = G.DEST_ZIP
AND
Q.[SERVICE DAYS] = G.MinDays;


then join the ">2" query ("[Q_TRANSIT OVER 2]")
with "=<2" query ("qryGetMinDaysWithOrigin")
in an aggregate query that only gives

FIRST( [=<2].Origin)
and
FIRST([=<2].MinDays)

from the [=<2] query

grouping on


[>2]..ID,
[>2]..Origin,
[>2]..DEST_ZIP,
[>2]..[SERVICE DAYS]

Just not using HAVING should shave "hours"
off execution time (okay..I exagerated).

good luck,

gary

Rafi said:
I have two tables with Tbl_A having close to a million records of shipments
out of 6 different warehouses (Origin) to multiple destinations (two
columns
Origin and Destination) and Tbl_B which has the transit time for each
origin-Destination pair (three columns: Origin, Destination and Transit
(time).

What I need to do is as follows:

1) find out which of the records in table a will have a transit time over
two days
2) find out which of those records above can be shipped in two days if we
changed the origin to another warehouse)

I have two queries that do the job; however, my second query results in
multiple combination (origin destination) that meet the two days
requirement.
I want to show only one record (Min) or if there are two records with an
identical transit time show the first of the two.


Step -1 Find the transit time for each pair
SELECT Q3_Shipments.ID, GND_TRANSIT.Origin, GND_TRANSIT.DEST_ZIP,
GND_TRANSIT.[SERVICE DAYS]
FROM GND_TRANSIT INNER JOIN Q3_Shipments ON (GND_TRANSIT.DEST_ZIP =
Q3_Shipments.CUST_ZIP) AND (GND_TRANSIT.Origin = Q3_Shipments.[Origin
Zip])
ORDER BY GND_TRANSIT.Origin;

SELECT Q_OD_Transit_Time.ID, Q_OD_Transit_Time.Origin,
Q_OD_Transit_Time.DEST_ZIP, Q_OD_Transit_Time.[SERVICE DAYS]
FROM Q_OD_Transit_Time
WHERE (((Q_OD_Transit_Time.[SERVICE DAYS])>2));

Step 2 - Determine which is over two days
SELECT Q_OD_Transit_Time.ID, Q_OD_Transit_Time.Origin,
Q_OD_Transit_Time.DEST_ZIP, Q_OD_Transit_Time.[SERVICE DAYS]
FROM Q_OD_Transit_Time
WHERE (((Q_OD_Transit_Time.[SERVICE DAYS])>2));

Steo 3 - determine a new origin
SELECT [Q_TRANSIT OVER 2].Origin AS [Current], GND_TRANSIT.Origin AS
Proposed, [Q_TRANSIT OVER 2].DEST_ZIP, [Q_TRANSIT OVER 2].[SERVICE DAYS]
AS
Current_Days, Min(GND_TRANSIT.[SERVICE DAYS]) AS Proposed_Days
FROM [Q_TRANSIT OVER 2] INNER JOIN GND_TRANSIT ON [Q_TRANSIT OVER
2].DEST_ZIP = GND_TRANSIT.DEST_ZIP
GROUP BY [Q_TRANSIT OVER 2].Origin, GND_TRANSIT.Origin, [Q_TRANSIT OVER
2].DEST_ZIP, [Q_TRANSIT OVER 2].[SERVICE DAYS]
HAVING (((Min(GND_TRANSIT.[SERVICE DAYS]))<=2));


Thanks for your help
Click to expand...
Click to expand...
 
G

Gary Walter

Hi Rafi,

I'd be glad to help further, but I would first
really like to know type of field "ID."

Is "ID" an AutoNumber (or IDENTITY) Primary Key?

Is Q3_Shipments a query that limits you to certain
shipments (say over a specific time frame -- like 3rd quarter)?

So, in the end, you want to show *every single record*
from Q3_Shipments that had a transit time > 2
(with an identity ID involved, there may be more than one record
for an Origin/DEST_ZIP group but different ID's)
but where a different Origin to the same DEST_ZIP got there
quicker...and you only want to know the quickest Origin.

The reason I ask is that I wonder if Q3_Shipments.ID
should not be in some of the queries.

{untested!}

qryGT2
--------
SELECT
Q.ID,
Q.Origin AS GT2Origin,
Q.DEST_ZIP AS GT2Dest,
Q.[SERVICE DAYS] AS GT2Days
FROM
Q_OD_Transit_Time AS Q
WHERE
Q.[SERVICE DAYS] >2;

qryMinTransitLTEQ2
---------------------
(no ID, just getting Min Transit time
for each *Origin/DEST_ZIP group*
where transit time <= 2 days)

SELECT
Q.Origin,
Q.DEST_ZIP,
MIN(Q.[SERVICE DAYS]) AS MinDays
FROM
Q_OD_Transit_Time AS Q
WHERE
Q.[SERVICE DAYS])<=2
GROUP BY
Q.Origin,
Q.DEST_ZIP;

qryOriginDestLTEQ2
-----------------------
(get *all* Origin/Dest records
that meet Min Transit time)

SELECT
Q.Origin AS LTEQ2Origin,
Q.DEST_ZIP As LTEQ2Dest,
M.MinDays
FROM
Q_OD_Transit_Time AS Q
INNER JOIN
qryMinTransitLTEQ2 AS M
ON
Q.Origin = M.Origin
AND
Q.DEST_ZIP = M.DEST_ZIP
AND
Q.[SERVICE DAYS] = M.MinDays;

qryFinal
--------
(show *all* the Q3 records GT 2,
but only one quickest Origin,
*if* there is a match between Dest's)

SELECT
Q1.ID,
Q1.GT2Origin,
Q1.GT2Dest,
Q1.GT2Days,
First(Q2.LTEQ2Origin) As QuickestOrigin,
First(Q2.MinDays) As QuickestTransitTime
FROM
qryGT2 AS Q1
INNER JOIN
qryOriginDestLTEQ2 AS Q2
ON
Q1.GT2Dest = Q2.LTEQ2Dest
GROUP BY
Q1.ID,
Q1.GT2Origin,
Q1.GT2Dest,
Q1.GT2Days;

Apologies if misunderstood...

gary




Rafi said:
Thank you very much for all your help. can I ask you to give me some more
insight on the join you are proposing as the final step

then join the ">2" query ("[Q_TRANSIT OVER 2]")
with "=<2" query ("qryGetMinDaysWithOrigin")
in an aggregate query that only gives

FIRST( [=<2].Origin)
and
FIRST([=<2].MinDays)

from the [=<2] query

grouping on


[>2]..ID,
[>2]..Origin,
[>2]..DEST_ZIP,
[>2]..[SERVICE DAYS]
Click to expand...

Thanks agian for all your help


Gary Walter said:
Hi Rafi,

The "million records" make me hesitant to suggest
a subquery, so I might combine 2 other queries that
finds the MIN transit time/Origin for only the records "=<2,"

qryGetMinDays

SELECT
Q.ID,
Q.Origin,
Q.DEST_ZIP,
MIN(Q.[SERVICE DAYS]) AS MinDays
FROM
Q_OD_Transit_Time AS Q
WHERE
Q.[SERVICE DAYS])<=2
GROUP BY
Q.ID,
Q.Origin,
Q.DEST_ZIP;

qryGetMinDaysWithOrigin

SELECT
Q.ID,
Q.Origin,
Q.DEST_ZIP,
G.MinDays
FROM
Q_OD_Transit_Time AS Q
INNER JOIN
qryGetMinDays AS G
ON
Q.ID = G.ID
AND
Q.Origin = G.Origin
AND
Q.DEST_ZIP = G.DEST_ZIP
AND
Q.[SERVICE DAYS] = G.MinDays;


then join the ">2" query ("[Q_TRANSIT OVER 2]")
with "=<2" query ("qryGetMinDaysWithOrigin")
in an aggregate query that only gives

FIRST( [=<2].Origin)
and
FIRST([=<2].MinDays)

from the [=<2] query

grouping on


[>2]..ID,
[>2]..Origin,
[>2]..DEST_ZIP,
[>2]..[SERVICE DAYS]

Just not using HAVING should shave "hours"
off execution time (okay..I exagerated).

good luck,

gary

Rafi said:
I have two tables with Tbl_A having close to a million records of
shipments
out of 6 different warehouses (Origin) to multiple destinations (two
columns
Origin and Destination) and Tbl_B which has the transit time for each
origin-Destination pair (three columns: Origin, Destination and Transit
(time).

What I need to do is as follows:

1) find out which of the records in table a will have a transit time
over
two days
2) find out which of those records above can be shipped in two days if
we
changed the origin to another warehouse)

I have two queries that do the job; however, my second query results in
multiple combination (origin destination) that meet the two days
requirement.
I want to show only one record (Min) or if there are two records with
an
identical transit time show the first of the two.


Step -1 Find the transit time for each pair
SELECT Q3_Shipments.ID, GND_TRANSIT.Origin, GND_TRANSIT.DEST_ZIP,
GND_TRANSIT.[SERVICE DAYS]
FROM GND_TRANSIT INNER JOIN Q3_Shipments ON (GND_TRANSIT.DEST_ZIP =
Q3_Shipments.CUST_ZIP) AND (GND_TRANSIT.Origin = Q3_Shipments.[Origin
Zip])
ORDER BY GND_TRANSIT.Origin;

SELECT Q_OD_Transit_Time.ID, Q_OD_Transit_Time.Origin,
Q_OD_Transit_Time.DEST_ZIP, Q_OD_Transit_Time.[SERVICE DAYS]
FROM Q_OD_Transit_Time
WHERE (((Q_OD_Transit_Time.[SERVICE DAYS])>2));

Step 2 - Determine which is over two days
SELECT Q_OD_Transit_Time.ID, Q_OD_Transit_Time.Origin,
Q_OD_Transit_Time.DEST_ZIP, Q_OD_Transit_Time.[SERVICE DAYS]
FROM Q_OD_Transit_Time
WHERE (((Q_OD_Transit_Time.[SERVICE DAYS])>2));

Steo 3 - determine a new origin
SELECT [Q_TRANSIT OVER 2].Origin AS [Current], GND_TRANSIT.Origin AS
Proposed, [Q_TRANSIT OVER 2].DEST_ZIP, [Q_TRANSIT OVER 2].[SERVICE
DAYS]
AS
Current_Days, Min(GND_TRANSIT.[SERVICE DAYS]) AS Proposed_Days
FROM [Q_TRANSIT OVER 2] INNER JOIN GND_TRANSIT ON [Q_TRANSIT OVER
2].DEST_ZIP = GND_TRANSIT.DEST_ZIP
GROUP BY [Q_TRANSIT OVER 2].Origin, GND_TRANSIT.Origin, [Q_TRANSIT OVER
2].DEST_ZIP, [Q_TRANSIT OVER 2].[SERVICE DAYS]
HAVING (((Min(GND_TRANSIT.[SERVICE DAYS]))<=2));


Thanks for your help
Click to expand...
Click to expand...
Click to expand...
 
R

Rafi

Gary,

Thanks again for all your help. ID is an autonumber in the Q3_Shipments
table and you are on the money with regards to my goal with this query, I
will test the code below and let you know however, many thanks in the
meantime.

Gary Walter said:
Hi Rafi,

I'd be glad to help further, but I would first
really like to know type of field "ID."

Is "ID" an AutoNumber (or IDENTITY) Primary Key?

Is Q3_Shipments a query that limits you to certain
shipments (say over a specific time frame -- like 3rd quarter)?

So, in the end, you want to show *every single record*
from Q3_Shipments that had a transit time > 2
(with an identity ID involved, there may be more than one record
for an Origin/DEST_ZIP group but different ID's)
but where a different Origin to the same DEST_ZIP got there
quicker...and you only want to know the quickest Origin.

The reason I ask is that I wonder if Q3_Shipments.ID
should not be in some of the queries.

{untested!}

qryGT2
--------
SELECT
Q.ID,
Q.Origin AS GT2Origin,
Q.DEST_ZIP AS GT2Dest,
Q.[SERVICE DAYS] AS GT2Days
FROM
Q_OD_Transit_Time AS Q
WHERE
Q.[SERVICE DAYS] >2;

qryMinTransitLTEQ2
---------------------
(no ID, just getting Min Transit time
for each *Origin/DEST_ZIP group*
where transit time <= 2 days)

SELECT
Q.Origin,
Q.DEST_ZIP,
MIN(Q.[SERVICE DAYS]) AS MinDays
FROM
Q_OD_Transit_Time AS Q
WHERE
Q.[SERVICE DAYS])<=2
GROUP BY
Q.Origin,
Q.DEST_ZIP;

qryOriginDestLTEQ2
-----------------------
(get *all* Origin/Dest records
that meet Min Transit time)

SELECT
Q.Origin AS LTEQ2Origin,
Q.DEST_ZIP As LTEQ2Dest,
M.MinDays
FROM
Q_OD_Transit_Time AS Q
INNER JOIN
qryMinTransitLTEQ2 AS M
ON
Q.Origin = M.Origin
AND
Q.DEST_ZIP = M.DEST_ZIP
AND
Q.[SERVICE DAYS] = M.MinDays;

qryFinal
--------
(show *all* the Q3 records GT 2,
but only one quickest Origin,
*if* there is a match between Dest's)

SELECT
Q1.ID,
Q1.GT2Origin,
Q1.GT2Dest,
Q1.GT2Days,
First(Q2.LTEQ2Origin) As QuickestOrigin,
First(Q2.MinDays) As QuickestTransitTime
FROM
qryGT2 AS Q1
INNER JOIN
qryOriginDestLTEQ2 AS Q2
ON
Q1.GT2Dest = Q2.LTEQ2Dest
GROUP BY
Q1.ID,
Q1.GT2Origin,
Q1.GT2Dest,
Q1.GT2Days;

Apologies if misunderstood...

gary




Rafi said:
Thank you very much for all your help. can I ask you to give me some more
insight on the join you are proposing as the final step

then join the ">2" query ("[Q_TRANSIT OVER 2]")
with "=<2" query ("qryGetMinDaysWithOrigin")
in an aggregate query that only gives

FIRST( [=<2].Origin)
and
FIRST([=<2].MinDays)

from the [=<2] query

grouping on


[>2]..ID,
[>2]..Origin,
[>2]..DEST_ZIP,
[>2]..[SERVICE DAYS]
Click to expand...

Thanks agian for all your help


Gary Walter said:
Hi Rafi,

The "million records" make me hesitant to suggest
a subquery, so I might combine 2 other queries that
finds the MIN transit time/Origin for only the records "=<2,"

qryGetMinDays

SELECT
Q.ID,
Q.Origin,
Q.DEST_ZIP,
MIN(Q.[SERVICE DAYS]) AS MinDays
FROM
Q_OD_Transit_Time AS Q
WHERE
Q.[SERVICE DAYS])<=2
GROUP BY
Q.ID,
Q.Origin,
Q.DEST_ZIP;

qryGetMinDaysWithOrigin

SELECT
Q.ID,
Q.Origin,
Q.DEST_ZIP,
G.MinDays
FROM
Q_OD_Transit_Time AS Q
INNER JOIN
qryGetMinDays AS G
ON
Q.ID = G.ID
AND
Q.Origin = G.Origin
AND
Q.DEST_ZIP = G.DEST_ZIP
AND
Q.[SERVICE DAYS] = G.MinDays;


then join the ">2" query ("[Q_TRANSIT OVER 2]")
with "=<2" query ("qryGetMinDaysWithOrigin")
in an aggregate query that only gives

FIRST( [=<2].Origin)
and
FIRST([=<2].MinDays)

from the [=<2] query

grouping on


[>2]..ID,
[>2]..Origin,
[>2]..DEST_ZIP,
[>2]..[SERVICE DAYS]

Just not using HAVING should shave "hours"
off execution time (okay..I exagerated).

good luck,

gary

:
I have two tables with Tbl_A having close to a million records of
shipments
out of 6 different warehouses (Origin) to multiple destinations (two
columns
Origin and Destination) and Tbl_B which has the transit time for each
origin-Destination pair (three columns: Origin, Destination and Transit
(time).

What I need to do is as follows:

1) find out which of the records in table a will have a transit time
over
two days
2) find out which of those records above can be shipped in two days if
we
changed the origin to another warehouse)

I have two queries that do the job; however, my second query results in
multiple combination (origin destination) that meet the two days
requirement.
I want to show only one record (Min) or if there are two records with
an
identical transit time show the first of the two.


Step -1 Find the transit time for each pair
SELECT Q3_Shipments.ID, GND_TRANSIT.Origin, GND_TRANSIT.DEST_ZIP,
GND_TRANSIT.[SERVICE DAYS]
FROM GND_TRANSIT INNER JOIN Q3_Shipments ON (GND_TRANSIT.DEST_ZIP =
Q3_Shipments.CUST_ZIP) AND (GND_TRANSIT.Origin = Q3_Shipments.[Origin
Zip])
ORDER BY GND_TRANSIT.Origin;

SELECT Q_OD_Transit_Time.ID, Q_OD_Transit_Time.Origin,
Q_OD_Transit_Time.DEST_ZIP, Q_OD_Transit_Time.[SERVICE DAYS]
FROM Q_OD_Transit_Time
WHERE (((Q_OD_Transit_Time.[SERVICE DAYS])>2));

Step 2 - Determine which is over two days
SELECT Q_OD_Transit_Time.ID, Q_OD_Transit_Time.Origin,
Q_OD_Transit_Time.DEST_ZIP, Q_OD_Transit_Time.[SERVICE DAYS]
FROM Q_OD_Transit_Time
WHERE (((Q_OD_Transit_Time.[SERVICE DAYS])>2));

Steo 3 - determine a new origin
SELECT [Q_TRANSIT OVER 2].Origin AS [Current], GND_TRANSIT.Origin AS
Proposed, [Q_TRANSIT OVER 2].DEST_ZIP, [Q_TRANSIT OVER 2].[SERVICE
DAYS]
AS
Current_Days, Min(GND_TRANSIT.[SERVICE DAYS]) AS Proposed_Days
FROM [Q_TRANSIT OVER 2] INNER JOIN GND_TRANSIT ON [Q_TRANSIT OVER
2].DEST_ZIP = GND_TRANSIT.DEST_ZIP
GROUP BY [Q_TRANSIT OVER 2].Origin, GND_TRANSIT.Origin, [Q_TRANSIT OVER
2].DEST_ZIP, [Q_TRANSIT OVER 2].[SERVICE DAYS]
HAVING (((Min(GND_TRANSIT.[SERVICE DAYS]))<=2));


Thanks for your help
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Select min value 1
Assembling large "array" 3
vb for record comparison 2
Workspaces, pointers, etc. 1
Moving from xBase/Clipper 1
Can someone please help me create a data base for a shipping compa 1
relationship 4
Fast & Easy Program 0

Top