Filter In A Query

A

Ange Kappas

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
 
J

John Spencer

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

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
A

Ange Kappas

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




John Spencer said:
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

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
.

Ange Kappas said:
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.
Thanks
Ange
Click to expand...
Click to expand...
 
A

Ange Kappas

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
 

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

Date Difference In Query 2
Query with IIF Expression Need Help 4
Copying All Records from a Query to a Table 2
Copying Specific Records From A Field According to another Field By Code 2
Code Stops Due to No Values In Query 5
problems with counting and summing records in a query 1
Adding <All> as option in a multiselect list box 16
Double fieldnames in tables in a query 1

Top