Mike and Doris Manning
mikedorism at ntelos.net
Fri Jul 18 11:37:10 CDT 2003
The WHERE clause is what determines which records get pulled. The ORDER BY clause just defines how the results will be displayed. It isn't working because you are currently asking to have the result set displayed in both DESC and ASC order using the same field. Access is basically saying "HUH?????" Doris Manning Database Administrator Hargrove Inc. www.hargroveinc.com -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Millard, Paul --- Sr. Developer Analyst ---WGO Sent: Thursday, July 17, 2003 5:03 PM To: Access Developers discussion and problem solving Subject: RE: [AccessD] Union Query with ORDER BY They need to be going in opposite directions. For instance, 1st select statement needs to be DESC 95021, 95020, 95019, etc. Otherwise, it will grab the lowest one which could be 00001, 00002, etc. Opposite (same technique) applies when >95022. SELECT TOP 5 ZipCode, Svc, SvcMiles FROM tblSvcToZipMiles WHERE ZipCode<'95022' ORDER BY ZipCode DESC I think I'll create two recordsets since it still runs fast...Obviously, it would have been nice to use union.... Thx - Paul -----Original Message----- From: Mike and Doris Manning [mailto:mikedorism at ntelos.net] Sent: Thursday, July 17, 2003 1:27 PM To: 'Access Developers discussion and problem solving' Subject: RE: [AccessD] Union Query with ORDER BY The problem is that your ORDER BY clauses are going in two different directions... Try making both ASC or both DESC Doris Manning Database Administrator Hargrove Inc. www.hargroveinc.com -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Millard, Paul --- Sr. Developer Analyst ---WGO Sent: Thursday, July 17, 2003 4:18 PM To: accessd at databaseadvisors.com Subject: [AccessD] Union Query with ORDER BY oops forgot to change subject title on message.... I'm trying to write a UNION SQL query below to get the 5 closest zip codes in both directions. To do this, I need to put an ORDER BY clause to get the closest ones in each direction. Running the query separately without UNION everything works fine, however, using the UNION creates a syntax error which has to do with ORDER BY. Any ideas? I would like to keep it as a UNION but worst scenario would be to run two recordsets... Thanks, Paul Millard San Jose, CA SELECT TOP 5 ZipCode, Svc, SvcMiles FROM tblSvcToZipMiles WHERE ZipCode<'95022' ORDER BY ZipCode DESC UNION SELECT TOP 5 ZipCode, Svc, SvcMiles FROM tblSvcToZipMiles WHERE ZipCode>'95022' ORDER BY ZipCode ASC ********************************************************** This message contains information that is confidential and proprietary to FedEx Freight or its affiliates. It is intended only for the recipient named and for the express purpose(s) described therein. Any other use is prohibited. **************************************************************** _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com