Toggle Fields in Crosstab Query

[Guest]

Hello,

I know that in a crosstab query you can only have one value field. Is it
possible to create a form or parameter prompt to ask the person running the
query which field to use between three options?

Thanks!

Ari

 

[David Lloyd]

Ari:

One option is to create a form and build the crosstab query SQL syntax
dynamically based on the field chosen on the form.

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


Hello,

I know that in a crosstab query you can only have one value field. Is it
possible to create a form or parameter prompt to ask the person running the
query which field to use between three options?

Thanks!

Ari

 

[Guest]

David,

I tried the following but it doesn't like [enter value] as the variable for
the TRANSFORM line. Any thoughts?

PARAMETERS [Enter Salesperson] Text ( 255 ), [Enter S Date] DateTime, [Enter
E Date] DateTime, [Enter Value] Text ( 256);
TRANSFORM Sum([Enter Value]) AS SUM
SELECT Table1.Salesman, Table1.Customer
FROM Table1
WHERE (((Table1.Salesman)=[Enter Salesperson]) AND ((Table1.[Order Date])
Between [Enter S Date] And [Enter E Date]))
GROUP BY Table1.Salesman, Table1.Customer
PIVOT Table1.Month;

 

[Duane Hookom]

There is a method of creating a multiple value crosstab query. Try search
google groups on
Hookom crosstab multiple value group:*Access*
Changing values in the tblXTabColumns table should allow users to Sum()
different fields.
--
Duane Hookom
MS Access MVP

David,

I tried the following but it doesn't like [enter value] as the variable
for
the TRANSFORM line. Any thoughts?

PARAMETERS [Enter Salesperson] Text ( 255 ), [Enter S Date] DateTime,
[Enter
E Date] DateTime, [Enter Value] Text ( 256);
TRANSFORM Sum([Enter Value]) AS SUM
SELECT Table1.Salesman, Table1.Customer
FROM Table1
WHERE (((Table1.Salesman)=[Enter Salesperson]) AND ((Table1.[Order Date])
Between [Enter S Date] And [Enter E Date]))
GROUP BY Table1.Salesman, Table1.Customer
PIVOT Table1.Month;

Ari:

One option is to create a form and build the crosstab query SQL syntax
dynamically based on the field chosen on the form.

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or
warranties.


Hello,

I know that in a crosstab query you can only have one value field. Is it
possible to create a form or parameter prompt to ask the person running
the
query which field to use between three options?

Thanks!

Ari

 

[Guest]

Duane,

Thanks for this. I was able to create the dual value crosstab query.
However, while changing values in the tblXTabColumns table changes the column
label, it doesn't change the column that the second value field is summing.
My TRANSFORM line is:

TRANSFORM Sum(IIf([FldName]="Volume",[Volume],[Price])) AS DaVal

If I want to give the user the opportunity to change say [Price] to [Cost]
(both fields in the database for the column headings) via a Parameter or a
form that they enter, is that possible?

Thanks very much for your help.

Ari

There is a method of creating a multiple value crosstab query. Try search
google groups on
Hookom crosstab multiple value group:*Access*
Changing values in the tblXTabColumns table should allow users to Sum()
different fields.
--
Duane Hookom
MS Access MVP

David,

I tried the following but it doesn't like [enter value] as the variable
for
the TRANSFORM line. Any thoughts?

PARAMETERS [Enter Salesperson] Text ( 255 ), [Enter S Date] DateTime,
[Enter
E Date] DateTime, [Enter Value] Text ( 256);
TRANSFORM Sum([Enter Value]) AS SUM
SELECT Table1.Salesman, Table1.Customer
FROM Table1
WHERE (((Table1.Salesman)=[Enter Salesperson]) AND ((Table1.[Order Date])
Between [Enter S Date] And [Enter E Date]))
GROUP BY Table1.Salesman, Table1.Customer
PIVOT Table1.Month;

Ari:

