top value queries and null values

[lindactp]

Hello, I have created a top values query that finds the most recent date. If
there are any records with an empty field then it ignores all records for
that item. I put criteria of Is Not Null, but it still is not working. Any
Ideas? Thanks

 

[Bob Barrows [MVP]]

Hello, I have created a top values query that finds the most recent
date. If there are any records with an empty field then it ignores
all records for that item. I put criteria of Is Not Null, but it
still is not working. Any Ideas? Thanks

Show us a few rows of sample data (just enough to illustrate the
symptoms - if two rows will illustrate the symptoms, then show us two
rows) and show the incorrect results you are getting from that sample,
and the correct results you want to be getting.

It will also help to show us the sql statement that is retrieving the
incorrect results - switch your query to SQL View (View menu, toolbar
button or right-click menu) to access the sql statement.

 

[lindactp]

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;

THANKS!!

 

[Bob Barrows [MVP]]

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]

 

[lindactp]

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

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.

 

[Bob Barrows [MVP]]

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

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

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.