At most one record can be returned by this subquery...

[ScottA]

.... at lease that's what Access is telling me with regard
to a query that was working perfectly well before.

Does anyone have any insight into the nature of this
error? Trying to use the Access 2000 Support Center and
not able to find much...

Thanks,

ScottA

 

[[MVP] S.Clark]

I'd say you have a duplicate record, in regards to the criteria that the
Subquery is using. Trying running the subquery by itself and see how many
records are returned.


--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

 

[ScottA]

Steve, thanks for your suggestion.

I'm not seeing any duplicates records in the tables - and
the subquery is a TOP 1 query that can only return single
selected values that match the criteria given. When I run
the subquery by itself, I just get the one record (the TOP
1).

What I find really bizarre is that when I run the query
from design view, the query returns records for the column
called 'LastMaintained' for equipment that has been
serviced - then the query pauses for a minute, Access pops
up the 'at most one record...' error, and all of the
fields returned by the query are replaced with #Name?

I swear I used to get records with this query, and have no
idea as to why it won't work anymore... If you have any
ideas, please let me know.


Here's the SQL:

SELECT tblEquipment.EqID, tblEquipTypes.EqType,
tblLocations.RoomNo, tblServiceCompanies.ServiceCompName,
tblEquipment.ServiceNo, tblEquipment.WarrantyExpDate,
tblEquipment.MaintenanceInterval, (SELECT TOP 1
MaintenanceDate FROM tblMaintenanceRecords WHERE
(tblMaintenanceRecords.EqID = tblEquipment.EqID) ORDER BY
MaintenanceDate DESC, tblMaintenanceRecords.EqID ) AS
LastMaintained, tblEquipment.EqName,
tblServiceCompanies.ServiceCompCustomerNumber,
tblServiceCompanies.ServiceCompContractNo,
tblServiceCompanies.ServiceCompContactName,
tblServiceCompanies.ServiceCompPhone,
tblPurchases.PurchaseDate
FROM tblServiceCompanies RIGHT JOIN (tblLocations RIGHT
JOIN (tblEquipTypes RIGHT JOIN (tblEquipment INNER JOIN
tblPurchases ON tblEquipment.EqID = tblPurchases.EqID) ON
tblEquipTypes.EqTypeID = tblEquipment.EqTypeID) ON
tblLocations.LocationID = tblEquipment.LocationID) ON
tblServiceCompanies.ServiceCompID =
tblEquipment.ServiceCompID
WHERE (((tblEquipment.MaintenanceInterval) Is Not Null));

 

[[MVP] S.Clark]

You'd better double check the results of your TOP 1 query, as it can still
return more than one record, in the case of a tie.