One option is to create a form and build the crosstab query SQL syntax
dynamically based on the field chosen on the form.

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or
warranties.


Hello,

I know that in a crosstab query you can only have one value field. Is it
possible to create a form or parameter prompt to ask the person running
the
query which field to use between three options?

Thanks!

Ari

 

[Duane Hookom]

Consider adding three records to tblXTabColumns: Volume, Price, Cost. Then
use
TRANSFORM Sum(IIf([FldName]="Volume",[Volume],IIf("Price",[Price],[Cost])))
AS DaVal
If you exclude the Price field from tblXTabColumns, you will only get Price
and Cost. Removing (filtering out) one value will leave the remaining two.
--
Duane Hookom
MS Access MVP

Duane,

Thanks for this. I was able to create the dual value crosstab query.
However, while changing values in the tblXTabColumns table changes the
column
label, it doesn't change the column that the second value field is
summing.
My TRANSFORM line is:

TRANSFORM Sum(IIf([FldName]="Volume",[Volume],[Price])) AS DaVal

If I want to give the user the opportunity to change say [Price] to [Cost]
(both fields in the database for the column headings) via a Parameter or a
form that they enter, is that possible?

Thanks very much for your help.

Ari

There is a method of creating a multiple value crosstab query. Try search
google groups on
Hookom crosstab multiple value group:*Access*
Changing values in the tblXTabColumns table should allow users to Sum()
different fields.
--
Duane Hookom
MS Access MVP

David,

I tried the following but it doesn't like [enter value] as the variable
for
the TRANSFORM line. Any thoughts?

PARAMETERS [Enter Salesperson] Text ( 255 ), [Enter S Date] DateTime,
[Enter
E Date] DateTime, [Enter Value] Text ( 256);
TRANSFORM Sum([Enter Value]) AS SUM
SELECT Table1.Salesman, Table1.Customer
FROM Table1
WHERE (((Table1.Salesman)=[Enter Salesperson]) AND ((Table1.[Order
Date])
Between [Enter S Date] And [Enter E Date]))
GROUP BY Table1.Salesman, Table1.Customer
PIVOT Table1.Month;

:

Ari:

One option is to create a form and build the crosstab query SQL syntax
dynamically based on the field chosen on the form.

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or
warranties.


Hello,

I know that in a crosstab query you can only have one value field. Is
it
possible to create a form or parameter prompt to ask the person
running
the
query which field to use between three options?

Thanks!

Ari

 

[Guest]

Duane,

Thanks a bunch for this. That worked great once I added [FldName]= in the
second if statement.

TRANSFORM
Sum(IIf([FldName]="Volume",[Volume],IIf([FldName]="Price",[Price],[Cost])))

When you mention filtering out the value from the tblXTabColumns table, do
you have a suggestion for how to do that with a parameter on the form that I
am using to run the crosstab query?

I really appreciate all your help!

Ari

Consider adding three records to tblXTabColumns: Volume, Price, Cost. Then
use
TRANSFORM Sum(IIf([FldName]="Volume",[Volume],IIf("Price",[Price],[Cost])))
AS DaVal
If you exclude the Price field from tblXTabColumns, you will only get Price
and Cost. Removing (filtering out) one value will leave the remaining two.
--
Duane Hookom
MS Access MVP

Duane,

Thanks for this. I was able to create the dual value crosstab query.
However, while changing values in the tblXTabColumns table changes the
column
label, it doesn't change the column that the second value field is
summing.
My TRANSFORM line is:

TRANSFORM Sum(IIf([FldName]="Volume",[Volume],[Price])) AS DaVal

If I want to give the user the opportunity to change say [Price] to [Cost]
(both fields in the database for the column headings) via a Parameter or a
form that they enter, is that possible?

Thanks very much for your help.

Ari

