Bob,
Your SQL was performing the SUM() inside the IIf() and mine is doing it around the IIf().
Duane
Sent: Wednesday, January 18, 2017 9:01 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] Crosstab Multiple Fields
That does work Duane. I have to add a further field which would need summing and I feared that would break again, but it seems to work.
Much appreciated.
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: 18 January 2017 12:49
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Crosstab Multiple Fields
Try this SQL:
TRANSFORM SUM(IIf([Fieldname]="Upsells", trk.LengthOfStay, 1))
AS TotalUpsells
SELECT prop.PropertyName
FROM tblColumns,
Region AS reg
INNER JOIN ((Country AS cty
INNER JOIN (Brand AS brnd
INNER JOIN (Area INNER JOIN (Division AS div
INNER JOIN (Contract AS con
INNER JOIN Property AS prop
ON con.ContractID = prop.ContractID)
ON div.DivisionID = prop.DivisionID)
ON area.AreaID = prop.AreaID)
ON brnd.BrandID = prop.BrandID)
ON cty.CountryID = prop.CountryID)
INNER JOIN Tracking AS trk
ON prop.PropertyID = trk.PropertyID)
ON reg.RegionID = prop.RegionID
WHERE trk.TrackingYear = 2016
GROUP BY reg.RegionID, prop.PropertyName
PIVOT Fieldname & reg.RegionID;
Regards,
Duane
From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of 'Bob Phillips' bob.phillips@dsl.pipex.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Wednesday, January 18, 2017 4:53 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] Crosstab Multiple Fields
Duane,
My original attempt did not include that column, but I got a runtime error that the TRANSFORM statement IIf(IsNull(IIf([Fieldname]="Upsells",SUM(trk.LengthOfStay),Count(trk.StaffID))),0,IIf([Fieldname]="Upsells",SUM(trk.LengthOfStay),Count(trk.StaffID))) is not part of an aggregate field. Adding it was how I got it to run.
Bob
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: 18 January 2017 10:27
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Crosstab Multiple Fields
Bob,
Try remove FieldName from the Group By.
Regards,
Duane
From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of 'Bob Phillips' bob.phillips@dsl.pipex.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Wednesday, January 18, 2017 4:16 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Crosstab Multiple Fields
I am trying to create a crosstab report showing multiple values. I am using a technique that I found as described by Allen Browne, create a single column table with the field names as rows, add it withoiut joining in the query, and an IIF statement to test the value in this table in the TRANSFORM which determine what is aggregated.
TRANSFORM IIf(IsNull(IIf([Fieldname]="Upsells",
SUM(trk.LengthOfStay),
COUNT(trk.StaffID)
)
),
0,
IIf([Fieldname]="Upsells",SUM(trk.LengthOfStay),Count(trk.StaffID))
) AS TotalUpsells
SELECT prop.PropertyName
FROM tblColumns,
Region AS reg
INNER JOIN ((Country AS cty
INNER JOIN (Brand AS brnd
INNER JOIN (Area INNER JOIN (Division AS div
INNER JOIN (Contract AS con
INNER JOIN Property AS prop
ON con.ContractID = prop.ContractID)
ON div.DivisionID = prop.DivisionID)
ON area.AreaID = prop.AreaID)
ON brnd.BrandID = prop.BrandID)
ON cty.CountryID = prop.CountryID)
INNER JOIN Tracking AS trk
ON prop.PropertyID = trk.PropertyID)
ON reg.RegionID = prop.RegionID
WHERE trk.TrackingYear = 2016
GROUP BY reg.RegionID, prop.PropertyName, FieldName PIVOT Fieldname & reg.RegionID;
It works to an extent, but I get two lines per property, the first shows the nights, the second shows the upsells. Allen’s example creates single rows.
Can anyone give me a steer as to what to change to get them on the same line?
TIA
Posted by: Duane Hookom <duanehookom@hotmail.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (7) |
Tidak ada komentar:
Posting Komentar