adding criteria to limit results

L

Loni - RWT

I have created a database to keep mileage/trip records for a small trucking
company. Each trip (tblTrips) can have multiple entries (tblTripDetails)
with mileage descriptions such as crossing state lines, loading/unloading,
fueling, and trip end. I had a set of cascading queries that first pulled
all odometer entries with a mileage description of "trip end" for a specific
truck. It then selected the maximum odometer entry from those records and
returned the matching location and state. That worked fine until the
occasion arose when a truck had to be towed by a wrecker. We added an
additional mileage description, "wrecker," to handle this. In that instance,
we had a "trip end" mileage description with an odometer entry at one
location and a seperate trip with a "wrecker" mileage description at a
different location, but the same odometer. I modified my queries to include
"wrecker" along with "trip end." That appeared to work, as it pulled all
odometer entries with those mileage descriptions. However, when it selects
the maximum odometer entry from those records, it is returning both "trip
end" and "wrecker" since they have the same odometer. It is highly possible
(in this instance it happened) that the "trip end" and the "wrecker" trips
occured on the same date. The one unique identifier is the TripDetailsID.
When I try to pull the max of these, it still returns both records. How do I
establish a criteria to separate the two?

Thanks in advance for your help!

Posted below is the SQL for these queries.

qryTripEnd1:
SELECT tblTrips.TripID, tblTrips.TripDate, tblTrips.TruckID,
tblTripDetails.MileageDescription, tblTripDetails.Odometer
FROM tblTrips INNER JOIN tblTripDetails ON tblTrips.TripID =
tblTripDetails.TripID
WHERE (((tblTrips.TripID)<[Forms]![frmTrips]![TripID]) AND
((tblTrips.TripDate)<=[Forms]![frmTrips]![TripDate]) AND
((tblTrips.TruckID)=[Forms]![frmTrips]![truckID]) AND
((tblTripDetails.MileageDescription)="Trip End")) OR
(((tblTrips.TripID)<[Forms]![frmTrips]![TripID]) AND
((tblTrips.TripDate)<=[Forms]![frmTrips]![TripDate]) AND
((tblTrips.TruckID)=[Forms]![frmTrips]![truckID]) AND
((tblTripDetails.MileageDescription)="Wrecker"))
ORDER BY tblTripDetails.Odometer;

qryTripEnd2:
SELECT qryTripEnd1.TruckID, qryTripEnd1.MileageDescription,
Max(qryTripEnd1.Odometer) AS MaxOfOdometer
FROM qryTripEnd1 INNER JOIN tblTripDetails ON qryTripEnd1.Odometer =
tblTripDetails.Odometer
GROUP BY qryTripEnd1.TruckID, qryTripEnd1.MileageDescription;

qryTripEnd2A:
SELECT qryTripEnd2.MaxOfOdometer, qryTripEnd2.MileageDescription,
tblTripDetails.TripID, tblTripDetails.TripDetailsID
FROM qryTripEnd2 INNER JOIN tblTripDetails ON
(qryTripEnd2.MileageDescription = tblTripDetails.MileageDescription) AND
(qryTripEnd2.MaxOfOdometer = tblTripDetails.Odometer);

qryTripEnd2B:
SELECT qryTripEnd2A.MaxOfOdometer, qryTripEnd2A.TripID,
Max(qryTripEnd2A.TripDetailsID) AS MaxOfTripDetailsID, tblTrips.TripDate
FROM qryTripEnd2A INNER JOIN tblTrips ON qryTripEnd2A.TripID = tblTrips.TripID
GROUP BY qryTripEnd2A.MaxOfOdometer, qryTripEnd2A.MileageDescription,
qryTripEnd2A.TripID, tblTrips.TripDate;

qryTripEnd3:
SELECT qryTripEnd2B.MileageDescription, tblTripDetails.Odometer,
tblTripDetails.TripDetailsID, tblTripDetails.Location, tblTripDetails.State
FROM tblTripDetails INNER JOIN qryTripEnd2B ON (qryTripEnd2B.TripID =
tblTripDetails.TripID) AND (tblTripDetails.MileageDescription =
qryTripEnd2B.MileageDescription) AND (tblTripDetails.Odometer =
qryTripEnd2B.MaxOfOdometer);
 
M

[MVP] S.Clark

I think I would throw out the white flag, and add a tenth of a mile to the
post-wrecker starting mileage.

--
Steve Clark, Access MVP
http://www.fmsinc.com/consulting
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