There is a method of creating a multiple value crosstab query. Try search
google groups on
Hookom crosstab multiple value group:*Access*
Changing values in the tblXTabColumns table should allow users to Sum()
different fields.
--
Duane Hookom
MS Access MVP


David,

I tried the following but it doesn't like [enter value] as the variable
for
the TRANSFORM line. Any thoughts?

PARAMETERS [Enter Salesperson] Text ( 255 ), [Enter S Date] DateTime,
[Enter
E Date] DateTime, [Enter Value] Text ( 256);
TRANSFORM Sum([Enter Value]) AS SUM
SELECT Table1.Salesman, Table1.Customer
FROM Table1
WHERE (((Table1.Salesman)=[Enter Salesperson]) AND ((Table1.[Order
Date])
Between [Enter S Date] And [Enter E Date]))
GROUP BY Table1.Salesman, Table1.Customer
PIVOT Table1.Month;

:

Ari:

One option is to create a form and build the crosstab query SQL syntax
dynamically based on the field chosen on the form.

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or
warranties.


Hello,

I know that in a crosstab query you can only have one value field. Is
it
possible to create a form or parameter prompt to ask the person
running
the
query which field to use between three options?

Thanks!

Ari

 

[Duane Hookom]

Try add a yes/no field to tblXTabColumns and allow the user to check or
uncheck the field. Then filter the crosstab on only the checked boxes.

--
Duane Hookom
MS Access MVP

Duane,

Thanks a bunch for this. That worked great once I added [FldName]= in the
second if statement.

TRANSFORM
Sum(IIf([FldName]="Volume",[Volume],IIf([FldName]="Price",[Price],[Cost])))

When you mention filtering out the value from the tblXTabColumns table, do
you have a suggestion for how to do that with a parameter on the form that
I
am using to run the crosstab query?

I really appreciate all your help!

Ari

Consider adding three records to tblXTabColumns: Volume, Price, Cost.
Then
use
TRANSFORM
Sum(IIf([FldName]="Volume",[Volume],IIf("Price",[Price],[Cost])))
AS DaVal
If you exclude the Price field from tblXTabColumns, you will only get
Price
and Cost. Removing (filtering out) one value will leave the remaining
two.
--
Duane Hookom
MS Access MVP

Duane,

Thanks for this. I was able to create the dual value crosstab query.
However, while changing values in the tblXTabColumns table changes the
column
label, it doesn't change the column that the second value field is
summing.
My TRANSFORM line is:

TRANSFORM Sum(IIf([FldName]="Volume",[Volume],[Price])) AS DaVal

If I want to give the user the opportunity to change say [Price] to
[Cost]
(both fields in the database for the column headings) via a Parameter
or a
form that they enter, is that possible?

Thanks very much for your help.

Ari

:

There is a method of creating a multiple value crosstab query. Try
search
google groups on
Hookom crosstab multiple value group:*Access*
Changing values in the tblXTabColumns table should allow users to
Sum()
different fields.
--
Duane Hookom
MS Access MVP


David,

I tried the following but it doesn't like [enter value] as the
variable
for
the TRANSFORM line. Any thoughts?

PARAMETERS [Enter Salesperson] Text ( 255 ), [Enter S Date]
DateTime,
[Enter
E Date] DateTime, [Enter Value] Text ( 256);
TRANSFORM Sum([Enter Value]) AS SUM
SELECT Table1.Salesman, Table1.Customer
FROM Table1
WHERE (((Table1.Salesman)=[Enter Salesperson]) AND ((Table1.[Order
Date])
Between [Enter S Date] And [Enter E Date]))
GROUP BY Table1.Salesman, Table1.Customer
PIVOT Table1.Month;

:

Ari:

One option is to create a form and build the crosstab query SQL
syntax
dynamically based on the field chosen on the form.

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or
warranties.


Hello,

I know that in a crosstab query you can only have one value field.
Is
it
possible to create a form or parameter prompt to ask the person
running
the
query which field to use between three options?

Thanks!

Ari