Using Columns in a Crosstab Query as fields in another query

A

Amy E. Baggott

I have a crosstab query that lists the exhibitors with their square footage
from each show they've been in. I have another query that calculates a point
bonus for them based on their square footage in the last five shows.
However, this requires me to rewrite the bonus query every year, as the field
names will change each year. Is there any way to set this up so I don't have
to rewrite the fields (particularly a calculated field) every year? I'm
afraid I may forget, and I know that if I'm not here, nobody here would know
how to do it.
 
D

Duane Hookom

I'm not exactly sure what you want but consider the Orders and Customers
tables in Northwind. This SQL will sum the Freight for the most recent 5
orders for each customer:

SELECT Customers.CustomerID, Customers.CompanyName,
Sum(Orders.Freight) AS SumOfFreight
FROM Customers INNER JOIN
Orders ON Customers.CustomerID = Orders.CustomerID
WHERE OrderID In (SELECT TOP 5 OrderID
FROM ORDERS O
WHERE O.CustomerID=Orders.CustomerID
ORDER BY OrderDate Desc)
GROUP BY Customers.CustomerID, CompanyName;
 
A

Amy E. Baggott

The expression for the bonus points is

BonusPoints: IIf(IsNull([IBX02]) Or IsNull([IBX03]) Or IsNull([IBX04]) Or
IsNull([IBX05]) Or [IBX02]>=[IBSX01],0,IIf([IBX03]>=[IBX02] And
[IBX04]>=[IBX03] And [IBX05]>=[IBX04],4,0))

However, the field names for next year will change to IBX02, IBX03, IBX04,
IBX05, and IBX06. What I'd like to do is somehow set it up so that the query
doesn't have to be rewritten every year to change the field names because the
PITA factor there gets to be huge, plus I want to set this up so that other
people can run the query besides me.
--
Amy E. Baggott

"I''m going crazy and I''m taking all of you with me!" -- Linda Grayson


Duane Hookom said:
I'm not exactly sure what you want but consider the Orders and Customers
tables in Northwind. This SQL will sum the Freight for the most recent 5
orders for each customer:

SELECT Customers.CustomerID, Customers.CompanyName,
Sum(Orders.Freight) AS SumOfFreight
FROM Customers INNER JOIN
Orders ON Customers.CustomerID = Orders.CustomerID
WHERE OrderID In (SELECT TOP 5 OrderID
FROM ORDERS O
WHERE O.CustomerID=Orders.CustomerID
ORDER BY OrderDate Desc)
GROUP BY Customers.CustomerID, CompanyName;
Click to expand...
 
D

Duane Hookom

Assuming IBX02 is generated as a column heading in a crosstab, re-evaluate
how you create the column heading expression. I think you can replace the
"02","03" etc with a number that is relative to the current year.

If you need more help, reply back the with SQL view of your crosstab.

--
Duane Hookom
MS Access MVP
--

Amy E. Baggott said:
The expression for the bonus points is

BonusPoints: IIf(IsNull([IBX02]) Or IsNull([IBX03]) Or IsNull([IBX04]) Or
IsNull([IBX05]) Or [IBX02]>=[IBSX01],0,IIf([IBX03]>=[IBX02] And
[IBX04]>=[IBX03] And [IBX05]>=[IBX04],4,0))

However, the field names for next year will change to IBX02, IBX03, IBX04,
IBX05, and IBX06. What I'd like to do is somehow set it up so that the
query
doesn't have to be rewritten every year to change the field names because
the
PITA factor there gets to be huge, plus I want to set this up so that
other
people can run the query besides me.
Click to expand...
 
A

Amy E. Baggott

Actually, that column heading comes from the value of a show ID field.
Here's the SQL for the crosstab query:

TRANSFORM Avg(qrySqFtHistory.SqFt) AS AvgOfSqFt
SELECT qrySqFtHistory.exh_masid, qrySqFtHistory.[Company Name],
Avg(qrySqFtHistory.SqFt) AS [Total Of SqFt]
FROM qrySqFtHistory
GROUP BY qrySqFtHistory.exh_masid, qrySqFtHistory.[Company Name]
ORDER BY qrySqFtHistory.tex_showid DESC
PIVOT qrySqFtHistory.tex_showid;