Loni - RWT said:
I have created a database to keep mileage/trip records for a small trucking
company. Each trip (tblTrips) can have multiple entries (tblTripDetails)
with mileage descriptions such as crossing state lines, loading/unloading,
fueling, and trip end. I had a set of cascading queries that first pulled
all odometer entries with a mileage description of "trip end" for a
specific
truck. It then selected the maximum odometer entry from those records and
returned the matching location and state. That worked fine until the
occasion arose when a truck had to be towed by a wrecker. We added an
additional mileage description, "wrecker," to handle this. In that
instance,
we had a "trip end" mileage description with an odometer entry at one
location and a seperate trip with a "wrecker" mileage description at a
different location, but the same odometer. I modified my queries to
include
"wrecker" along with "trip end." That appeared to work, as it pulled all
odometer entries with those mileage descriptions. However, when it
selects
the maximum odometer entry from those records, it is returning both "trip
end" and "wrecker" since they have the same odometer. It is highly
possible
(in this instance it happened) that the "trip end" and the "wrecker" trips
occured on the same date. The one unique identifier is the
TripDetailsID.
When I try to pull the max of these, it still returns both records. How
do I
establish a criteria to separate the two?

Thanks in advance for your help!

Posted below is the SQL for these queries.

qryTripEnd1:
SELECT tblTrips.TripID, tblTrips.TripDate, tblTrips.TruckID,
tblTripDetails.MileageDescription, tblTripDetails.Odometer
FROM tblTrips INNER JOIN tblTripDetails ON tblTrips.TripID =
tblTripDetails.TripID
WHERE (((tblTrips.TripID)<[Forms]![frmTrips]![TripID]) AND
((tblTrips.TripDate)<=[Forms]![frmTrips]![TripDate]) AND
((tblTrips.TruckID)=[Forms]![frmTrips]![truckID]) AND
((tblTripDetails.MileageDescription)="Trip End")) OR
(((tblTrips.TripID)<[Forms]![frmTrips]![TripID]) AND
((tblTrips.TripDate)<=[Forms]![frmTrips]![TripDate]) AND
((tblTrips.TruckID)=[Forms]![frmTrips]![truckID]) AND
((tblTripDetails.MileageDescription)="Wrecker"))
ORDER BY tblTripDetails.Odometer;

qryTripEnd2:
SELECT qryTripEnd1.TruckID, qryTripEnd1.MileageDescription,
Max(qryTripEnd1.Odometer) AS MaxOfOdometer
FROM qryTripEnd1 INNER JOIN tblTripDetails ON qryTripEnd1.Odometer =
tblTripDetails.Odometer
GROUP BY qryTripEnd1.TruckID, qryTripEnd1.MileageDescription;

qryTripEnd2A:
SELECT qryTripEnd2.MaxOfOdometer, qryTripEnd2.MileageDescription,
tblTripDetails.TripID, tblTripDetails.TripDetailsID
FROM qryTripEnd2 INNER JOIN tblTripDetails ON
(qryTripEnd2.MileageDescription = tblTripDetails.MileageDescription) AND
(qryTripEnd2.MaxOfOdometer = tblTripDetails.Odometer);

qryTripEnd2B:
SELECT qryTripEnd2A.MaxOfOdometer, qryTripEnd2A.TripID,
Max(qryTripEnd2A.TripDetailsID) AS MaxOfTripDetailsID, tblTrips.TripDate
FROM qryTripEnd2A INNER JOIN tblTrips ON qryTripEnd2A.TripID =
tblTrips.TripID
GROUP BY qryTripEnd2A.MaxOfOdometer, qryTripEnd2A.MileageDescription,
qryTripEnd2A.TripID, tblTrips.TripDate;

qryTripEnd3:
SELECT qryTripEnd2B.MileageDescription, tblTripDetails.Odometer,
tblTripDetails.TripDetailsID, tblTripDetails.Location,
tblTripDetails.State
FROM tblTripDetails INNER JOIN qryTripEnd2B ON (qryTripEnd2B.TripID =
tblTripDetails.TripID) AND (tblTripDetails.MileageDescription =
qryTripEnd2B.MileageDescription) AND (tblTripDetails.Odometer =
qryTripEnd2B.MaxOfOdometer);
Click to expand...
 
O

OfficeDev18 via AccessMonster.com

Sorry to burst your bubble, but what you're doing is making the exception
make requirements for the rule. I would never have made any change whatever,
simply take the trip end and odometer reading at the point the wrecker
entered the picture, and add a comment field (or memo field) to the table and
describe there what happened, if necessary. Why re-invent the wheel?

