Windows 10 
Office 365
Hi all
I've only just started getting more familiar with MS Access and SQL
I have a table which contains patients details (firstname, lastname, id) and another one where it shows the dates of their admissions accross 4 different wards (from a dropdown list) in a hospital (e.g. 19/8/23-23/8/23 on ward1 OR 10/8/23 [with no discharge date yet] on ward 4). I want to create a table that will contain patients details on the rows and the ward numbers (1-4) on the columns. The data I'm interested is the number of days they were admitted on each ward within a time period that will be defined by the user.
E.g.
Ward 1 Ward 2 Ward 3 Ward 4
Patient1 4 2
Patient2 10
Patient3 4 1 2 1
What I've done so far (which may be utterly wrong) is:
SELECT p.lastname, p.firstname, h.adm_date, h.dis_date, h.ward, reportstartdate AS expr1, reportenddate AS expr2, IIF(h.adm_date<reportstartdate, reportstartdate, h.adm_date) AS start_date, IIF(ISNULL(h.dis_date), reportenddate, IIF(h.dis_date<reportenddate, h.dis_date, reportenddate) AS end_date, SUM(DATEDIFF('d', start_date, end_date)) AS expr3
FROM patients p
JOIN hospital h
ON p.id=h.patientid
GROUP BY p.lastname, p.firstname, h.adm_date, h.dis_date, h.ward
HAVING h.adm_date>=reportstartdate AND (h.dis_date<=reportenddate OR ISNULL(h.dis_date))
AS query1
And then
SELECT query1.p.lastname, query1.p.firstname, query1.h.ward, SUM(query1.expr3) AS total
FROM query1
GROUP BY query1.p.lastname, query1.p.firstname, query1.h.ward
Now this gets me to the position to have the SUMs of each patient for each ward
I tried using the query wizard but after I finish I get the message "the microsoft access database engine does not recognize 'reportstartdate' as a valid field name or expression
Thank you in advance
Reply With Quote
