I thought it was because you avoided two different aggregate functions
Duane, but I see what you mean now.
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com]
Sent: 18 January 2017 15:48
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Crosstab Multiple Fields
Bob,
Your SQL was performing the SUM() inside the IIf() and mine is doing it
around the IIf().
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 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
<mailto:MS_Access_Professionals@yahoogroups.com>
<MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals@yahoogroups.com> > on behalf of 'Bob
Phillips' bob.phillips@dsl.pipex.com <mailto:bob.phillips@dsl.pipex.com>
[MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals@yahoogroups.com> >
Sent: Wednesday, January 18, 2017 4:53 AM
To: MS_Access_Professionals@yahoogroups.com
<mailto: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>
[mailto:MS_Access_Professionals@yahoogroups.com]
Sent: 18 January 2017 10:27
To: MS_Access_Professionals@yahoogroups.com
<mailto: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
<mailto:MS_Access_Professionals@yahoogroups.com>
<MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals@yahoogroups.com> > on behalf of 'Bob
Phillips' bob.phillips@dsl.pipex.com <mailto:bob.phillips@dsl.pipex.com>
[MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals@yahoogroups.com> >
Sent: Wednesday, January 18, 2017 4:16 AM
To: MS_Access_Professionals@yahoogroups.com
<mailto: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
[Non-text portions of this message have been removed]
Posted by: "Bob Phillips" <bob.phillips@dsl.pipex.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (8) |
Tidak ada komentar:
Posting Komentar