Results 1 to 4 of 4
  1. #1
    Bilakos93 is offline Novice
    Windows 10 Office 365
    Join Date
    Aug 2023

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

    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
    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
    Last edited by Bilakos93; 09-23-2023 at 07:06 AM. Reason: Typo

  2. #2
    Join Date
    May 2018
    Living in Scotland UK
    Welcome to the forum

    This is not the way to enter data in Access, this is a Spreadsheet layout

    Ward 1 Ward 2 Ward 3 Ward 4

  3. #3
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2015
    Kelowna, BC, Canada

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Pivot table
    By bbridgers05 in forum Access
    Replies: 3
    Last Post: 12-29-2018, 11:29 AM
  2. Pivot table ref
    By Bobby100 in forum Queries
    Replies: 4
    Last Post: 10-28-2016, 11:08 AM
  3. Replies: 0
    Last Post: 09-22-2014, 07:07 AM
  4. Can't creat pivot table
    By jmhultin in forum Access
    Replies: 3
    Last Post: 03-11-2013, 03:08 PM
  5. [Help] Access Pivot Table
    By weiguo.shi in forum Queries
    Replies: 0
    Last Post: 05-18-2011, 09:49 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
Other Forums: Microsoft Office Forums