Make sense?
I have created a database to keep mileage/trip records for a small trucking
company. Each trip (tblTrips) can have multiple entries (tblTripDetails)
with mileage descriptions such as crossing state lines, loading/unloading,
fueling, and trip end. I had a set of cascading queries that first pulled
all odometer entries with a mileage description of "trip end" for a specific
truck. It then selected the maximum odometer entry from those records and
returned the matching location and state. That worked fine until the
occasion arose when a truck had to be towed by a wrecker. We added an
additional mileage description, "wrecker," to handle this. In that instance,
we had a "trip end" mileage description with an odometer entry at one
location and a seperate trip with a "wrecker" mileage description at a
different location, but the same odometer. I modified my queries to include
"wrecker" along with "trip end." That appeared to work, as it pulled all
odometer entries with those mileage descriptions. However, when it selects
the maximum odometer entry from those records, it is returning both "trip
end" and "wrecker" since they have the same odometer. It is highly possible
(in this instance it happened) that the "trip end" and the "wrecker" trips
occured on the same date. The one unique identifier is the TripDetailsID.
When I try to pull the max of these, it still returns both records. How do I
establish a criteria to separate the two?

Thanks in advance for your help!

Posted below is the SQL for these queries.

qryTripEnd1:
SELECT tblTrips.TripID, tblTrips.TripDate, tblTrips.TruckID,
tblTripDetails.MileageDescription, tblTripDetails.Odometer
FROM tblTrips INNER JOIN tblTripDetails ON tblTrips.TripID =
tblTripDetails.TripID
WHERE (((tblTrips.TripID)<[Forms]![frmTrips]![TripID]) AND
((tblTrips.TripDate)<=[Forms]![frmTrips]![TripDate]) AND
((tblTrips.TruckID)=[Forms]![frmTrips]![truckID]) AND
((tblTripDetails.MileageDescription)="Trip End")) OR
(((tblTrips.TripID)<[Forms]![frmTrips]![TripID]) AND
((tblTrips.TripDate)<=[Forms]![frmTrips]![TripDate]) AND
((tblTrips.TruckID)=[Forms]![frmTrips]![truckID]) AND
((tblTripDetails.MileageDescription)="Wrecker"))
ORDER BY tblTripDetails.Odometer;

qryTripEnd2:
SELECT qryTripEnd1.TruckID, qryTripEnd1.MileageDescription,
Max(qryTripEnd1.Odometer) AS MaxOfOdometer
FROM qryTripEnd1 INNER JOIN tblTripDetails ON qryTripEnd1.Odometer =
tblTripDetails.Odometer
GROUP BY qryTripEnd1.TruckID, qryTripEnd1.MileageDescription;

qryTripEnd2A:
SELECT qryTripEnd2.MaxOfOdometer, qryTripEnd2.MileageDescription,
tblTripDetails.TripID, tblTripDetails.TripDetailsID
FROM qryTripEnd2 INNER JOIN tblTripDetails ON
(qryTripEnd2.MileageDescription = tblTripDetails.MileageDescription) AND
(qryTripEnd2.MaxOfOdometer = tblTripDetails.Odometer);

qryTripEnd2B:
SELECT qryTripEnd2A.MaxOfOdometer, qryTripEnd2A.TripID,
Max(qryTripEnd2A.TripDetailsID) AS MaxOfTripDetailsID, tblTrips.TripDate
FROM qryTripEnd2A INNER JOIN tblTrips ON qryTripEnd2A.TripID = tblTrips.TripID
GROUP BY qryTripEnd2A.MaxOfOdometer, qryTripEnd2A.MileageDescription,
qryTripEnd2A.TripID, tblTrips.TripDate;

qryTripEnd3:
SELECT qryTripEnd2B.MileageDescription, tblTripDetails.Odometer,
tblTripDetails.TripDetailsID, tblTripDetails.Location, tblTripDetails.State
FROM tblTripDetails INNER JOIN qryTripEnd2B ON (qryTripEnd2B.TripID =
tblTripDetails.TripID) AND (tblTripDetails.MileageDescription =
qryTripEnd2B.MileageDescription) AND (tblTripDetails.Odometer =
qryTripEnd2B.MaxOfOdometer);
Click to expand...
 
L

Loni - RWT

Thanks for your reply. I rethought the situation and started with the most
recent trip and then pulled the ending location for that trip which works
fine since "wrecker' and "trip end" will never be an issue.


