Problem with query: SELECT TOP 1

S

ScottA

I'm trying to produce a report that will show when a
machine needs service, which is calculated by evaluating
data in two tables - a service records table and a service
schedule table. The business rule for calculating next
service is to check the schedule, which contains both a
start date and interval, and the records table which shows
the last maintenance. The next service should be the
later of (Start Date) or (Last Mainenance + Interval).
Each machine can be subject to multiptle types of
service/inspection.

I've been able to put figures from these two tables
together using a Union query that also eliminates null
values where they would cause problems:

ELECT tblServiceRecords.EqID,
tblServiceRecords.ServiceTypeID, DateAdd("m",
[tblServiceSchedule.Interval],[ServiceDate]) AS DueDate
FROM tblServiceRecords LEFT JOIN tblServiceSchedule ON
(tblServiceRecords.EqID = tblServiceSchedule.EqID) AND
(tblServiceRecords.ServiceTypeID =
tblServiceSchedule.ServiceTypeID)
WHERE (((tblServiceSchedule.Interval) Is Not Null))
UNION SELECT EqID, ServiceTypeID, StartDate FROM
tblServiceSchedule WHERE (tblServiceSchedule.StartDate) Is
Not Null;

The result is a record for all possible due dates

Now I would like to bring these results into another query
that shows a record for each type of service with the
latest due date. I'm working with a TOP 1 query, but it
doesn't seem to do the job right.

Specifically, it is asking me for two parameters
(tblServiceSchedule.ServiceTypeID and
tblServiceSchedule.EqID) that are both in the query!!!

SELECT tblServiceSchedule.EqID,
tblServiceSchedule.ServiceTypeID,
tblServiceSchedule.Interval,
tblServiceSchedule.ServiceCompanyID, (SELECT TOP 1 DueDate
FROM quniServiceDueDates WHERE (ServiceTypeID =
tblServiceSchedule.ServiceTypeID AND EqID =
tblServiceSchedule.EqID) ORDER BY DueDate DESC ) AS
ServiceDue
FROM tblServiceSchedule;

Can anybody tell me what I might have done wrong?

Thanks,

Scott A
 
G

Gary Walter

ScottA said:
I'm trying to produce a report that will show when a
machine needs service, which is calculated by evaluating
data in two tables - a service records table and a service
schedule table. The business rule for calculating next
service is to check the schedule, which contains both a
start date and interval, and the records table which shows
the last maintenance. The next service should be the
later of (Start Date) or (Last Mainenance + Interval).
Each machine can be subject to multiptle types of
service/inspection.

I've been able to put figures from these two tables
together using a Union query that also eliminates null
values where they would cause problems:

ELECT tblServiceRecords.EqID,
tblServiceRecords.ServiceTypeID, DateAdd("m",
[tblServiceSchedule.Interval],[ServiceDate]) AS DueDate
FROM tblServiceRecords LEFT JOIN tblServiceSchedule ON
(tblServiceRecords.EqID = tblServiceSchedule.EqID) AND
(tblServiceRecords.ServiceTypeID =
tblServiceSchedule.ServiceTypeID)
WHERE (((tblServiceSchedule.Interval) Is Not Null))
UNION SELECT EqID, ServiceTypeID, StartDate FROM
tblServiceSchedule WHERE (tblServiceSchedule.StartDate) Is
Not Null;

The result is a record for all possible due dates

Now I would like to bring these results into another query
that shows a record for each type of service with the
latest due date. I'm working with a TOP 1 query, but it
doesn't seem to do the job right.

Specifically, it is asking me for two parameters
(tblServiceSchedule.ServiceTypeID and
tblServiceSchedule.EqID) that are both in the query!!!

SELECT tblServiceSchedule.EqID,
tblServiceSchedule.ServiceTypeID,
tblServiceSchedule.Interval,
tblServiceSchedule.ServiceCompanyID, (SELECT TOP 1 DueDate
FROM quniServiceDueDates WHERE (ServiceTypeID =
tblServiceSchedule.ServiceTypeID AND EqID =
tblServiceSchedule.EqID) ORDER BY DueDate DESC ) AS
ServiceDue
FROM tblServiceSchedule;
Click to expand...
Hi Scott,

I don't know if I would do it the above way....
but, you might try something like below (to
eliminate "parameter requests" at least):