And here's the SQL for the bonus points query:

SELECT qrySqFtHistory_Crosstab.exh_masid, qrySqFtHistory_Crosstab.IBSX01,
qrySqFtHistory_Crosstab.IBX02, qrySqFtHistory_Crosstab.IBX03,
qrySqFtHistory_Crosstab.IBX04, qrySqFtHistory_Crosstab.IBX05,
IIf(IsNull([IBX02]) Or IsNull([IBX03]) Or IsNull([IBX04]) Or IsNull([IBX05])
Or [IBX02]>=[IBSX01],0,IIf([IBX03]>=[IBX02] And [IBX04]>=[IBX03] And
[IBX05]>=[IBX04],4,0)) AS BonusPoints
FROM qrySqFtHistory_Crosstab;

I tried using the Column Headings property to simply give the columns
different headings, but then no data at all showed up. Am I going about the
whole thing all wrong?
--
Amy E. Baggott

"I''m going crazy and I''m taking all of you with me!" -- Linda Grayson


Duane Hookom said:
Assuming IBX02 is generated as a column heading in a crosstab, re-evaluate
how you create the column heading expression. I think you can replace the
"02","03" etc with a number that is relative to the current year.

If you need more help, reply back the with SQL view of your crosstab.

--
Duane Hookom
MS Access MVP
--

Amy E. Baggott said:
The expression for the bonus points is

BonusPoints: IIf(IsNull([IBX02]) Or IsNull([IBX03]) Or IsNull([IBX04]) Or
IsNull([IBX05]) Or [IBX02]>=[IBSX01],0,IIf([IBX03]>=[IBX02] And
[IBX04]>=[IBX03] And [IBX05]>=[IBX04],4,0))

However, the field names for next year will change to IBX02, IBX03, IBX04,
IBX05, and IBX06. What I'd like to do is somehow set it up so that the
query
doesn't have to be rewritten every year to change the field names because
the
PITA factor there gets to be huge, plus I want to set this up so that
other
people can run the query besides me.
--
Amy E. Baggott

"I''m going crazy and I''m taking all of you with me!" -- Linda Grayson


Duane Hookom said:
I'm not exactly sure what you want but consider the Orders and Customers
tables in Northwind. This SQL will sum the Freight for the most recent 5
orders for each customer:

SELECT Customers.CustomerID, Customers.CompanyName,
Sum(Orders.Freight) AS SumOfFreight
FROM Customers INNER JOIN
Orders ON Customers.CustomerID = Orders.CustomerID
WHERE OrderID In (SELECT TOP 5 OrderID
FROM ORDERS O
WHERE O.CustomerID=Orders.CustomerID
ORDER BY OrderDate Desc)
GROUP BY Customers.CustomerID, CompanyName;


--
Duane Hookom
MS Access MVP
--

I have a crosstab query that lists the exhibitors with their square
footage
from each show they've been in. I have another query that calculates a
point
bonus for them based on their square footage in the last five shows.
However, this requires me to rewrite the bonus query every year, as the
field
names will change each year. Is there any way to set this up so I
don't
have
to rewrite the fields (particularly a calculated field) every year?
I'm
afraid I may forget, and I know that if I'm not here, nobody here would
know
how to do it.
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

