Problem with query: SELECT TOP 1

[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

 

[Gary Walter]

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;

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

 

[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-----

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;

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


.

 

[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-----

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;

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


.

 

[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.

 

[John Vinson]

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

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)

 

[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

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-----

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;

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


.