SELECT tblServiceSchedule.EqID,
tblServiceSchedule.ServiceTypeID,
tblServiceSchedule.Interval,
tblServiceSchedule.ServiceCompanyID,
(SELECT Max(q.DueDate)
FROM quniServiceDueDates As q
WHERE
q.ServiceTypeID = tblServiceSchedule.ServiceTypeID
AND
q.EqID = tblServiceSchedule.EqID) AS ServiceDue
FROM tblServiceSchedule;

Good luck,

Gary Walter
 
S

ScottA

Gary -
Many thanks for the suggestion, but I still get prompted
for these two parameters. It makes absolutely no sense as
to why they would pop up when the fields are already named
in the query. Is this a bug?

-----Original Message-----

ScottA said:
I'm trying to produce a report that will show when a
machine needs service, which is calculated by evaluating
data in two tables - a service records table and a service
schedule table. The business rule for calculating next
service is to check the schedule, which contains both a
start date and interval, and the records table which shows
the last maintenance. The next service should be the
later of (Start Date) or (Last Mainenance + Interval).
Each machine can be subject to multiptle types of
service/inspection.

I've been able to put figures from these two tables
together using a Union query that also eliminates null
values where they would cause problems:

ELECT tblServiceRecords.EqID,
tblServiceRecords.ServiceTypeID, DateAdd("m",
[tblServiceSchedule.Interval],[ServiceDate]) AS DueDate
FROM tblServiceRecords LEFT JOIN tblServiceSchedule ON
(tblServiceRecords.EqID = tblServiceSchedule.EqID) AND
(tblServiceRecords.ServiceTypeID =
tblServiceSchedule.ServiceTypeID)
WHERE (((tblServiceSchedule.Interval) Is Not Null))
UNION SELECT EqID, ServiceTypeID, StartDate FROM
tblServiceSchedule WHERE (tblServiceSchedule.StartDate) Is
Not Null;

The result is a record for all possible due dates

Now I would like to bring these results into another query
that shows a record for each type of service with the
latest due date. I'm working with a TOP 1 query, but it
doesn't seem to do the job right.

Specifically, it is asking me for two parameters
(tblServiceSchedule.ServiceTypeID and
tblServiceSchedule.EqID) that are both in the query!!!

SELECT tblServiceSchedule.EqID,
tblServiceSchedule.ServiceTypeID,
tblServiceSchedule.Interval,
tblServiceSchedule.ServiceCompanyID, (SELECT TOP 1 DueDate
FROM quniServiceDueDates WHERE (ServiceTypeID =
tblServiceSchedule.ServiceTypeID AND EqID =
tblServiceSchedule.EqID) ORDER BY DueDate DESC ) AS
ServiceDue
FROM tblServiceSchedule;
Click to expand...
Hi Scott,

I don't know if I would do it the above way....
but, you might try something like below (to
eliminate "parameter requests" at least):

SELECT tblServiceSchedule.EqID,
tblServiceSchedule.ServiceTypeID,
tblServiceSchedule.Interval,
tblServiceSchedule.ServiceCompanyID,
(SELECT Max(q.DueDate)
FROM quniServiceDueDates As q
WHERE
q.ServiceTypeID = tblServiceSchedule.ServiceTypeID
AND
q.EqID = tblServiceSchedule.EqID) AS ServiceDue
FROM tblServiceSchedule;

Good luck,

Gary Walter


.
Click to expand...
 
S

ScottA

I tried flipping the records in the Union Query - which
seems to have helped. It doesn't ask for the parameters
any longer. I would assume that would be because the
first part of the union query explicitly declared table
names? wierd.

When I used the Select Max, it gave me the same start date
for all records (?), so I changed it back to Top 1, and
sorted the union query results in descending order:

SELECT tblServiceSchedule.EqID,
tblServiceSchedule.ServiceTypeID,
tblServiceSchedule.Interval,
tblServiceSchedule.ServiceCompanyID, (SELECT TOP 1 DueDate
FROM quniServiceDueDates As q WHERE q.ServiceTypeID =
tblServiceSchedule.ServiceTypeID AND q.EqID =
tblServiceSchedule.EqID ORDER BY DueDate DESC ) AS
ServiceDue FROM tblServiceSchedule;

