how do I create a crosstab query that does not use numeric values

[Bart Tse]

I would like to create a query that would display as follows:

Name Company Aug 2, 04 Dec 2, 05 Mar 14, 05
Doe, John XYZ Yes No Yes
Doe, Harry ABC No No Yes

This would be based on a query with the column headers: Name, Company
Represented, Date, and Attended.
I understand that it may not be possible to add in company, that's ok.
I would like the dates to pivot and then display as columns and the "attend"
data(setup as a yes/no field) to be displayed underneath.
The dates are based on meetings that occour a couple times a year and do not
necessarily repeat from year to year.
Right now all I can get is the name, and dates but I get a 1 for yes and
blank for no in the data fields.

I would appreciate any help, Thanks

 

[Duane Hookom]

Set your "value" to and expression like:
TheValue: IIf([Attend],"Yes","No")
You can also add the Company field as another Row Heading.

 

[Bart Tse]

Thanks, the company row heading works great, but I'm not sure where exactly I
am supposed to put "TheValue: IIf([Attend],"Yes","No")". Also what do you
mean by set your "value". Is that value under the Crosstab row in the design
view? and where is the expression you refrenced.

Thanks for you help

Set your "value" to and expression like:
TheValue: IIf([Attend],"Yes","No")
You can also add the Company field as another Row Heading.

--
Duane Hookom
MS Access MVP
--

I would like to create a query that would display as follows:

Name Company Aug 2, 04 Dec 2, 05 Mar 14, 05
Doe, John XYZ Yes No Yes
Doe, Harry ABC No No Yes

This would be based on a query with the column headers: Name, Company
Represented, Date, and Attended.
I understand that it may not be possible to add in company, that's ok.
I would like the dates to pivot and then display as columns and the
"attend"
data(setup as a yes/no field) to be displayed underneath.
The dates are based on meetings that occour a couple times a year and do
not
necessarily repeat from year to year.
Right now all I can get is the name, and dates but I get a 1 for yes and
blank for no in the data fields.

I would appreciate any help, Thanks

 

[Duane Hookom]

A crosstab must have:
One Column Heading
At least one Row Heading
One Value
I provided an expression to use for the "Value".
If you can't figure this out, reply back with your SQL view of your
crosstab.

--
Duane Hookom
MS Access MVP
--

Thanks, the company row heading works great, but I'm not sure where
exactly I
am supposed to put "TheValue: IIf([Attend],"Yes","No")". Also what do you
mean by set your "value". Is that value under the Crosstab row in the
design
view? and where is the expression you refrenced.

Thanks for you help

Set your "value" to and expression like:
TheValue: IIf([Attend],"Yes","No")
You can also add the Company field as another Row Heading.

--
Duane Hookom
MS Access MVP
--

I would like to create a query that would display as follows:

Name Company Aug 2, 04 Dec 2, 05 Mar 14, 05
Doe, John XYZ Yes No Yes
Doe, Harry ABC No No Yes

This would be based on a query with the column headers: Name, Company
Represented, Date, and Attended.
I understand that it may not be possible to add in company, that's ok.
I would like the dates to pivot and then display as columns and the
"attend"
data(setup as a yes/no field) to be displayed underneath.
The dates are based on meetings that occour a couple times a year and
do
not
necessarily repeat from year to year.
Right now all I can get is the name, and dates but I get a 1 for yes
and
blank for no in the data fields.

I would appreciate any help, Thanks

 

[Bart Tse]

I have one column and one value heading and two row headings.
In the design view for the value heading I set the "Field" to "TheValue:
IIf([Attended],"Yes","No")" and "Total" to "Expression". When I try to
execute the query it tells me that I "Tried to execute a query that does not
includ the specified expresstion 'IIf([Attended],"Yes","No")' as part of an
aggregate function."

Here is the SQL.
TRANSFORM IIf([Attended],"Yes","No") AS TheValue
SELECT [Six Sigma Focus Group Attendence].Name AS ID, [Six Sigma Focus Group
Attendence].[Company Represented]
FROM [Six Sigma Focus Group Attendence]
GROUP BY [Six Sigma Focus Group Attendence].Name, [Six Sigma Focus Group
Attendence].[Company Represented]
PIVOT [Six Sigma Focus Group Attendence].[Meeting Date];

Thanks again for your help.

A crosstab must have:
One Column Heading
At least one Row Heading
One Value
I provided an expression to use for the "Value".
If you can't figure this out, reply back with your SQL view of your
crosstab.

--
Duane Hookom
MS Access MVP
--

Thanks, the company row heading works great, but I'm not sure where
exactly I
am supposed to put "TheValue: IIf([Attend],"Yes","No")". Also what do you
mean by set your "value". Is that value under the Crosstab row in the
design
view? and where is the expression you refrenced.

Thanks for you help

Set your "value" to and expression like:
TheValue: IIf([Attend],"Yes","No")
You can also add the Company field as another Row Heading.

--
Duane Hookom
MS Access MVP
--

I would like to create a query that would display as follows:

Name Company Aug 2, 04 Dec 2, 05 Mar 14, 05
Doe, John XYZ Yes No Yes
Doe, Harry ABC No No Yes

