 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / February 2008Filter In A Query
Hi, As you can see below I have a query which includes records from the reservation table, but I want to show only those records which their arrival date ARRIVAL of the RESERVATIONS table is equal to the value of RUNDAY in the HOUSEKEEPING DATA Query. SELECT ROOMS.ROOMS, [HOUSEKEEPING DATA].HOTELAPT, [HOUSEKEEPING DATA].PERSONS, [HOUSEKEEPING DATA].RESNAME, [HOUSEKEEPING DATA].ARRIVAL, [HOUSEKEEPING DATA].DEPARTURE, [HOUSEKEEPING DATA].TOWELS, [HOUSEKEEPING DATA].[TOWELS NEXT], [HOUSEKEEPING DATA].LINEN, [HOUSEKEEPING DATA].[LINEN NEXT], [HOUSEKEEPING DATA].RUNDAY, RESERVATIONS.PERSONS, RESERVATIONS.RESNAME, RESERVATIONS.COMPANY, RESERVATIONS.ARRIVAL FROM (ROOMS LEFT JOIN [HOUSEKEEPING DATA] ON ROOMS.ROOMS = [HOUSEKEEPING DATA].ROOMNO) LEFT JOIN RESERVATIONS ON ROOMS.ROOMS = RESERVATIONS.ROOMNO GROUP BY ROOMS.ROOMS, [HOUSEKEEPING DATA].HOTELAPT, [HOUSEKEEPING DATA].PERSONS, [HOUSEKEEPING DATA].RESNAME, [HOUSEKEEPING DATA].ARRIVAL, [HOUSEKEEPING DATA].DEPARTURE, [HOUSEKEEPING DATA].TOWELS, [HOUSEKEEPING DATA].[TOWELS NEXT], [HOUSEKEEPING DATA].LINEN, [HOUSEKEEPING DATA].[LINEN NEXT], [HOUSEKEEPING DATA].RUNDAY, [HOUSEKEEPING DATA].ROOMNO, RESERVATIONS.PERSONS, RESERVATIONS.RESNAME, RESERVATIONS.COMPANY, RESERVATIONS.ARRIVAL; Thanks Ange Just add a where clause before the group by. SELECT ROOMS.ROOMS , [HOUSEKEEPING DATA].HOTELAPT , [HOUSEKEEPING DATA].PERSONS , [HOUSEKEEPING DATA].RESNAME , [HOUSEKEEPING DATA].ARRIVAL , [HOUSEKEEPING DATA].DEPARTURE , [HOUSEKEEPING DATA].TOWELS , [HOUSEKEEPING DATA].[TOWELS NEXT] , [HOUSEKEEPING DATA].LINEN , [HOUSEKEEPING DATA].[LINEN NEXT] , [HOUSEKEEPING DATA].RUNDAY , RESERVATIONS.PERSONS , RESERVATIONS.RESNAME , RESERVATIONS.COMPANY , RESERVATIONS.ARRIVAL FROM (ROOMS LEFT JOIN [HOUSEKEEPING DATA] ON ROOMS.ROOMS = [HOUSEKEEPING DATA].ROOMNO) LEFT JOIN RESERVATIONS ON ROOMS.ROOMS = RESERVATIONS.ROOMNO WHERE Reservations.Arrival = [HouseKeeping Data].RunDay GROUP BY ROOMS.ROOMS , [HOUSEKEEPING DATA].HOTELAPT , [HOUSEKEEPING DATA].PERSONS , [HOUSEKEEPING DATA].RESNAME , [HOUSEKEEPING DATA].ARRIVAL , [HOUSEKEEPING DATA].DEPARTURE , [HOUSEKEEPING DATA].TOWELS , [HOUSEKEEPING DATA].[TOWELS NEXT] , [HOUSEKEEPING DATA].LINEN , [HOUSEKEEPING DATA].[LINEN NEXT] , [HOUSEKEEPING DATA].RUNDAY , [HOUSEKEEPING DATA].ROOMNO , RESERVATIONS.PERSONS , RESERVATIONS.RESNAME , RESERVATIONS.COMPANY , RESERVATIONS.ARRIVAL By the way you can accomplish the same thing without grouping by including DISTINCT after SELECT in the query. SELECT DISTINCT ROOMS.ROOMS , [HOUSEKEEPING DATA].HOTELAPT , [HOUSEKEEPING DATA].PERSONS , [HOUSEKEEPING DATA].RESNAME , [HOUSEKEEPING DATA].ARRIVAL , [HOUSEKEEPING DATA].DEPARTURE , [HOUSEKEEPING DATA].TOWELS , [HOUSEKEEPING DATA].[TOWELS NEXT] , [HOUSEKEEPING DATA].LINEN , [HOUSEKEEPING DATA].[LINEN NEXT] , [HOUSEKEEPING DATA].RUNDAY , RESERVATIONS.PERSONS , RESERVATIONS.RESNAME , RESERVATIONS.COMPANY , RESERVATIONS.ARRIVAL FROM (ROOMS LEFT JOIN [HOUSEKEEPING DATA] ON ROOMS.ROOMS = [HOUSEKEEPING DATA].ROOMNO) LEFT JOIN RESERVATIONS ON ROOMS.ROOMS = RESERVATIONS.ROOMNO WHERE Reservations.Arrival = [HouseKeeping Data].RunDay  SignatureJohn Spencer Access MVP 2002-2005, 2007-2008 Center for Health Program Development and Management University of Maryland Baltimore County . > Hi, > As you can see below I have a query which includes records from the [quoted text clipped - 3 lines] > Thanks > Ange Hi John, Tried it but it returned with no results since there is a record which has the same value in the Arrival field as the runday field. Ange > Just add a where clause before the group by. > [quoted text clipped - 67 lines] >> Thanks >> Ange Hi John, I worked it out, I made a separate query filtering out todays arrivals called it qryarrivals and added it to the query housekeeping adjusted the union and it worked. Here is the SQL to it: SELECT ROOMS.ROOMS, [HOUSEKEEPING DATA].HOTELAPT, [HOUSEKEEPING DATA].PERSONS, [HOUSEKEEPING DATA].RESNAME, [HOUSEKEEPING DATA].ARRIVAL, [HOUSEKEEPING DATA].DEPARTURE, [HOUSEKEEPING DATA].TOWELS, [HOUSEKEEPING DATA].[TOWELS NEXT], [HOUSEKEEPING DATA].LINEN, [HOUSEKEEPING DATA].[LINEN NEXT], [HOUSEKEEPING DATA].RUNDAY, qryARRIVALS.ROOMNO, qryARRIVALS.COMPANY, qryARRIVALS.ARRIVAL, qryARRIVALS.PERSONS, qryARRIVALS.RESNAME FROM qryARRIVALS RIGHT JOIN (ROOMS LEFT JOIN [HOUSEKEEPING DATA] ON ROOMS.ROOMS = [HOUSEKEEPING DATA].ROOMNO) ON qryARRIVALS.ROOMNO = ROOMS.ROOMS GROUP BY ROOMS.ROOMS, [HOUSEKEEPING DATA].HOTELAPT, [HOUSEKEEPING DATA].PERSONS, [HOUSEKEEPING DATA].RESNAME, [HOUSEKEEPING DATA].ARRIVAL, [HOUSEKEEPING DATA].DEPARTURE, [HOUSEKEEPING DATA].TOWELS, [HOUSEKEEPING DATA].[TOWELS NEXT], [HOUSEKEEPING DATA].LINEN, [HOUSEKEEPING DATA].[LINEN NEXT], [HOUSEKEEPING DATA].RUNDAY, [HOUSEKEEPING DATA].ROOMNO, qryARRIVALS.ROOMNO, qryARRIVALS.COMPANY, qryARRIVALS.ARRIVAL, qryARRIVALS.PERSONS, qryARRIVALS.RESNAME; Thanks. Ange > Hi, > As you can see below I have a query which includes records from the [quoted text clipped - 20 lines] > Thanks > Ange |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.