But I'm still not out of the woods. When I run the query
I get my favorite "At most one record can be returned by
this subquery", which usually pops up when there is a tie
(?). I've already searched the recordset for dupes, but
there aren't any!

Any ideas?
-----Original Message-----

ScottA said:
I'm trying to produce a report that will show when a
machine needs service, which is calculated by evaluating
data in two tables - a service records table and a service
schedule table. The business rule for calculating next
service is to check the schedule, which contains both a
start date and interval, and the records table which shows
the last maintenance. The next service should be the
later of (Start Date) or (Last Mainenance + Interval).
Each machine can be subject to multiptle types of
service/inspection.

I've been able to put figures from these two tables
together using a Union query that also eliminates null
values where they would cause problems:

ELECT tblServiceRecords.EqID,
tblServiceRecords.ServiceTypeID, DateAdd("m",
[tblServiceSchedule.Interval],[ServiceDate]) AS DueDate
FROM tblServiceRecords LEFT JOIN tblServiceSchedule ON
(tblServiceRecords.EqID = tblServiceSchedule.EqID) AND
(tblServiceRecords.ServiceTypeID =
tblServiceSchedule.ServiceTypeID)
WHERE (((tblServiceSchedule.Interval) Is Not Null))
UNION SELECT EqID, ServiceTypeID, StartDate FROM
tblServiceSchedule WHERE (tblServiceSchedule.StartDate) Is
Not Null;

The result is a record for all possible due dates

Now I would like to bring these results into another query
that shows a record for each type of service with the
latest due date. I'm working with a TOP 1 query, but it
doesn't seem to do the job right.

Specifically, it is asking me for two parameters
(tblServiceSchedule.ServiceTypeID and
tblServiceSchedule.EqID) that are both in the query!!!

SELECT tblServiceSchedule.EqID,
tblServiceSchedule.ServiceTypeID,
tblServiceSchedule.Interval,
tblServiceSchedule.ServiceCompanyID, (SELECT TOP 1 DueDate
FROM quniServiceDueDates WHERE (ServiceTypeID =
tblServiceSchedule.ServiceTypeID AND EqID =
tblServiceSchedule.EqID) ORDER BY DueDate DESC ) AS
ServiceDue
FROM tblServiceSchedule;
Click to expand...
Hi Scott,

I don't know if I would do it the above way....
but, you might try something like below (to
eliminate "parameter requests" at least):

SELECT tblServiceSchedule.EqID,
tblServiceSchedule.ServiceTypeID,
tblServiceSchedule.Interval,
tblServiceSchedule.ServiceCompanyID,
(SELECT Max(q.DueDate)
FROM quniServiceDueDates As q
WHERE
q.ServiceTypeID = tblServiceSchedule.ServiceTypeID
AND
q.EqID = tblServiceSchedule.EqID) AS ServiceDue
FROM tblServiceSchedule;

Good luck,

Gary Walter


.
Click to expand...
 
J

John Spencer (MVP)

Top 1 CAN return MORE than one record if there are ties, and the existence of
the possibility is enough to cause the failure. You will have to use MAX to get
the latest date. Perhaps the following will work:

SELECT tblServiceSchedule.EqID,
tblServiceSchedule.ServiceTypeID,
tblServiceSchedule.Interval,
tblServiceSchedule.ServiceCompanyID,
(SELECT Max(Q.DueDate)
FROM quniServiceDueDates as Q
WHERE Q.ServiceTypeID = tblServiceSchedule.ServiceTypeID
AND Q.EqID = tblServiceSchedule.EqID) AS ServiceDue
FROM tblServiceSchedule;

Note that I have specifically identified the tables/queries for the fields in
the subquery.

As you probably know the most frequent cause of the parameter request is
misspelled field or table names.
 
J

John Vinson

Specifically, it is asking me for two parameters
(tblServiceSchedule.ServiceTypeID and
tblServiceSchedule.EqID) that are both in the query!!!
Click to expand...

But they're not in the query *with those names*. The UNION query
discards the table information - try referring to just ServiceTypeID
or quniServiceDueDates.ServiceTypeID instead of referring to the table
name.

John W. Vinson[MVP]
(no longer chatting for now)
 
G

Gary Walter

Hi Scott,

How big is tblServiceSchedule?

