Re: Grouping by Years in Columns

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Duane Hookom (duanehookom_at_NoSpamHotmail.com)
Date: 08/03/04 

Date: Tue, 3 Aug 2004 15:13:44 -0500

You would first need to normalize your table by using a union query:
SELECT Hospital, Year, Indicator1 As TheValue, 1 as Indicator
FROM tblSpreadsheet
UNION ALL
SELECT Hospital, Year, Indicator2 , 2
FROM tblSpreadsheet
UNION ALL
SELECT Hospital, Year, Indicator3 , 3
FROM tblSpreadsheet
UNION ALL
...etc...

You can then create a crosstab based on the union query that sets the column
heading to
     ColHead:"Year" & [Year]
Row Heading:
     Hospital
Value
     TheValue (first or sum or average or count or whatever) 

-- 
Duane Hookom
MS Access MVP
--
"Paige" <pclements@kyha.com> wrote in message
news:bb0501c47993$97bfa850$a401280a@phx.gbl...
> I have a database set up like this:
>
> Hospital, year, indicator1, indicator2, indicator3,
>
> I want to see a report like this:
>                Year1   Year2   Year3
> Hospital
>    Indicator 1
>    Indicator 2
>    Indicator 3
>
>
> It looks simple but I am having a brain blockage on
> this.  Do I need to define my table differently or my
> query?
>
> Help!
>

Relevant Pages

  • Re: [PHP] Decide witch table within a union
    ... I can easily imagine a circumstance in which this could be valuable, say the union of several mailing lists that are in separate tables simply because they originate from different sources. ... We union them to print a single alphabetical list, for example, but we want an indicator as to the source of each record. ... Surely you're not suggesting that we code an application to somehow divine the source of a record in a union query when the query itself could simply supply that datum easily. ...
    (php.general)
  • Re: [PHP] Decide witch table within a union
    ... We union them to print a single alphabetical list, for example, but we want an indicator as to the source of each record. ... I can imagine modifying the OP's query to read: ... divine the source of a record in a union query when the query itself could simply supply that datum easily. ...
    (php.general)
  • Re: [perfmon2] I.1 - System calls - ioctl
    ... clear indicator it should actually be two sys calls. ... but instead of adding another flag and directly ... passing a union, you also add interface complexity. ...
    (Linux-Kernel)
  • Re: [perfmon2] I.1 - System calls - ioctl
    ... clear indicator it should actually be two sys calls. ... but instead of adding another flag and directly ... passing a union, you also add interface complexity. ...
    (Linux-Kernel)
  • Re: Complex query with different conditions...
    ... but if any of the rows for that product is found with an Indicator is ... I added a primary key OID to ... A.PRODUCT) UNION SELECT OID, PRODUCT, CODE, RATE, INDICATOR FROM ... with your data plus your data with an extra "L" indicator for Product ...
    (comp.databases.ms-access)