Filtering my results by date

[iamguyster]

Hi,
I have an exercise I need to give to my pupils (I'm a teacher!) and I am trying to get a query working preferably using the query design view, without having to edit the SQL. The query involves three tables; Admission, Ward & Patient. The query is to initially return a list of all the wards with the total number of patients on each one. This is fine, using the following SQL:

SELECT Wards.Name, Wards.[Number Bed], Count(Patients.[Pat-id]) AS [CountOfPat-id]
FROM Wards INNER JOIN (Patients INNER JOIN Admissions ON Patients.[Pat-id] = Admissions.[Pat-id]) ON Wards.[Ward-id] = Admissions.[Ward-id]
GROUP BY Wards.Name, Wards.[Number Bed];

The next step is to report only on patients in wards on that day. I have added to the two admissions date fields and set criteria thus:

SELECT Wards.Name, Wards.[Number Bed], Count(Patients.[Pat-id]) AS [CountOfPat-id], Admissions.[Start Date], Admissions.[End Date]
FROM Wards INNER JOIN (Patients INNER JOIN Admissions ON Patients.[Pat-id] = Admissions.[Pat-id]) ON Wards.[Ward-id] = Admissions.[Ward-id]
GROUP BY Wards.Name, Wards.[Number Bed], Admissions.[Start Date], Admissions.[End Date]
HAVING (((Admissions.[Start Date])<Now()) AND ((Admissions.[End Date])>Now()));

Now, the results wnat to also group by the dates, so it doesn't return a single record for each ward, but multiple records for each ward depending on the dates.

Can anyone clear up how I can change this in design view to only return one record per ward regardless of the dates.

The end result will be to present this in a report. I have toyed with the idea of filtering the report using the initial query, but wnat to avoid overcomplicating things for the pupils.

I hope this makes sense and thanks for the help.

Cheers
Guy

[Rabbit]

For the dates you might want to be inclusive of the current day so use <= and >= instead.

As for the multiple records for wards. Do you need to show the date? If you don't show the date it should collapse the records. Grouping by date will show a record for every unique date.

[MMcCarthy]

As Rabbit says ...

As long as you include the date in the query you can't return only one record per ward. You can retain Admissions in the join so as to relate the tables but just not return any fields from this table. Your HAVING statement would be better as a WHERE with a BETWEEN criteria.

See the following ...

SELECT Wards.Name, Wards.[Number Bed], Count(Patients.[Pat-id]) AS [CountOfPat-id]
FROM Wards INNER JOIN (Patients INNER JOIN Admissions 
ON Patients.[Pat-id] = Admissions.[Pat-id]) 
ON Wards.[Ward-id] = Admissions.[Ward-id]
WHERE Now() BETWEEN Admissions.[Start Date] 
AND Admissions.[End Date]
GROUP BY Wards.Name, Wards.[Number Bed];
 

[NeoPa]

I agree with everything that Mary says, but I would add that one should never use the Now() function to compare against dates in this fashion. The Date() function returns just the Date portion of the current time so will not cause problems when comparing with Date-only items in your database.

To see how this is reflected (and therefore how to achieve the same result) in design view, simply paste this SQL into a query and change the view to Design-View. Regard how the WHERE clause is handled (Notice the difference between the WHERE here and the HAVING of your previous query).

Let us know how this works for you :)