You could:
1) add a field to your table of shows to create a column heading for your
crosstab. Each year you would change the values in this field.
or
2) use PIVOT "yr" & Val(Format(Date(),"yy") - Val(Right(tex_ShowID,2))

--
Duane Hookom
MS Access MVP
--

Amy E. Baggott said:
Actually, that column heading comes from the value of a show ID field.
Here's the SQL for the crosstab query:

TRANSFORM Avg(qrySqFtHistory.SqFt) AS AvgOfSqFt
SELECT qrySqFtHistory.exh_masid, qrySqFtHistory.[Company Name],
Avg(qrySqFtHistory.SqFt) AS [Total Of SqFt]
FROM qrySqFtHistory
GROUP BY qrySqFtHistory.exh_masid, qrySqFtHistory.[Company Name]
ORDER BY qrySqFtHistory.tex_showid DESC
PIVOT qrySqFtHistory.tex_showid;

And here's the SQL for the bonus points query:

SELECT qrySqFtHistory_Crosstab.exh_masid, qrySqFtHistory_Crosstab.IBSX01,
qrySqFtHistory_Crosstab.IBX02, qrySqFtHistory_Crosstab.IBX03,
qrySqFtHistory_Crosstab.IBX04, qrySqFtHistory_Crosstab.IBX05,
IIf(IsNull([IBX02]) Or IsNull([IBX03]) Or IsNull([IBX04]) Or
IsNull([IBX05])
Or [IBX02]>=[IBSX01],0,IIf([IBX03]>=[IBX02] And [IBX04]>=[IBX03] And
[IBX05]>=[IBX04],4,0)) AS BonusPoints
FROM qrySqFtHistory_Crosstab;

I tried using the Column Headings property to simply give the columns
different headings, but then no data at all showed up. Am I going about
the
whole thing all wrong?
--
Amy E. Baggott

"I''m going crazy and I''m taking all of you with me!" -- Linda Grayson


Duane Hookom said:
Assuming IBX02 is generated as a column heading in a crosstab,
re-evaluate
how you create the column heading expression. I think you can replace the
"02","03" etc with a number that is relative to the current year.

If you need more help, reply back the with SQL view of your crosstab.

--
Duane Hookom
MS Access MVP
--

Amy E. Baggott said:
The expression for the bonus points is

BonusPoints: IIf(IsNull([IBX02]) Or IsNull([IBX03]) Or IsNull([IBX04])
Or
IsNull([IBX05]) Or [IBX02]>=[IBSX01],0,IIf([IBX03]>=[IBX02] And
[IBX04]>=[IBX03] And [IBX05]>=[IBX04],4,0))

However, the field names for next year will change to IBX02, IBX03,
IBX04,
IBX05, and IBX06. What I'd like to do is somehow set it up so that the
query
doesn't have to be rewritten every year to change the field names
because
the
PITA factor there gets to be huge, plus I want to set this up so that
other
people can run the query besides me.
--
Amy E. Baggott

"I''m going crazy and I''m taking all of you with me!" -- Linda Grayson


:

I'm not exactly sure what you want but consider the Orders and
Customers
tables in Northwind. This SQL will sum the Freight for the most recent
5
orders for each customer:

SELECT Customers.CustomerID, Customers.CompanyName,
Sum(Orders.Freight) AS SumOfFreight
FROM Customers INNER JOIN
Orders ON Customers.CustomerID = Orders.CustomerID
WHERE OrderID In (SELECT TOP 5 OrderID
FROM ORDERS O
WHERE O.CustomerID=Orders.CustomerID
ORDER BY OrderDate Desc)
GROUP BY Customers.CustomerID, CompanyName;


--
Duane Hookom
MS Access MVP
--

message
I have a crosstab query that lists the exhibitors with their square
footage
from each show they've been in. I have another query that
calculates a
point
bonus for them based on their square footage in the last five shows.
However, this requires me to rewrite the bonus query every year, as
the
field
names will change each year. Is there any way to set this up so I
don't
have
to rewrite the fields (particularly a calculated field) every year?
I'm
afraid I may forget, and I know that if I'm not here, nobody here
would
know
how to do it.
--
Amy E. Baggott

"I''m going crazy and I''m taking all of you with me!" -- Linda
Grayson
Click to expand...
Click to expand...
Click to expand...
 
A

Amy E. Baggott

I don't understand the second option. Would this be put in the column
headings or used as a field in the crosstab or bonus query? I'm not at all
familiar with PIVOT, so please bear with me.
--
Amy E. Baggott

"I''m going crazy and I''m taking all of you with me!" -- Linda Grayson


Duane Hookom said:
You could:
1) add a field to your table of shows to create a column heading for your
crosstab. Each year you would change the values in this field.
or
2) use PIVOT "yr" & Val(Format(Date(),"yy") - Val(Right(tex_ShowID,2))

--
Duane Hookom
MS Access MVP
--

Amy E. Baggott said:
Actually, that column heading comes from the value of a show ID field.
Here's the SQL for the crosstab query:

TRANSFORM Avg(qrySqFtHistory.SqFt) AS AvgOfSqFt
SELECT qrySqFtHistory.exh_masid, qrySqFtHistory.[Company Name],
Avg(qrySqFtHistory.SqFt) AS [Total Of SqFt]
FROM qrySqFtHistory
GROUP BY qrySqFtHistory.exh_masid, qrySqFtHistory.[Company Name]
ORDER BY qrySqFtHistory.tex_showid DESC
PIVOT qrySqFtHistory.tex_showid;

And here's the SQL for the bonus points query:

SELECT qrySqFtHistory_Crosstab.exh_masid, qrySqFtHistory_Crosstab.IBSX01,
qrySqFtHistory_Crosstab.IBX02, qrySqFtHistory_Crosstab.IBX03,
qrySqFtHistory_Crosstab.IBX04, qrySqFtHistory_Crosstab.IBX05,
IIf(IsNull([IBX02]) Or IsNull([IBX03]) Or IsNull([IBX04]) Or
IsNull([IBX05])
Or [IBX02]>=[IBSX01],0,IIf([IBX03]>=[IBX02] And [IBX04]>=[IBX03] And
[IBX05]>=[IBX04],4,0)) AS BonusPoints
FROM qrySqFtHistory_Crosstab;

I tried using the Column Headings property to simply give the columns
different headings, but then no data at all showed up. Am I going about
the
whole thing all wrong?
--
Amy E. Baggott

"I''m going crazy and I''m taking all of you with me!" -- Linda Grayson


Duane Hookom said:
Assuming IBX02 is generated as a column heading in a crosstab,
re-evaluate
how you create the column heading expression. I think you can replace the
"02","03" etc with a number that is relative to the current year.

If you need more help, reply back the with SQL view of your crosstab.

--
Duane Hookom
MS Access MVP
--

The expression for the bonus points is

BonusPoints: IIf(IsNull([IBX02]) Or IsNull([IBX03]) Or IsNull([IBX04])
Or
IsNull([IBX05]) Or [IBX02]>=[IBSX01],0,IIf([IBX03]>=[IBX02] And
[IBX04]>=[IBX03] And [IBX05]>=[IBX04],4,0))

However, the field names for next year will change to IBX02, IBX03,
IBX04,
IBX05, and IBX06. What I'd like to do is somehow set it up so that the
query
doesn't have to be rewritten every year to change the field names
because
the
PITA factor there gets to be huge, plus I want to set this up so that
other
people can run the query besides me.
--
Amy E. Baggott

"I''m going crazy and I''m taking all of you with me!" -- Linda Grayson


:

I'm not exactly sure what you want but consider the Orders and
Customers
tables in Northwind. This SQL will sum the Freight for the most recent
5
orders for each customer:

SELECT Customers.CustomerID, Customers.CompanyName,
Sum(Orders.Freight) AS SumOfFreight
FROM Customers INNER JOIN
Orders ON Customers.CustomerID = Orders.CustomerID
WHERE OrderID In (SELECT TOP 5 OrderID
FROM ORDERS O
WHERE O.CustomerID=Orders.CustomerID
ORDER BY OrderDate Desc)
GROUP BY Customers.CustomerID, CompanyName;


--
Duane Hookom
MS Access MVP
--

message
I have a crosstab query that lists the exhibitors with their square
footage
from each show they've been in. I have another query that
calculates a
point
bonus for them based on their square footage in the last five shows.
However, this requires me to rewrite the bonus query every year, as
the
field
names will change each year. Is there any way to set this up so I
don't
have
to rewrite the fields (particularly a calculated field) every year?
I'm
afraid I may forget, and I know that if I'm not here, nobody here
would
know
how to do it.
--
Amy E. Baggott

"I''m going crazy and I''m taking all of you with me!" -- Linda
Grayson
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

You posted the crosstab query's sql view which contains the PIVOT clause.
This expression is generated from the Column Heading of the crosstab. Your
current crosstab uses:
PIVOT qrySqFtHistory.tex_showid
and my suggestion was to calculate a "relative" column heading based on the
current year and the right two characters from the tex_ShowID field. I
assumed the right two characters identify a year.
PIVOT "yr" & Val(Format(Date(),"yy") - Val(Right(tex_ShowID,2))

--
Duane Hookom
MS Access MVP


Amy E. Baggott said:
I don't understand the second option. Would this be put in the column
headings or used as a field in the crosstab or bonus query? I'm not at
all
familiar with PIVOT, so please bear with me.
--
Amy E. Baggott

"I''m going crazy and I''m taking all of you with me!" -- Linda Grayson


Duane Hookom said:
You could:
1) add a field to your table of shows to create a column heading for your
crosstab. Each year you would change the values in this field.
or
2) use PIVOT "yr" & Val(Format(Date(),"yy") - Val(Right(tex_ShowID,2))

--
Duane Hookom
MS Access MVP
--

Amy E. Baggott said:
Actually, that column heading comes from the value of a show ID field.
Here's the SQL for the crosstab query:

TRANSFORM Avg(qrySqFtHistory.SqFt) AS AvgOfSqFt
SELECT qrySqFtHistory.exh_masid, qrySqFtHistory.[Company Name],
Avg(qrySqFtHistory.SqFt) AS [Total Of SqFt]
FROM qrySqFtHistory
GROUP BY qrySqFtHistory.exh_masid, qrySqFtHistory.[Company Name]
ORDER BY qrySqFtHistory.tex_showid DESC
PIVOT qrySqFtHistory.tex_showid;

And here's the SQL for the bonus points query:

SELECT qrySqFtHistory_Crosstab.exh_masid,
qrySqFtHistory_Crosstab.IBSX01,
qrySqFtHistory_Crosstab.IBX02, qrySqFtHistory_Crosstab.IBX03,
qrySqFtHistory_Crosstab.IBX04, qrySqFtHistory_Crosstab.IBX05,
IIf(IsNull([IBX02]) Or IsNull([IBX03]) Or IsNull([IBX04]) Or
IsNull([IBX05])
Or [IBX02]>=[IBSX01],0,IIf([IBX03]>=[IBX02] And [IBX04]>=[IBX03] And
[IBX05]>=[IBX04],4,0)) AS BonusPoints
FROM qrySqFtHistory_Crosstab;

I tried using the Column Headings property to simply give the columns
different headings, but then no data at all showed up. Am I going
about
the
whole thing all wrong?
--
Amy E. Baggott

"I''m going crazy and I''m taking all of you with me!" -- Linda Grayson


:

Assuming IBX02 is generated as a column heading in a crosstab,
re-evaluate
how you create the column heading expression. I think you can replace
the
"02","03" etc with a number that is relative to the current year.

If you need more help, reply back the with SQL view of your crosstab.

--
Duane Hookom
MS Access MVP
--

message
The expression for the bonus points is

BonusPoints: IIf(IsNull([IBX02]) Or IsNull([IBX03]) Or
IsNull([IBX04])
Or
IsNull([IBX05]) Or [IBX02]>=[IBSX01],0,IIf([IBX03]>=[IBX02] And
[IBX04]>=[IBX03] And [IBX05]>=[IBX04],4,0))

However, the field names for next year will change to IBX02, IBX03,
IBX04,
IBX05, and IBX06. What I'd like to do is somehow set it up so that
the
query
doesn't have to be rewritten every year to change the field names
because
the
PITA factor there gets to be huge, plus I want to set this up so
that
other
people can run the query besides me.
--
Amy E. Baggott

"I''m going crazy and I''m taking all of you with me!" -- Linda
Grayson


:

I'm not exactly sure what you want but consider the Orders and
Customers
tables in Northwind. This SQL will sum the Freight for the most
recent
5
orders for each customer:

SELECT Customers.CustomerID, Customers.CompanyName,
Sum(Orders.Freight) AS SumOfFreight
FROM Customers INNER JOIN
Orders ON Customers.CustomerID = Orders.CustomerID
WHERE OrderID In (SELECT TOP 5 OrderID
FROM ORDERS O
WHERE O.CustomerID=Orders.CustomerID
ORDER BY OrderDate Desc)
GROUP BY Customers.CustomerID, CompanyName;


--
Duane Hookom
MS Access MVP
--

message
I have a crosstab query that lists the exhibitors with their
square
footage
from each show they've been in. I have another query that
calculates a
point
bonus for them based on their square footage in the last five
shows.
However, this requires me to rewrite the bonus query every year,
as
the
field
names will change each year. Is there any way to set this up so
I
don't
have
to rewrite the fields (particularly a calculated field) every
year?
I'm
afraid I may forget, and I know that if I'm not here, nobody here
would
know
how to do it.
--
Amy E. Baggott

"I''m going crazy and I''m taking all of you with me!" -- Linda
Grayson
Click to expand...
Click to expand...
Click to expand...
 
A

Amy E. Baggott

There seems to be a closing parenthesis missing in this expression. I tried
putting it in as

PIVOT "yr" & Val(Format(Date(),"yy")) -
Val(Right(qrySqFtHistory.tex_showid,2))

and got the following error message

You tried to execute a query that does not include the specified expression
'qrySqFtHistory.tex_showid' as part of an aggregate function.

Am I doing something wrong? I get the same error any time I try to put
parameters in a crosstab query as well.
--
Amy E. Baggott

"I''m going crazy and I''m taking all of you with me!" -- Linda Grayson


Duane Hookom said:
You posted the crosstab query's sql view which contains the PIVOT clause.
This expression is generated from the Column Heading of the crosstab. Your
current crosstab uses:
PIVOT qrySqFtHistory.tex_showid
and my suggestion was to calculate a "relative" column heading based on the
current year and the right two characters from the tex_ShowID field. I
assumed the right two characters identify a year.
PIVOT "yr" & Val(Format(Date(),"yy") - Val(Right(tex_ShowID,2))

--
Duane Hookom
MS Access MVP


Amy E. Baggott said:
I don't understand the second option. Would this be put in the column
headings or used as a field in the crosstab or bonus query? I'm not at
all
familiar with PIVOT, so please bear with me.
--
Amy E. Baggott

"I''m going crazy and I''m taking all of you with me!" -- Linda Grayson


Duane Hookom said:
You could:
1) add a field to your table of shows to create a column heading for your
crosstab. Each year you would change the values in this field.
or
2) use PIVOT "yr" & Val(Format(Date(),"yy") - Val(Right(tex_ShowID,2))

--
Duane Hookom
MS Access MVP
--

Actually, that column heading comes from the value of a show ID field.
Here's the SQL for the crosstab query:

TRANSFORM Avg(qrySqFtHistory.SqFt) AS AvgOfSqFt
SELECT qrySqFtHistory.exh_masid, qrySqFtHistory.[Company Name],
Avg(qrySqFtHistory.SqFt) AS [Total Of SqFt]
FROM qrySqFtHistory
GROUP BY qrySqFtHistory.exh_masid, qrySqFtHistory.[Company Name]
ORDER BY qrySqFtHistory.tex_showid DESC
PIVOT qrySqFtHistory.tex_showid;

And here's the SQL for the bonus points query:

SELECT qrySqFtHistory_Crosstab.exh_masid,
qrySqFtHistory_Crosstab.IBSX01,
qrySqFtHistory_Crosstab.IBX02, qrySqFtHistory_Crosstab.IBX03,
qrySqFtHistory_Crosstab.IBX04, qrySqFtHistory_Crosstab.IBX05,
IIf(IsNull([IBX02]) Or IsNull([IBX03]) Or IsNull([IBX04]) Or
IsNull([IBX05])
Or [IBX02]>=[IBSX01],0,IIf([IBX03]>=[IBX02] And [IBX04]>=[IBX03] And
[IBX05]>=[IBX04],4,0)) AS BonusPoints
FROM qrySqFtHistory_Crosstab;

I tried using the Column Headings property to simply give the columns
different headings, but then no data at all showed up. Am I going
about
the
whole thing all wrong?
--
Amy E. Baggott

"I''m going crazy and I''m taking all of you with me!" -- Linda Grayson


:

Assuming IBX02 is generated as a column heading in a crosstab,
re-evaluate
how you create the column heading expression. I think you can replace
the
"02","03" etc with a number that is relative to the current year.

If you need more help, reply back the with SQL view of your crosstab.

--
Duane Hookom
MS Access MVP
--

message
The expression for the bonus points is

BonusPoints: IIf(IsNull([IBX02]) Or IsNull([IBX03]) Or
IsNull([IBX04])
Or
IsNull([IBX05]) Or [IBX02]>=[IBSX01],0,IIf([IBX03]>=[IBX02] And
[IBX04]>=[IBX03] And [IBX05]>=[IBX04],4,0))

However, the field names for next year will change to IBX02, IBX03,
IBX04,
IBX05, and IBX06. What I'd like to do is somehow set it up so that
the
query
doesn't have to be rewritten every year to change the field names
because
the
PITA factor there gets to be huge, plus I want to set this up so
that
other
people can run the query besides me.
--
Amy E. Baggott

"I''m going crazy and I''m taking all of you with me!" -- Linda
Grayson


:

I'm not exactly sure what you want but consider the Orders and
Customers
tables in Northwind. This SQL will sum the Freight for the most
recent
5
orders for each customer:

SELECT Customers.CustomerID, Customers.CompanyName,
Sum(Orders.Freight) AS SumOfFreight
FROM Customers INNER JOIN
Orders ON Customers.CustomerID = Orders.CustomerID
WHERE OrderID In (SELECT TOP 5 OrderID
FROM ORDERS O
WHERE O.CustomerID=Orders.CustomerID
ORDER BY OrderDate Desc)
GROUP BY Customers.CustomerID, CompanyName;


--
Duane Hookom
MS Access MVP
--

message
I have a crosstab query that lists the exhibitors with their
square
footage
from each show they've been in. I have another query that
calculates a
point
bonus for them based on their square footage in the last five
shows.
However, this requires me to rewrite the bonus query every year,
as
the
field
names will change each year. Is there any way to set this up so
I
don't
have
to rewrite the fields (particularly a calculated field) every
year?
I'm
afraid I may forget, and I know that if I'm not here, nobody here
would
know
how to do it.
--
Amy E. Baggott

"I''m going crazy and I''m taking all of you with me!" -- Linda
Grayson
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

Get rid of the Order by clause:

TRANSFORM Avg(qrySqFtHistory.SqFt) AS AvgOfSqFt
SELECT qrySqFtHistory.exh_masid, qrySqFtHistory.[Company Name],
Avg(qrySqFtHistory.SqFt) AS [Total Of SqFt]
FROM qrySqFtHistory
GROUP BY qrySqFtHistory.exh_masid, qrySqFtHistory.[Company Name]
PIVOT "yr" & Val(Format(Date(),"yy")) - Val(Right(tex_ShowID,2))


--
Duane Hookom
MS Access MVP
--

Amy E. Baggott said:
There seems to be a closing parenthesis missing in this expression. I
tried
putting it in as

PIVOT "yr" & Val(Format(Date(),"yy")) -
Val(Right(qrySqFtHistory.tex_showid,2))

and got the following error message

You tried to execute a query that does not include the specified
expression
'qrySqFtHistory.tex_showid' as part of an aggregate function.

Am I doing something wrong? I get the same error any time I try to put
parameters in a crosstab query as well.
--
Amy E. Baggott

"I''m going crazy and I''m taking all of you with me!" -- Linda Grayson


Duane Hookom said:
You posted the crosstab query's sql view which contains the PIVOT clause.
This expression is generated from the Column Heading of the crosstab.
Your
current crosstab uses:
PIVOT qrySqFtHistory.tex_showid
and my suggestion was to calculate a "relative" column heading based on
the
current year and the right two characters from the tex_ShowID field. I
assumed the right two characters identify a year.
PIVOT "yr" & Val(Format(Date(),"yy") - Val(Right(tex_ShowID,2))

--
Duane Hookom
MS Access MVP


Amy E. Baggott said:
I don't understand the second option. Would this be put in the column
headings or used as a field in the crosstab or bonus query? I'm not at
all
familiar with PIVOT, so please bear with me.
--
Amy E. Baggott

"I''m going crazy and I''m taking all of you with me!" -- Linda Grayson


:

You could:
1) add a field to your table of shows to create a column heading for
your
crosstab. Each year you would change the values in this field.
or
2) use PIVOT "yr" & Val(Format(Date(),"yy") - Val(Right(tex_ShowID,2))

--
Duane Hookom
MS Access MVP
--

message
Actually, that column heading comes from the value of a show ID
field.
Here's the SQL for the crosstab query:

TRANSFORM Avg(qrySqFtHistory.SqFt) AS AvgOfSqFt
SELECT qrySqFtHistory.exh_masid, qrySqFtHistory.[Company Name],
Avg(qrySqFtHistory.SqFt) AS [Total Of SqFt]
FROM qrySqFtHistory
GROUP BY qrySqFtHistory.exh_masid, qrySqFtHistory.[Company Name]
ORDER BY qrySqFtHistory.tex_showid DESC
PIVOT qrySqFtHistory.tex_showid;

And here's the SQL for the bonus points query:

SELECT qrySqFtHistory_Crosstab.exh_masid,
qrySqFtHistory_Crosstab.IBSX01,
qrySqFtHistory_Crosstab.IBX02, qrySqFtHistory_Crosstab.IBX03,
qrySqFtHistory_Crosstab.IBX04, qrySqFtHistory_Crosstab.IBX05,
IIf(IsNull([IBX02]) Or IsNull([IBX03]) Or IsNull([IBX04]) Or
IsNull([IBX05])
Or [IBX02]>=[IBSX01],0,IIf([IBX03]>=[IBX02] And [IBX04]>=[IBX03] And
[IBX05]>=[IBX04],4,0)) AS BonusPoints
FROM qrySqFtHistory_Crosstab;

I tried using the Column Headings property to simply give the
columns
different headings, but then no data at all showed up. Am I going
about
the
whole thing all wrong?
--
Amy E. Baggott

"I''m going crazy and I''m taking all of you with me!" -- Linda
Grayson


:

Assuming IBX02 is generated as a column heading in a crosstab,
re-evaluate
how you create the column heading expression. I think you can
replace
the
"02","03" etc with a number that is relative to the current year.

If you need more help, reply back the with SQL view of your
crosstab.

--
Duane Hookom
MS Access MVP
--

message
The expression for the bonus points is

BonusPoints: IIf(IsNull([IBX02]) Or IsNull([IBX03]) Or
IsNull([IBX04])
Or
IsNull([IBX05]) Or [IBX02]>=[IBSX01],0,IIf([IBX03]>=[IBX02] And
[IBX04]>=[IBX03] And [IBX05]>=[IBX04],4,0))

However, the field names for next year will change to IBX02,
IBX03,
IBX04,
IBX05, and IBX06. What I'd like to do is somehow set it up so
that
the
query
doesn't have to be rewritten every year to change the field names
because
the
PITA factor there gets to be huge, plus I want to set this up so
that
other
people can run the query besides me.
--
Amy E. Baggott

"I''m going crazy and I''m taking all of you with me!" -- Linda
Grayson


:

I'm not exactly sure what you want but consider the Orders and
Customers
tables in Northwind. This SQL will sum the Freight for the most
recent
5
orders for each customer:

SELECT Customers.CustomerID, Customers.CompanyName,
Sum(Orders.Freight) AS SumOfFreight
FROM Customers INNER JOIN
Orders ON Customers.CustomerID = Orders.CustomerID
WHERE OrderID In (SELECT TOP 5 OrderID
FROM ORDERS O
WHERE O.CustomerID=Orders.CustomerID
ORDER BY OrderDate Desc)
GROUP BY Customers.CustomerID, CompanyName;


--
Duane Hookom
MS Access MVP
--

in
message
I have a crosstab query that lists the exhibitors with their
square
footage
from each show they've been in. I have another query that
calculates a
point
bonus for them based on their square footage in the last five
shows.
However, this requires me to rewrite the bonus query every
year,
as
the
field
names will change each year. Is there any way to set this up
so
I
don't
have
to rewrite the fields (particularly a calculated field) every
year?
I'm
afraid I may forget, and I know that if I'm not here, nobody
here
would
know
how to do it.
--
Amy E. Baggott

"I''m going crazy and I''m taking all of you with me!" --
Linda
Grayson
Click to expand...
Click to expand...
Click to expand...
 
Top