This would be based on a query with the column headers: Name, Company
Represented, Date, and Attended.
I understand that it may not be possible to add in company, that's ok.
I would like the dates to pivot and then display as columns and the
"attend"
data(setup as a yes/no field) to be displayed underneath.
The dates are based on meetings that occour a couple times a year and
do
not
necessarily repeat from year to year.
Right now all I can get is the name, and dates but I get a 1 for yes
and
blank for no in the data fields.

I would appreciate any help, Thanks

 

[Duane Hookom]

You need to specify "First" or "Max" or something for the Totals line of the
value.
TRANSFORM First(IIf([Attended],"Yes","No")) AS TheValue


--
Duane Hookom
MS Access MVP
--

I have one column and one value heading and two row headings.
In the design view for the value heading I set the "Field" to "TheValue:
IIf([Attended],"Yes","No")" and "Total" to "Expression". When I try to
execute the query it tells me that I "Tried to execute a query that does
not
includ the specified expresstion 'IIf([Attended],"Yes","No")' as part of
an
aggregate function."

Here is the SQL.
TRANSFORM IIf([Attended],"Yes","No") AS TheValue
SELECT [Six Sigma Focus Group Attendence].Name AS ID, [Six Sigma Focus
Group
Attendence].[Company Represented]
FROM [Six Sigma Focus Group Attendence]
GROUP BY [Six Sigma Focus Group Attendence].Name, [Six Sigma Focus Group
Attendence].[Company Represented]
PIVOT [Six Sigma Focus Group Attendence].[Meeting Date];

Thanks again for your help.

A crosstab must have:
One Column Heading
At least one Row Heading
One Value
I provided an expression to use for the "Value".
If you can't figure this out, reply back with your SQL view of your
crosstab.

--
Duane Hookom
MS Access MVP
--

Thanks, the company row heading works great, but I'm not sure where
exactly I
am supposed to put "TheValue: IIf([Attend],"Yes","No")". Also what do
you
mean by set your "value". Is that value under the Crosstab row in the
design
view? and where is the expression you refrenced.

Thanks for you help

:

Set your "value" to and expression like:
TheValue: IIf([Attend],"Yes","No")
You can also add the Company field as another Row Heading.

--
Duane Hookom
MS Access MVP
--

I would like to create a query that would display as follows:

Name Company Aug 2, 04 Dec 2, 05 Mar 14, 05
Doe, John XYZ Yes No Yes
Doe, Harry ABC No No Yes

This would be based on a query with the column headers: Name,
Company
Represented, Date, and Attended.
I understand that it may not be possible to add in company, that's
ok.
I would like the dates to pivot and then display as columns and the
"attend"
data(setup as a yes/no field) to be displayed underneath.
The dates are based on meetings that occour a couple times a year
and
do
not
necessarily repeat from year to year.
Right now all I can get is the name, and dates but I get a 1 for yes
and
blank for no in the data fields.

I would appreciate any help, Thanks

 

[Bart Tse]

That worked, thanks so much.

You need to specify "First" or "Max" or something for the Totals line of the
value.
TRANSFORM First(IIf([Attended],"Yes","No")) AS TheValue


--
Duane Hookom
MS Access MVP
--

I have one column and one value heading and two row headings.
In the design view for the value heading I set the "Field" to "TheValue:
IIf([Attended],"Yes","No")" and "Total" to "Expression". When I try to
execute the query it tells me that I "Tried to execute a query that does
not
includ the specified expresstion 'IIf([Attended],"Yes","No")' as part of
an
aggregate function."

Here is the SQL.
TRANSFORM IIf([Attended],"Yes","No") AS TheValue
SELECT [Six Sigma Focus Group Attendence].Name AS ID, [Six Sigma Focus
Group
Attendence].[Company Represented]
FROM [Six Sigma Focus Group Attendence]
GROUP BY [Six Sigma Focus Group Attendence].Name, [Six Sigma Focus Group
Attendence].[Company Represented]
PIVOT [Six Sigma Focus Group Attendence].[Meeting Date];

Thanks again for your help.

A crosstab must have:
One Column Heading
At least one Row Heading
One Value
I provided an expression to use for the "Value".
If you can't figure this out, reply back with your SQL view of your
crosstab.

--
Duane Hookom
MS Access MVP
--

Thanks, the company row heading works great, but I'm not sure where
exactly I
am supposed to put "TheValue: IIf([Attend],"Yes","No")". Also what do
you
mean by set your "value". Is that value under the Crosstab row in the
design
view? and where is the expression you refrenced.

Thanks for you help

:

Set your "value" to and expression like:
TheValue: IIf([Attend],"Yes","No")
You can also add the Company field as another Row Heading.

--
Duane Hookom
MS Access MVP
--

I would like to create a query that would display as follows:

Name Company Aug 2, 04 Dec 2, 05 Mar 14, 05
Doe, John XYZ Yes No Yes
Doe, Harry ABC No No Yes

This would be based on a query with the column headers: Name,
Company
Represented, Date, and Attended.
I understand that it may not be possible to add in company, that's
ok.
I would like the dates to pivot and then display as columns and the
"attend"
data(setup as a yes/no field) to be displayed underneath.
The dates are based on meetings that occour a couple times a year
and
do
not
necessarily repeat from year to year.
Right now all I can get is the name, and dates but I get a 1 for yes
and
blank for no in the data fields.

I would appreciate any help, Thanks