Re: top value queries and null values
- From: "Bob Barrows [MVP]" <reb01501@xxxxxxxxxxxxxxx>
- Date: Mon, 13 Oct 2008 15:46:41 -0400
I hope you saw the mistake I made ...
Nz([Oil Change Mileage],0) + 3000 As NextRecommendedOilChange
should of course be
Max(Nz([Oil Change Mileage],0)) + 3000 As NextRecommendedOilChange
lindactp wrote:
Bob, you are awesome! Thank you, Thank you,Thank you. I did need a
group by query ordered by vehicle id. Have a wonderful week!!
Linda
"Bob Barrows [MVP]" wrote:
lindactp wrote:
Hi Bob, thanks so much for responding!! The database is for vehicle
maintenance. The Maintenance table tracks several entries for each
of the vehicles in the fleet. A record has the following fields
VehicleID, Fuel Fill, Tire Pressure, Oil Change. Some of the fields
may be empty, like when they fuel the vehicle but don't perform an
oil change. The query works great until I add the top value sort to
it then it ignores any vehicle that has a record with a blank field.
The Oil Change Query should return the most recent oil change
mileage for each vehicle and then calculate the mileage for the
next oil change.
From the maintenance table
Vehicle ID Fuel Fill Tire Pressure Oil Change
1 8/1/2008 8/1/2008 43500
1 9/29/2008 9/29/2008
1 10/6/2008 10/6/2008 46500
2 8/1/2008 8/1/2008 25500
2 10/13/2008 10/13/2008 28500
OilQuery Result should show the most recent oil change mileage and
there is a field that calculated the next oil change due
Vehicle ID Oil Change Mileage Next Oil Change Due
1 46500 49500
2 28500 31500
OilQuery incorrect results...
2 28500 49500
When all the fields have data the query works and of course I have a
report generating from the query. If any one field is blank it will
not return any result for that vehicle.
Here is the sql view
SELECT TOP 1 Maintenance.[Vehicle ID], Maintenance.[Oil Change
Mileage]
FROM Maintenance
ORDER BY [Oil Change Mileage]+3000 DESC;
Ok, there are two things I see:
1. with one exception, any operation involving a Null results in
Null. You can use Nz to fix that problem:
ORDER BY Nz([Oil Change Mileage],0) + 3000 DESC
Why are you adding 3000 to the value here? It will result in the same
sort order as it would without adding 3000 ...
2. You say you want to get two results, but you've told the query to
only return 1 result: "SELECT TOP 1". I think what you actually want
is a GROUP BY query:
SELECT Maintenance.[Vehicle ID], Max(Nz([Oil Change Mileage],0)) As
lastoilchange,
Nz([Oil Change Mileage],0) + 3000 As NextRecommendedOilChange
FROM Maintenance
ORDER BY [Vehicle ID]
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get
a quicker response by posting to the newsgroup.
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
.
- References:
- top value queries and null values
- From: lindactp
- Re: top value queries and null values
- From: Bob Barrows [MVP]
- Re: top value queries and null values
- From: lindactp
- Re: top value queries and null values
- From: Bob Barrows [MVP]
- Re: top value queries and null values
- From: lindactp
- top value queries and null values
- Prev by Date: Re: using a puesdo record/autonumber in a make table query
- Next by Date: Date Diff Function
- Previous by thread: Re: top value queries and null values
- Next by thread: Date/Time group by 2 hr increments.
- Index(es):
Relevant Pages
|
