By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,426 Members | 3,368 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,426 IT Pros & Developers. It's quick & easy.

Filtering my results by date

P: 1
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:

Expand|Select|Wrap|Line Numbers
  1. SELECT Wards.Name, Wards.[Number Bed], Count(Patients.[Pat-id]) AS [CountOfPat-id]
  2. FROM Wards INNER JOIN (Patients INNER JOIN Admissions ON Patients.[Pat-id] = Admissions.[Pat-id]) ON Wards.[Ward-id] = Admissions.[Ward-id]
  3. 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:

Expand|Select|Wrap|Line Numbers
  1. SELECT Wards.Name, Wards.[Number Bed], Count(Patients.[Pat-id]) AS [CountOfPat-id], Admissions.[Start Date], Admissions.[End Date]
  2. FROM Wards INNER JOIN (Patients INNER JOIN Admissions ON Patients.[Pat-id] = Admissions.[Pat-id]) ON Wards.[Ward-id] = Admissions.[Ward-id]
  3. GROUP BY Wards.Name, Wards.[Number Bed], Admissions.[Start Date], Admissions.[End Date]
  4. 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.

Feb 7 '07 #1
Share this Question
Share on Google+
3 Replies

Expert Mod 10K+
P: 12,366
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.
Feb 7 '07 #2

Expert Mod 10K+
P: 14,534
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 ...

Expand|Select|Wrap|Line Numbers
  1. SELECT Wards.Name, Wards.[Number Bed], Count(Patients.[Pat-id]) AS [CountOfPat-id]
  2. FROM Wards INNER JOIN (Patients INNER JOIN Admissions 
  3. ON Patients.[Pat-id] = Admissions.[Pat-id]) 
  4. ON Wards.[Ward-id] = Admissions.[Ward-id]
  5. WHERE Now() BETWEEN Admissions.[Start Date] 
  6. AND Admissions.[End Date]
  7. GROUP BY Wards.Name, Wards.[Number Bed];
Feb 8 '07 #3

Expert Mod 15k+
P: 31,494
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 :)
Feb 8 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.