OfficeDev18 via AccessMonster.com said:
Sorry to burst your bubble, but what you're doing is making the exception
make requirements for the rule. I would never have made any change whatever,
simply take the trip end and odometer reading at the point the wrecker
entered the picture, and add a comment field (or memo field) to the table and
describe there what happened, if necessary. Why re-invent the wheel?

Make sense?
I have created a database to keep mileage/trip records for a small trucking
company. Each trip (tblTrips) can have multiple entries (tblTripDetails)
with mileage descriptions such as crossing state lines, loading/unloading,
fueling, and trip end. I had a set of cascading queries that first pulled
all odometer entries with a mileage description of "trip end" for a specific
truck. It then selected the maximum odometer entry from those records and
returned the matching location and state. That worked fine until the
occasion arose when a truck had to be towed by a wrecker. We added an
additional mileage description, "wrecker," to handle this. In that instance,
we had a "trip end" mileage description with an odometer entry at one
location and a seperate trip with a "wrecker" mileage description at a
different location, but the same odometer. I modified my queries to include
"wrecker" along with "trip end." That appeared to work, as it pulled all
odometer entries with those mileage descriptions. However, when it selects
the maximum odometer entry from those records, it is returning both "trip
end" and "wrecker" since they have the same odometer. It is highly possible
(in this instance it happened) that the "trip end" and the "wrecker" trips
occured on the same date. The one unique identifier is the TripDetailsID.
When I try to pull the max of these, it still returns both records. How do I
establish a criteria to separate the two?

Thanks in advance for your help!

Posted below is the SQL for these queries.

qryTripEnd1:
SELECT tblTrips.TripID, tblTrips.TripDate, tblTrips.TruckID,
tblTripDetails.MileageDescription, tblTripDetails.Odometer
FROM tblTrips INNER JOIN tblTripDetails ON tblTrips.TripID =
tblTripDetails.TripID
WHERE (((tblTrips.TripID)<[Forms]![frmTrips]![TripID]) AND
((tblTrips.TripDate)<=[Forms]![frmTrips]![TripDate]) AND
((tblTrips.TruckID)=[Forms]![frmTrips]![truckID]) AND
((tblTripDetails.MileageDescription)="Trip End")) OR
(((tblTrips.TripID)<[Forms]![frmTrips]![TripID]) AND
((tblTrips.TripDate)<=[Forms]![frmTrips]![TripDate]) AND
((tblTrips.TruckID)=[Forms]![frmTrips]![truckID]) AND
((tblTripDetails.MileageDescription)="Wrecker"))
ORDER BY tblTripDetails.Odometer;

qryTripEnd2:
SELECT qryTripEnd1.TruckID, qryTripEnd1.MileageDescription,
Max(qryTripEnd1.Odometer) AS MaxOfOdometer
FROM qryTripEnd1 INNER JOIN tblTripDetails ON qryTripEnd1.Odometer =
tblTripDetails.Odometer
GROUP BY qryTripEnd1.TruckID, qryTripEnd1.MileageDescription;

qryTripEnd2A:
SELECT qryTripEnd2.MaxOfOdometer, qryTripEnd2.MileageDescription,
tblTripDetails.TripID, tblTripDetails.TripDetailsID
FROM qryTripEnd2 INNER JOIN tblTripDetails ON
(qryTripEnd2.MileageDescription = tblTripDetails.MileageDescription) AND
(qryTripEnd2.MaxOfOdometer = tblTripDetails.Odometer);

qryTripEnd2B:
SELECT qryTripEnd2A.MaxOfOdometer, qryTripEnd2A.TripID,
Max(qryTripEnd2A.TripDetailsID) AS MaxOfTripDetailsID, tblTrips.TripDate
FROM qryTripEnd2A INNER JOIN tblTrips ON qryTripEnd2A.TripID = tblTrips.TripID
GROUP BY qryTripEnd2A.MaxOfOdometer, qryTripEnd2A.MileageDescription,
qryTripEnd2A.TripID, tblTrips.TripDate;

qryTripEnd3:
SELECT qryTripEnd2B.MileageDescription, tblTripDetails.Odometer,
tblTripDetails.TripDetailsID, tblTripDetails.Location, tblTripDetails.State
FROM tblTripDetails INNER JOIN qryTripEnd2B ON (qryTripEnd2B.TripID =
tblTripDetails.TripID) AND (tblTripDetails.MileageDescription =
qryTripEnd2B.MileageDescription) AND (tblTripDetails.Odometer =
qryTripEnd2B.MaxOfOdometer);
Click to expand...
Click to expand...
 
L

Loni - RWT

Thanks for your reply. I rethought the situation and started with the most
recent trip and then pulled the ending location for that trip which works
fine since "wrecker' and "trip end" will never be an issue.

