Cross tab query using the value also as Columns

[Krish]

I have a table with fields-Sales person, Date, Invoice # and Margin $. I am trying to create a CrossTab query with Salesperson in Rows, Margin $ as Value and also use Marin $ in columns to group >$1000, Between $1000 and $ 2500, Between $ 2500 and $ 5000, > $5000. How to write the user defined Grouping for such column head?

Thanks.
K

 

[steve]

sorry

you cant do this with microsoft access 2003.

you must upgrade to microsoft access 2004:
http://www.ammara.com/access_image_faq/access_loading_image_dialog.html


HTH

Steve

I have a table with fields-Sales person, Date, Invoice # and Margin $. I am trying to create a CrossTab query with Salesperson in Rows, Margin $ as Value and also use Marin $ in columns to group >$1000, Between $1000 and $ 2500, Between $ 2500 and $ 5000, > $5000. How to write the user defined Grouping for such column head?

Thanks.
K

 

[Neil Sunderland]

steve wrote (for - at least - the third time):

sorry

you cant do this with microsoft access 2003.

you must upgrade to microsoft access 2004:
http://www.ammara.com/access_image_faq/access_loading_image_dialog.html

Wouldn't it be nice if there was a newsreader built on an Access
database? Then you could do stuff like this:

INSERT INTO
tblTroll (username, email)
VALUES
('steve', '(e-mail address removed)')

DELETE *
FROM
microsoft.public.access.queries
WHERE
from _header IN (SELECT email FROM tblTroll)

 

[Michel Walsh]

Hi,


Customize the PIVOT clause:

....
PIVOT Switch(Margin<1000, "<1000$", Margin <2500, "1000-2500$", Margin <
5000, "2500-5000$", true, ">5000$")


You can also use an inner join (rather than typing all these constants in
the code), with a table like:

GroupName LowerLimit UpperLimit
"<1000$" 0 1000
"1000-2500" 1000 2500
....


and


TRANSFORM......
FROM myTable INNER JOIN rangesTable
ON myTable.Margin >= rangesTable.LowerLimit
AND myTable.Margin < rangesTable.LowerLimit
GROUP BY SalesPerson
PIVOT rangesTable.GroupName


Hoping it may help,
Vanderghast, Access MVP

I have a table with fields-Sales person, Date, Invoice # and Margin $. I am
trying to create a CrossTab query with Salesperson in Rows, Margin $ as
Value and also use Marin $ in columns to group >$1000, Between $1000 and $
2500, Between $ 2500 and $ 5000, > $5000. How to write the user defined
Grouping for such column head?

Thanks.
K

 

[Krish]

Thank you so much for the best suggestion. Creating a new table as shown by you really helped me. Using the inner join, design view is disabled. However is there an easier way in SQL to define the properties of the field, especially on the value to limit to "2" decimals and use Comma separator?

Krish
Hi,


Customize the PIVOT clause:

...
PIVOT Switch(Margin<1000, "<1000$", Margin <2500, "1000-2500$", Margin <
5000, "2500-5000$", true, ">5000$")


You can also use an inner join (rather than typing all these constants in
the code), with a table like:

GroupName LowerLimit UpperLimit
"<1000$" 0 1000
"1000-2500" 1000 2500
...


and


TRANSFORM......
FROM myTable INNER JOIN rangesTable
ON myTable.Margin >= rangesTable.LowerLimit
AND myTable.Margin < rangesTable.LowerLimit
GROUP BY SalesPerson
PIVOT rangesTable.GroupName


Hoping it may help,
Vanderghast, Access MVP

I have a table with fields-Sales person, Date, Invoice # and Margin $. I am
trying to create a CrossTab query with Salesperson in Rows, Margin $ as
Value and also use Marin $ in columns to group >$1000, Between $1000 and $
2500, Between $ 2500 and $ 5000, > $5000. How to write the user defined
Grouping for such column head?

Thanks.
K

 

[Michel Walsh]

Hi,

That is probably best done with the report / form than with the query
itself, since it is a matter of formatting a value, but you can, within
Access, define the desired default format for your columns by right clicking
on it... in the design view... assuming that the column exists (which is not
the case for all columns, in a XTab, unfortunately). So, you are probably
left by choosing the format from a control in a FORM. As for the decimal
delimiter, it would be the one selected by the end user (you cannot easily
specify a different "culture", in VBA, as least, not as easily as in
dot-Net).


Hoping it may help,
Vanderghast, Access MVP


Thank you so much for the best suggestion. Creating a new table as shown by
you really helped me. Using the inner join, design view is disabled. However
is there an easier way in SQL to define the properties of the field,
especially on the value to limit to "2" decimals and use Comma separator?

Krish
Hi,


Customize the PIVOT clause:

...
PIVOT Switch(Margin<1000, "<1000$", Margin <2500, "1000-2500$", Margin <
5000, "2500-5000$", true, ">5000$")


You can also use an inner join (rather than typing all these constants in
the code), with a table like:

GroupName LowerLimit UpperLimit
"<1000$" 0 1000
"1000-2500" 1000 2500
...


and


TRANSFORM......
FROM myTable INNER JOIN rangesTable
ON myTable.Margin >= rangesTable.LowerLimit
AND myTable.Margin < rangesTable.LowerLimit
GROUP BY SalesPerson
PIVOT rangesTable.GroupName


Hoping it may help,
Vanderghast, Access MVP

I have a table with fields-Sales person, Date, Invoice # and Margin $. I
am
trying to create a CrossTab query with Salesperson in Rows, Margin $ as
Value and also use Marin $ in columns to group >$1000, Between $1000 and $
2500, Between $ 2500 and $ 5000, > $5000. How to write the user defined
Grouping for such column head?

Thanks.
K

 

[Michel Walsh]

Hi,



For the Inner join problem, do it in a query, say query1, then, built your
crosstab on that saved query, query1.


Hoping it may help,
Vanderghast, Access MVP

Thank you so much for the best suggestion. Creating a new table as shown by
you really helped me. Using the inner join, design view is disabled. However
is there an easier way in SQL to define the properties of the field,
especially on the value to limit to "2" decimals and use Comma separator?

Krish
Hi,


Customize the PIVOT clause:

...
PIVOT Switch(Margin<1000, "<1000$", Margin <2500, "1000-2500$", Margin <
5000, "2500-5000$", true, ">5000$")


You can also use an inner join (rather than typing all these constants in
the code), with a table like:

GroupName LowerLimit UpperLimit
"<1000$" 0 1000
"1000-2500" 1000 2500
...


and


TRANSFORM......
FROM myTable INNER JOIN rangesTable
ON myTable.Margin >= rangesTable.LowerLimit
AND myTable.Margin < rangesTable.LowerLimit
GROUP BY SalesPerson
PIVOT rangesTable.GroupName


Hoping it may help,
Vanderghast, Access MVP

I have a table with fields-Sales person, Date, Invoice # and Margin $. I
am
trying to create a CrossTab query with Salesperson in Rows, Margin $ as
Value and also use Marin $ in columns to group >$1000, Between $1000 and $
2500, Between $ 2500 and $ 5000, > $5000. How to write the user defined
Grouping for such column head?

Thanks.
K