PIVOT Tables (1 Viewer)

[bilakos93]

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

 

[CJ_London]

Have you tried the crosstab wizard? Wards would be column header, patient details the row header and sum days for the value

For crosstabs , you must specify the parameters- see the parameter option on the ribbon in query design view

 

[CJ_London]

crossposted here with answers

PIVOT table

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...

www.accessforums.net

@bilakos93 - please read this thread

A message to forum cross posters

Do you know why you're here? If you have been pointed to this page, it is not because someone is mad at you, but rather because we want to curb your posting habits before people start ignoring your posts. Online … Continue reading →

excelguru.ca

 

[bilakos93]

Have you tried the crosstab wizard? Wards would be column header, patient details the row header and sum days for the value

For crosstabs , you must specify the parameters- see the parameter option on the ribbon in query design view

thank you that was very helpful

 

[plog]

You don't build a four story house and then at put a level on the roof to see if the whole thing is level. You check as you go.

Your second query didn't fail, your first one did. Run query1 and you'll get the same error as you do in the second (and possibly third if there is one). The reason is when you build an aggregate query (one with a GROUP BY), every item in the SELECT must either be acted on by a aggregate function (COUNT, MAX, SUM, etc.) or appear in the GROUP BY. You're query doesn't conform to that and the errors are which fields fail that test.

The good news is, you don't need all those fields in query1's SELECT. So either remove them from there, or make them appear in the GROUP BY. Try to only use the fields you need.

 

[bilakos93]

You don't build a four story house and then at put a level on the roof to see if the whole thing is level. You check as you go.

Your second query didn't fail, your first one did. Run query1 and you'll get the same error as you do in the second (and possibly third if there is one). The reason is when you build an aggregate query (one with a GROUP BY), every item in the SELECT must either be acted on by a aggregate function (COUNT, MAX, SUM, etc.) or appear in the GROUP BY. You're query doesn't conform to that and the errors are which fields fail that test.

The good news is, you don't need all those fields in query1's SELECT. So either remove them from there, or make them appear in the GROUP BY. Try to only use the fields you need.

thank you

Actually my first query was fine
I understand it may be long and messy but I essentially have zero experience
What I did is I created a pivot table (using the wizard) based on my query1

TRANSFORM Sum(Query1.Expr3) AS SumOfExpr3
SELECT Query1.lastname, Query1.firstname
FROM Query1
GROUP BY Query1.lastname, Query1.firstname
ORDER BY Query1.lastname
PIVOT Query1.h.ward In ("ward1","ward2","ward3","ward4");

 

[June7]

What are "reportstartdate" and "reportenddate"? DO NOT use popup input prompts in query - cannot validate input. Reference controls on form for user input. For these to be popup input prompts, must enclose in brackets - [reportstartdate], [reportenddate].

Should be able to produce this CROSSTAB output without the intermediate query. Review http://allenbrowne.com/ser-67.html.
And because you show non-U.S. date structure, http://allenbrowne.com/ser-36.html

TRANSFORM Sum(DateDiff("d",[adm_date],[dis_date])) AS Days
SELECT lastname, firstname
FROM Patients INNER JOIN Hospital ON Patients.ID = Hospital.PatientID
WHERE adm_date >= [enter start] And dis_date <= [enter end]
GROUP BY lastname, firstname
PIVOT "Ward" & [Ward] In ("Ward1", "Ward2", "Ward3", "Ward4");