If this is not sensitive data, you are welcome
to import that table into a new database, zip
it, and send to me (just remove "pleasenospam"
from my email address) in an attachment.

It might be a day or so before I can respond back
here, but I don't understand why Max didn't work
unless I have assumed something that does not gibe
with your data.

Thanks,

Gary Walter

ScottA said:
I tried flipping the records in the Union Query - which
seems to have helped. It doesn't ask for the parameters
any longer. I would assume that would be because the
first part of the union query explicitly declared table
names? wierd.

When I used the Select Max, it gave me the same start date
for all records (?), so I changed it back to Top 1, and
sorted the union query results in descending order:

SELECT tblServiceSchedule.EqID,
tblServiceSchedule.ServiceTypeID,
tblServiceSchedule.Interval,
tblServiceSchedule.ServiceCompanyID, (SELECT TOP 1 DueDate
FROM quniServiceDueDates As q WHERE q.ServiceTypeID =
tblServiceSchedule.ServiceTypeID AND q.EqID =
tblServiceSchedule.EqID ORDER BY DueDate DESC ) AS
ServiceDue FROM tblServiceSchedule;

But I'm still not out of the woods. When I run the query
I get my favorite "At most one record can be returned by
this subquery", which usually pops up when there is a tie
(?). I've already searched the recordset for dupes, but
there aren't any!

Any ideas?
-----Original Message-----

ScottA said:
I'm trying to produce a report that will show when a
machine needs service, which is calculated by evaluating
data in two tables - a service records table and a service
schedule table. The business rule for calculating next
service is to check the schedule, which contains both a
start date and interval, and the records table which shows
the last maintenance. The next service should be the
later of (Start Date) or (Last Mainenance + Interval).
Each machine can be subject to multiptle types of
service/inspection.

I've been able to put figures from these two tables
together using a Union query that also eliminates null
values where they would cause problems:

ELECT tblServiceRecords.EqID,
tblServiceRecords.ServiceTypeID, DateAdd("m",
[tblServiceSchedule.Interval],[ServiceDate]) AS DueDate
FROM tblServiceRecords LEFT JOIN tblServiceSchedule ON
(tblServiceRecords.EqID = tblServiceSchedule.EqID) AND
(tblServiceRecords.ServiceTypeID =
tblServiceSchedule.ServiceTypeID)
WHERE (((tblServiceSchedule.Interval) Is Not Null))
UNION SELECT EqID, ServiceTypeID, StartDate FROM
tblServiceSchedule WHERE (tblServiceSchedule.StartDate) Is
Not Null;

The result is a record for all possible due dates

Now I would like to bring these results into another query
that shows a record for each type of service with the
latest due date. I'm working with a TOP 1 query, but it
doesn't seem to do the job right.

Specifically, it is asking me for two parameters
(tblServiceSchedule.ServiceTypeID and
tblServiceSchedule.EqID) that are both in the query!!!

SELECT tblServiceSchedule.EqID,
tblServiceSchedule.ServiceTypeID,
tblServiceSchedule.Interval,
tblServiceSchedule.ServiceCompanyID, (SELECT TOP 1 DueDate
FROM quniServiceDueDates WHERE (ServiceTypeID =
tblServiceSchedule.ServiceTypeID AND EqID =
tblServiceSchedule.EqID) ORDER BY DueDate DESC ) AS
ServiceDue
FROM tblServiceSchedule;
Click to expand...
Hi Scott,

I don't know if I would do it the above way....
but, you might try something like below (to
eliminate "parameter requests" at least):

SELECT tblServiceSchedule.EqID,
tblServiceSchedule.ServiceTypeID,
tblServiceSchedule.Interval,
tblServiceSchedule.ServiceCompanyID,
(SELECT Max(q.DueDate)
FROM quniServiceDueDates As q
WHERE
q.ServiceTypeID = tblServiceSchedule.ServiceTypeID
AND
q.EqID = tblServiceSchedule.EqID) AS ServiceDue
FROM tblServiceSchedule;

Good luck,

Gary Walter


.
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 Top with aggregate function 1
Help needed with SELECT query 2
problem with a DISTINCT query 4
Problem with "Top N Value" Query 4
Query with sub SELECT statement not allowing sum of a field 1
Excel query help with union select 2
Please Help with Query! 4
problem with query in vba 1

Top