Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Nov 2001
    Location
    Crediton, Devon, England
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    'Normalised' pivot table confusion (Access '97)

    Can a pivot table guru guide me please? I'm trying to design a cross-tab for eventual use as a stored query for website in Access '97.

    I collect recyclable materials from houses in several towns. People occasionally request replacement storage boxes and/or lids. I want to display the aggregated request count over a given period, per town, per thousand houses in THAT town (so that I may compare the needs of towns across the region).

    Simplifying my problem, two tables are relevant. [Incidentlog] contains the requests, and has the [Town] as a field together with [CatAbbrev] which will hold "B" for a box, "L" for a lid. The other table [routeday], is a list of all the roads in all the towns, with [Town] as one field and another [housecount] as the number of houses in a given road.

    Getting the non-normalised request count per town is trivial (almost, *grin*):-

    TRANSFORM Count(Incidentlog.RecID) AS NumberofEnquiries
    SELECT Town
    FROM IncidentLog
    WHERE (CatAbbrev = "B" OR CatAbbrev="L")
    GROUP BY Town
    PIVOT CatAbbrev;

    This shows correctly, per town, the counts for boxes and lids over a given period.

    What I've tried unsuccessfully for normalisation (per 1 household at present) is:

    TRANSFORM Count(Incidentlog.RecID)/Sum(RouteDay.HouseCount) AS RequestsPer
    SELECT IncidentLog.Town
    FROM IncidentLog, RouteDay
    WHERE IncidentLog.Town = RouteDay.town
    AND (CategoryLU.CatShortCode = "B" OR CategoryLU.CatShortCode="L")
    GROUP BY IncidentLog.Town
    PIVOT CatAbbrev;

    This clearly is not working. A key issue is clearly to aggregate all the roads in a particular town and Sum() their housecount fields. I can't quite get my head round what's going on here, and so hope that someone more expert than I might tell me whether or not this kind of thing can be done in an Access 97 crosstab, and if it can, what the correct syntax is!!

    Grateful for any guidance. TIA - Mike Brett -

  2. #2
    New Lounger
    Join Date
    Nov 2001
    Location
    Crediton, Devon, England
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'Normalised' pivot table confusion (Access '97)

    Problem resolved by compromise....

    For any student still awake at this stage and remotely interested, here's how.

    You cannot have more than one aggregate function in a CrossTab, it would seem, so I made a two field temporary table consisting solely of the town name and its associated household count:

    SELECT RouteDay.Town, Sum(RouteDay.HouseCount) AS [HousesperTown] INTO HouseCounts
    FROM RouteDay

    Then I modified the CrossTab to bring that prefabricated aggregate in ......

    TRANSFORM Format(1000*Count(Incidentlog.RecID)/Housecounts.HousesPerTown, "##") AS RequestsPerThousand
    SELECT IncidentLog.Town, Housecounts.HousesPerTown
    FROM IncidentLog, Housecounts
    WHERE (Catabbrev = "B" OR Catabbrev="L")
    AND IncidentLog.Town = Housecounts.Town
    GROUP BY IncidentLog.Town, Housecounts.HousesPerTown
    PIVOT CategoryLU.Category

    This gives me four columns rather than the three I actually need (Town, HousesPerTown (not really wanted), normalised "B"ox requests, normalised "L"id requests).

    Goes to prove how powerful SQL can be even at the Access '97 level. I'd hate to have done that via scripted manipulation in code!!

    - Mike -

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •