Crosstab Combinations

[Michael Conroy]

The sales department keeps changing regions for which they are responsible.
This makes quarterly reports difficult because the area they cover is always
changing size. Currently I have RegionIDs 1-17 that represent areas of the
country and I use a crosstab query to get the results and everything works
fine. Now they are asking for two regions to be combined. So the Texas Region
(RegionID = 5) and Southwest Region (RegionID = 16) need to be combined with
a column heading of the newer name: Southwest. So if the Texas region has 50
and the new Southwest region has 15, I want the Southwest column in the
crosstab to give me 65. I realize I could alter the data and do an update
query replacing all the fives with sixteen but I would like to keep the
historical data for other reasons. As always, you help is very much
appreciated. Thanks

 

[John Spencer]

Could you force the Texas into the Southwest using an IIF clause?

IIF (TableName.RegionID=5,16,TableName.RegionID) as RegionID

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Michael Conroy]

John:
I thought of that but I would need three or four IIF to handle all the
region merges and that doesn't seem right. I read another post here, where
the guy wanted everyone whose last name came in the first half of the
alphabet and he used and expression [Like A-M]* and I was wondering how you
could do this with numbers. What I am looking for is an SQL statement that
says count all the records where the RegionID is like 5 or 16 and put that in
the Southwest column? Then another that says count all the records where the
RegionID is like 3 and 17 and put that in Gulf. This will be an ongoing
problem as they seem to redraw the lines every year so I need to figure out a
clean way of handling this. Below is my current SQL with your suggestion. I
get numbers for Region 16 (Southwest) but they are not the totals of Region 5
(Texas) and 16 combined. Probably my SQL is wrong. Anyway, I thank you for
taking the time to look at this.

TRANSFORM Count([tbl Test Data].LogID) AS CountOfLogID
SELECT [tbl Business].BusinessShort, [tbl Source].SourceShort, [tbl
Party].PartyShort
FROM ((([tbl Test Data] LEFT JOIN [tbl Party] ON [tbl Test
Data].PartyID=[tbl Party].PartyID) LEFT JOIN [tbl Source] ON [tbl Test
Data].SourceID=[tbl Source].SourceID) LEFT JOIN [tbl Region] ON [tbl Test
Data].RegionID=[tbl Region].RegionID) LEFT JOIN [tbl Business] ON [tbl Test
Data].BusinessID=[tbl Business].BusinessID
WHERE ((([tbl Test Data].BusinessID)=1) And (([tbl Test
Data].Opened)>=#1/1/2007#) And (([tbl Test Data].RegionID)=IIf([tbl Test
Data].RegionID=5,16,[tbl Test Data].Regionid)))
GROUP BY [tbl Business].BusinessShort, [tbl Source].SourceShort, [tbl
Party].PartyShort, [tbl Test Data].Opened
PIVOT [tbl Region].RegionShort;
--
Michael Conroy
Stamford, CT

Could you force the Texas into the Southwest using an IIF clause?

IIF (TableName.RegionID=5,16,TableName.RegionID) as RegionID

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[John Spencer]

Ok, then why not use an equivalence table.

TblEquivalentRegions
CurrentRegion
CombinedRegion

Then you can say
1 : 1
2 : 2
3 : 2
5 : 16
...
16 :16

Add that into the query, joining on Current Region and using CombinedRegion
to Group by, Pivot On, or whatever.

And you could expand on the idea, by adding another field (or fields) the
to the equivalence table so you could do different regional groupings based
on a date range or group name (East Coast, West Coast, MidWest)

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

John:
I thought of that but I would need three or four IIF to handle all the
region merges and that doesn't seem right. I read another post here, where
the guy wanted everyone whose last name came in the first half of the
alphabet and he used and expression [Like A-M]* and I was wondering how
you
could do this with numbers. What I am looking for is an SQL statement that
says count all the records where the RegionID is like 5 or 16 and put that
in
the Southwest column? Then another that says count all the records where
the
RegionID is like 3 and 17 and put that in Gulf. This will be an ongoing
problem as they seem to redraw the lines every year so I need to figure
out a
clean way of handling this. Below is my current SQL with your suggestion.
I
get numbers for Region 16 (Southwest) but they are not the totals of
Region 5
(Texas) and 16 combined. Probably my SQL is wrong. Anyway, I thank you for
taking the time to look at this.