[MVP] S.Clark said:
I think I would throw out the white flag, and add a tenth of a mile to the
post-wrecker starting mileage.

--
Steve Clark, Access MVP
http://www.fmsinc.com/consulting
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

Loni - RWT said:
I have created a database to keep mileage/trip records for a small trucking
company. Each trip (tblTrips) can have multiple entries (tblTripDetails)
with mileage descriptions such as crossing state lines, loading/unloading,
fueling, and trip end. I had a set of cascading queries that first pulled
all odometer entries with a mileage description of "trip end" for a
specific
truck. It then selected the maximum odometer entry from those records and
returned the matching location and state. That worked fine until the
occasion arose when a truck had to be towed by a wrecker. We added an
additional mileage description, "wrecker," to handle this. In that
instance,
we had a "trip end" mileage description with an odometer entry at one
location and a seperate trip with a "wrecker" mileage description at a
different location, but the same odometer. I modified my queries to
include
"wrecker" along with "trip end." That appeared to work, as it pulled all
odometer entries with those mileage descriptions. However, when it
selects
the maximum odometer entry from those records, it is returning both "trip
end" and "wrecker" since they have the same odometer. It is highly
possible
(in this instance it happened) that the "trip end" and the "wrecker" trips
occured on the same date. The one unique identifier is the
TripDetailsID.
When I try to pull the max of these, it still returns both records. How
do I
establish a criteria to separate the two?

Thanks in advance for your help!

Posted below is the SQL for these queries.

qryTripEnd1:
SELECT tblTrips.TripID, tblTrips.TripDate, tblTrips.TruckID,
tblTripDetails.MileageDescription, tblTripDetails.Odometer
FROM tblTrips INNER JOIN tblTripDetails ON tblTrips.TripID =
tblTripDetails.TripID
WHERE (((tblTrips.TripID)<[Forms]![frmTrips]![TripID]) AND
((tblTrips.TripDate)<=[Forms]![frmTrips]![TripDate]) AND
((tblTrips.TruckID)=[Forms]![frmTrips]![truckID]) AND
((tblTripDetails.MileageDescription)="Trip End")) OR
(((tblTrips.TripID)<[Forms]![frmTrips]![TripID]) AND
((tblTrips.TripDate)<=[Forms]![frmTrips]![TripDate]) AND
((tblTrips.TruckID)=[Forms]![frmTrips]![truckID]) AND
((tblTripDetails.MileageDescription)="Wrecker"))
ORDER BY tblTripDetails.Odometer;

qryTripEnd2:
SELECT qryTripEnd1.TruckID, qryTripEnd1.MileageDescription,
Max(qryTripEnd1.Odometer) AS MaxOfOdometer
FROM qryTripEnd1 INNER JOIN tblTripDetails ON qryTripEnd1.Odometer =
tblTripDetails.Odometer
GROUP BY qryTripEnd1.TruckID, qryTripEnd1.MileageDescription;

qryTripEnd2A:
SELECT qryTripEnd2.MaxOfOdometer, qryTripEnd2.MileageDescription,
tblTripDetails.TripID, tblTripDetails.TripDetailsID
FROM qryTripEnd2 INNER JOIN tblTripDetails ON
(qryTripEnd2.MileageDescription = tblTripDetails.MileageDescription) AND
(qryTripEnd2.MaxOfOdometer = tblTripDetails.Odometer);

qryTripEnd2B:
SELECT qryTripEnd2A.MaxOfOdometer, qryTripEnd2A.TripID,
Max(qryTripEnd2A.TripDetailsID) AS MaxOfTripDetailsID, tblTrips.TripDate
FROM qryTripEnd2A INNER JOIN tblTrips ON qryTripEnd2A.TripID =
tblTrips.TripID
GROUP BY qryTripEnd2A.MaxOfOdometer, qryTripEnd2A.MileageDescription,
qryTripEnd2A.TripID, tblTrips.TripDate;

qryTripEnd3:
SELECT qryTripEnd2B.MileageDescription, tblTripDetails.Odometer,
tblTripDetails.TripDetailsID, tblTripDetails.Location,
tblTripDetails.State
FROM tblTripDetails INNER JOIN qryTripEnd2B ON (qryTripEnd2B.TripID =
tblTripDetails.TripID) AND (tblTripDetails.MileageDescription =
qryTripEnd2B.MileageDescription) AND (tblTripDetails.Odometer =
qryTripEnd2B.MaxOfOdometer);
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

update a field in a table 1

Top