TRANSFORM Count([tbl Test Data].LogID) AS CountOfLogID
SELECT [tbl Business].BusinessShort, [tbl Source].SourceShort, [tbl
Party].PartyShort
FROM ((([tbl Test Data] LEFT JOIN [tbl Party] ON [tbl Test
Data].PartyID=[tbl Party].PartyID) LEFT JOIN [tbl Source] ON [tbl Test
Data].SourceID=[tbl Source].SourceID) LEFT JOIN [tbl Region] ON [tbl Test
Data].RegionID=[tbl Region].RegionID) LEFT JOIN [tbl Business] ON [tbl
Test
Data].BusinessID=[tbl Business].BusinessID
WHERE ((([tbl Test Data].BusinessID)=1) And (([tbl Test
Data].Opened)>=#1/1/2007#) And (([tbl Test Data].RegionID)=IIf([tbl Test
Data].RegionID=5,16,[tbl Test Data].Regionid)))
GROUP BY [tbl Business].BusinessShort, [tbl Source].SourceShort, [tbl
Party].PartyShort, [tbl Test Data].Opened
PIVOT [tbl Region].RegionShort;

 

[Michael Conroy]

John:
That's what I came up with last night. In the region table I will add a
"current" regionID field and each region will roll up into whatever the
current one is. This will give me the original number in one column and the
"roll up" number in another as you suggested. That way I can maintain the
historical data, the region the inquiry originated in and roll it up into
whatever is the newest area. Thanks again for your help.
--
Michael Conroy
Stamford, CT

Ok, then why not use an equivalence table.

TblEquivalentRegions
CurrentRegion
CombinedRegion

Then you can say
1 : 1
2 : 2
3 : 2
5 : 16
...
16 :16

Add that into the query, joining on Current Region and using CombinedRegion
to Group by, Pivot On, or whatever.

And you could expand on the idea, by adding another field (or fields) the
to the equivalence table so you could do different regional groupings based
on a date range or group name (East Coast, West Coast, MidWest)

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

John:
I thought of that but I would need three or four IIF to handle all the
region merges and that doesn't seem right. I read another post here, where
the guy wanted everyone whose last name came in the first half of the
alphabet and he used and expression [Like A-M]* and I was wondering how
you
could do this with numbers. What I am looking for is an SQL statement that
says count all the records where the RegionID is like 5 or 16 and put that
in
the Southwest column? Then another that says count all the records where
the
RegionID is like 3 and 17 and put that in Gulf. This will be an ongoing
problem as they seem to redraw the lines every year so I need to figure
out a
clean way of handling this. Below is my current SQL with your suggestion.
I
get numbers for Region 16 (Southwest) but they are not the totals of
Region 5
(Texas) and 16 combined. Probably my SQL is wrong. Anyway, I thank you for
taking the time to look at this.

TRANSFORM Count([tbl Test Data].LogID) AS CountOfLogID
SELECT [tbl Business].BusinessShort, [tbl Source].SourceShort, [tbl
Party].PartyShort
FROM ((([tbl Test Data] LEFT JOIN [tbl Party] ON [tbl Test
Data].PartyID=[tbl Party].PartyID) LEFT JOIN [tbl Source] ON [tbl Test
Data].SourceID=[tbl Source].SourceID) LEFT JOIN [tbl Region] ON [tbl Test
Data].RegionID=[tbl Region].RegionID) LEFT JOIN [tbl Business] ON [tbl
Test
Data].BusinessID=[tbl Business].BusinessID
WHERE ((([tbl Test Data].BusinessID)=1) And (([tbl Test
Data].Opened)>=#1/1/2007#) And (([tbl Test Data].RegionID)=IIf([tbl Test
Data].RegionID=5,16,[tbl Test Data].Regionid)))
GROUP BY [tbl Business].BusinessShort, [tbl Source].SourceShort, [tbl
Party].PartyShort, [tbl Test Data].Opened
PIVOT [tbl Region].RegionShort;
--
Michael Conroy
Stamford, CT

Could you force the Texas into the Southwest using an IIF clause?

IIF (TableName.RegionID=5,16,TableName.RegionID) as RegionID

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

message
The sales department keeps changing regions for which they are
responsible.
This makes quarterly reports difficult because the area they cover is
always
changing size. Currently I have RegionIDs 1-17 that represent areas of
the
country and I use a crosstab query to get the results and everything
works
fine. Now they are asking for two regions to be combined. So the Texas
Region
(RegionID = 5) and Southwest Region (RegionID = 16) need to be combined
with
a column heading of the newer name: Southwest. So if the Texas region
has
50
and the new Southwest region has 15, I want the Southwest column in the
crosstab to give me 65. I realize I could alter the data and do an
update
query replacing all the fives with sixteen but I would like to keep the
historical data for other reasons. As always, you help is very much
appreciated. Thanks