Xtab criteria problem?

P

Pete

Hi,
I have a problem with a crosstab query that sums orders and available
inventory for plants at a nursery according to pot size (rows), and colors
(columns). This query feeds the pages of a tab control in a form that has
customer info at the main form level with order and order detail subforms.
The main form has combo boxes for 'Customer Category' and 'Season'. The tab
control has pages to display orders for each 'Customer Category' and one for
'Inventory Available', etc. - each fed by a version of the following with
minor variations to suit the needs of the tab selected.

TRANSFORM Sum([Order Details].Quantity) AS SumOfQuantity
SELECT PotSizes.Size
FROM PotSizes INNER JOIN (((CustomerCategories INNER JOIN Customers ON
CustomerCategories.CategoryID = Customers.Category) INNER JOIN Orders ON
Customers.CustomerID = Orders.CustomerID) INNER JOIN ((Defaults INNER JOIN
Crops ON Defaults.Crop = Crops.CropID) INNER JOIN ([Order Details] INNER
JOIN Colors ON [Order Details].ColorID = Colors.ColorID) ON Crops.CropID =
[Order Details].CropID) ON Orders.OrderID = [Order Details].OrderID) ON
PotSizes.PotSizeID = [Order Details].PotSizeID
WHERE ((([Order Details].CropID)=[Defaults]![Crop]) AND
((Customers.Category)=[Forms]![Customer Orders]![txtCategory]) AND
((Year([Order Date]))=[Forms]![Customer Orders]![cboSeason]))
GROUP BY PotSizes.Size
PIVOT Colors.Color In ("Red","White","Pink","Marble","Burgundy","Plum
Pudding","Jingle Bells","WR red","WR white","WR pink","WR
marble","Monet","Peppermint");

This has worked very well for a couple of years, and with the 'Season'
selection box on the main form, we can just keep throwing everything into
the same tables from year to year without need to separate one year's orders
from another, as the only thing that shows up is the orders for the season
selected. As time goes by, though, some of the colors in the inventory have
changed and been discontinued, and I'd like to alter this crosstab query so
the colors in the tab control will be generated rather than coded in the
'IN' term at the end of the query - something like this

TRANSFORM Sum([Order Details].Quantity) AS SumOfQuantity
SELECT PotSizes.Size
FROM PotSizes INNER JOIN (((CustomerCategories INNER JOIN Customers ON
CustomerCategories.CategoryID = Customers.Category) INNER JOIN Orders ON
Customers.CustomerID = Orders.CustomerID) INNER JOIN ((Defaults INNER JOIN
Crops ON Defaults.Crop = Crops.CropID) INNER JOIN ([Order Details] INNER
JOIN Colors ON [Order Details].ColorID = Colors.ColorID) ON Crops.CropID =
[Order Details].CropID) ON Orders.OrderID = [Order Details].OrderID) ON
PotSizes.PotSizeID = [Order Details].PotSizeID
WHERE ((([Order Details].CropID)=[Defaults]![Crop]) AND
((Customers.Category)=[Forms]![Customer Orders]![txtCategory]) AND
((Year([Order Date]))=[Forms]![Customer Orders]![cboSeason]))
GROUP BY PotSizes.Size
PIVOT Colors.Color;

For some reason that I've been unable to discover this results in the
following error:
'The Microsoft Jet database engine does not recognize [Forms]![Customer
Orders]![cboSeason] as a valid field name or expression.'

If I delete the season criteria as follows, it works fine.

TRANSFORM Sum([Order Details].Quantity) AS SumOfQuantity
SELECT PotSizes.Size
FROM PotSizes INNER JOIN (((CustomerCategories INNER JOIN Customers ON
CustomerCategories.CategoryID = Customers.Category) INNER JOIN Orders ON
Customers.CustomerID = Orders.CustomerID) INNER JOIN ((Defaults INNER JOIN
Crops ON Defaults.Crop = Crops.CropID) INNER JOIN ([Order Details] INNER
JOIN Colors ON [Order Details].ColorID = Colors.ColorID) ON Crops.CropID =
[Order Details].CropID) ON Orders.OrderID = [Order Details].OrderID) ON
PotSizes.PotSizeID = [Order Details].PotSizeID
WHERE ((([Order Details].CropID)=[Defaults]![Crop]) AND
((CustomerCategories.CategoryName)="Special Order"))
GROUP BY PotSizes.Size
PIVOT Colors.Color;

Any ideas why Access can recognize the reference to [Forms]![Customer
Orders]![cboSeason] when the "IN" clause is included and not without it?
 
D

Duane Hookom

You must declare the datatypes of your parameters in crosstabs if you don't
provide the Column Headings. Select Query->Parameters and enter
[Forms]![Customer Orders]![txtCategory] Text
[Forms]![Customer Orders]![cboSeason] Long
or whatever your data types are.

--
Duane Hookom
MS Access MVP

Pete said:
Hi,
I have a problem with a crosstab query that sums orders and available
inventory for plants at a nursery according to pot size (rows), and colors
(columns). This query feeds the pages of a tab control in a form that has
customer info at the main form level with order and order detail subforms.
The main form has combo boxes for 'Customer Category' and 'Season'. The
tab
control has pages to display orders for each 'Customer Category' and one
for
'Inventory Available', etc. - each fed by a version of the following with
minor variations to suit the needs of the tab selected.

TRANSFORM Sum([Order Details].Quantity) AS SumOfQuantity
SELECT PotSizes.Size
FROM PotSizes INNER JOIN (((CustomerCategories INNER JOIN Customers ON
CustomerCategories.CategoryID = Customers.Category) INNER JOIN Orders ON
Customers.CustomerID = Orders.CustomerID) INNER JOIN ((Defaults INNER JOIN
Crops ON Defaults.Crop = Crops.CropID) INNER JOIN ([Order Details] INNER
JOIN Colors ON [Order Details].ColorID = Colors.ColorID) ON Crops.CropID =
[Order Details].CropID) ON Orders.OrderID = [Order Details].OrderID) ON
PotSizes.PotSizeID = [Order Details].PotSizeID
WHERE ((([Order Details].CropID)=[Defaults]![Crop]) AND
((Customers.Category)=[Forms]![Customer Orders]![txtCategory]) AND
((Year([Order Date]))=[Forms]![Customer Orders]![cboSeason]))
GROUP BY PotSizes.Size
PIVOT Colors.Color In ("Red","White","Pink","Marble","Burgundy","Plum
Pudding","Jingle Bells","WR red","WR white","WR pink","WR
marble","Monet","Peppermint");

This has worked very well for a couple of years, and with the 'Season'
selection box on the main form, we can just keep throwing everything into
the same tables from year to year without need to separate one year's
orders
from another, as the only thing that shows up is the orders for the season
selected. As time goes by, though, some of the colors in the inventory
have
changed and been discontinued, and I'd like to alter this crosstab query
so
the colors in the tab control will be generated rather than coded in the
'IN' term at the end of the query - something like this

TRANSFORM Sum([Order Details].Quantity) AS SumOfQuantity
SELECT PotSizes.Size
FROM PotSizes INNER JOIN (((CustomerCategories INNER JOIN Customers ON
CustomerCategories.CategoryID = Customers.Category) INNER JOIN Orders ON
Customers.CustomerID = Orders.CustomerID) INNER JOIN ((Defaults INNER JOIN
Crops ON Defaults.Crop = Crops.CropID) INNER JOIN ([Order Details] INNER
JOIN Colors ON [Order Details].ColorID = Colors.ColorID) ON Crops.CropID =
[Order Details].CropID) ON Orders.OrderID = [Order Details].OrderID) ON
PotSizes.PotSizeID = [Order Details].PotSizeID
WHERE ((([Order Details].CropID)=[Defaults]![Crop]) AND
((Customers.Category)=[Forms]![Customer Orders]![txtCategory]) AND
((Year([Order Date]))=[Forms]![Customer Orders]![cboSeason]))
GROUP BY PotSizes.Size
PIVOT Colors.Color;

For some reason that I've been unable to discover this results in the
following error:
'The Microsoft Jet database engine does not recognize [Forms]![Customer
Orders]![cboSeason] as a valid field name or expression.'

If I delete the season criteria as follows, it works fine.

TRANSFORM Sum([Order Details].Quantity) AS SumOfQuantity
SELECT PotSizes.Size
FROM PotSizes INNER JOIN (((CustomerCategories INNER JOIN Customers ON
CustomerCategories.CategoryID = Customers.Category) INNER JOIN Orders ON
Customers.CustomerID = Orders.CustomerID) INNER JOIN ((Defaults INNER JOIN
Crops ON Defaults.Crop = Crops.CropID) INNER JOIN ([Order Details] INNER
JOIN Colors ON [Order Details].ColorID = Colors.ColorID) ON Crops.CropID =
[Order Details].CropID) ON Orders.OrderID = [Order Details].OrderID) ON
PotSizes.PotSizeID = [Order Details].PotSizeID
WHERE ((([Order Details].CropID)=[Defaults]![Crop]) AND
((CustomerCategories.CategoryName)="Special Order"))
GROUP BY PotSizes.Size
PIVOT Colors.Color;

Any ideas why Access can recognize the reference to [Forms]![Customer
Orders]![cboSeason] when the "IN" clause is included and not without it?
Click to expand...
 
P

Pete

Thanks, Duane.
That makes the query work correctly. Unfortunately, Access now complains
that I can't use a non-fixed-column crosstab query in a subform. I guess
I'll have to come up with another plan.

Duane Hookom said:
You must declare the datatypes of your parameters in crosstabs if you don't
provide the Column Headings. Select Query->Parameters and enter
[Forms]![Customer Orders]![txtCategory] Text
[Forms]![Customer Orders]![cboSeason] Long
or whatever your data types are.

--
Duane Hookom
MS Access MVP

Pete said:
Hi,
I have a problem with a crosstab query that sums orders and available
inventory for plants at a nursery according to pot size (rows), and colors
(columns). This query feeds the pages of a tab control in a form that has
customer info at the main form level with order and order detail subforms.
The main form has combo boxes for 'Customer Category' and 'Season'. The
tab
control has pages to display orders for each 'Customer Category' and one
for
'Inventory Available', etc. - each fed by a version of the following with
minor variations to suit the needs of the tab selected.

TRANSFORM Sum([Order Details].Quantity) AS SumOfQuantity
SELECT PotSizes.Size
FROM PotSizes INNER JOIN (((CustomerCategories INNER JOIN Customers ON
CustomerCategories.CategoryID = Customers.Category) INNER JOIN Orders ON
Customers.CustomerID = Orders.CustomerID) INNER JOIN ((Defaults INNER JOIN
Crops ON Defaults.Crop = Crops.CropID) INNER JOIN ([Order Details] INNER
JOIN Colors ON [Order Details].ColorID = Colors.ColorID) ON Crops.CropID =
[Order Details].CropID) ON Orders.OrderID = [Order Details].OrderID) ON
PotSizes.PotSizeID = [Order Details].PotSizeID
WHERE ((([Order Details].CropID)=[Defaults]![Crop]) AND
((Customers.Category)=[Forms]![Customer Orders]![txtCategory]) AND
((Year([Order Date]))=[Forms]![Customer Orders]![cboSeason]))
GROUP BY PotSizes.Size
PIVOT Colors.Color In ("Red","White","Pink","Marble","Burgundy","Plum
Pudding","Jingle Bells","WR red","WR white","WR pink","WR
marble","Monet","Peppermint");

This has worked very well for a couple of years, and with the 'Season'
selection box on the main form, we can just keep throwing everything into
the same tables from year to year without need to separate one year's
orders
from another, as the only thing that shows up is the orders for the season
selected. As time goes by, though, some of the colors in the inventory
have
changed and been discontinued, and I'd like to alter this crosstab query
so
the colors in the tab control will be generated rather than coded in the
'IN' term at the end of the query - something like this

TRANSFORM Sum([Order Details].Quantity) AS SumOfQuantity
SELECT PotSizes.Size
FROM PotSizes INNER JOIN (((CustomerCategories INNER JOIN Customers ON
CustomerCategories.CategoryID = Customers.Category) INNER JOIN Orders ON
Customers.CustomerID = Orders.CustomerID) INNER JOIN ((Defaults INNER JOIN
Crops ON Defaults.Crop = Crops.CropID) INNER JOIN ([Order Details] INNER
JOIN Colors ON [Order Details].ColorID = Colors.ColorID) ON Crops.CropID =
[Order Details].CropID) ON Orders.OrderID = [Order Details].OrderID) ON
PotSizes.PotSizeID = [Order Details].PotSizeID
WHERE ((([Order Details].CropID)=[Defaults]![Crop]) AND
((Customers.Category)=[Forms]![Customer Orders]![txtCategory]) AND
((Year([Order Date]))=[Forms]![Customer Orders]![cboSeason]))
GROUP BY PotSizes.Size
PIVOT Colors.Color;

For some reason that I've been unable to discover this results in the
following error:
'The Microsoft Jet database engine does not recognize [Forms]![Customer
Orders]![cboSeason] as a valid field name or expression.'

If I delete the season criteria as follows, it works fine.

TRANSFORM Sum([Order Details].Quantity) AS SumOfQuantity
SELECT PotSizes.Size
FROM PotSizes INNER JOIN (((CustomerCategories INNER JOIN Customers ON
CustomerCategories.CategoryID = Customers.Category) INNER JOIN Orders ON
Customers.CustomerID = Orders.CustomerID) INNER JOIN ((Defaults INNER JOIN
Crops ON Defaults.Crop = Crops.CropID) INNER JOIN ([Order Details] INNER
JOIN Colors ON [Order Details].ColorID = Colors.ColorID) ON Crops.CropID =
[Order Details].CropID) ON Orders.OrderID = [Order Details].OrderID) ON
PotSizes.PotSizeID = [Order Details].PotSizeID
WHERE ((([Order Details].CropID)=[Defaults]![Crop]) AND
((CustomerCategories.CategoryName)="Special Order"))
GROUP BY PotSizes.Size
PIVOT Colors.Color;

Any ideas why Access can recognize the reference to [Forms]![Customer
Orders]![cboSeason] when the "IN" clause is included and not without it?
Click to expand...
Click to expand...
 
D

Duane Hookom

You can set the Source Object of a subform to a crosstab query as in the
sample dynamic subform at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

--
Duane Hookom
MS Access MVP

Pete said:
Thanks, Duane.
That makes the query work correctly. Unfortunately, Access now complains
that I can't use a non-fixed-column crosstab query in a subform. I guess
I'll have to come up with another plan.

Duane Hookom said:
You must declare the datatypes of your parameters in crosstabs if you don't
provide the Column Headings. Select Query->Parameters and enter
[Forms]![Customer Orders]![txtCategory] Text
[Forms]![Customer Orders]![cboSeason] Long
or whatever your data types are.

--
Duane Hookom
MS Access MVP

Pete said:
Hi,
I have a problem with a crosstab query that sums orders and available
inventory for plants at a nursery according to pot size (rows), and colors
(columns). This query feeds the pages of a tab control in a form that has
customer info at the main form level with order and order detail subforms.
The main form has combo boxes for 'Customer Category' and 'Season'.
The
tab
control has pages to display orders for each 'Customer Category' and
one
for
'Inventory Available', etc. - each fed by a version of the following with
minor variations to suit the needs of the tab selected.

TRANSFORM Sum([Order Details].Quantity) AS SumOfQuantity
SELECT PotSizes.Size
FROM PotSizes INNER JOIN (((CustomerCategories INNER JOIN Customers ON
CustomerCategories.CategoryID = Customers.Category) INNER JOIN Orders
ON
Customers.CustomerID = Orders.CustomerID) INNER JOIN ((Defaults INNER JOIN
Crops ON Defaults.Crop = Crops.CropID) INNER JOIN ([Order Details]
INNER
JOIN Colors ON [Order Details].ColorID = Colors.ColorID) ON
Crops.CropID =
[Order Details].CropID) ON Orders.OrderID = [Order Details].OrderID) ON
PotSizes.PotSizeID = [Order Details].PotSizeID
WHERE ((([Order Details].CropID)=[Defaults]![Crop]) AND
((Customers.Category)=[Forms]![Customer Orders]![txtCategory]) AND
((Year([Order Date]))=[Forms]![Customer Orders]![cboSeason]))
GROUP BY PotSizes.Size
PIVOT Colors.Color In ("Red","White","Pink","Marble","Burgundy","Plum
Pudding","Jingle Bells","WR red","WR white","WR pink","WR
marble","Monet","Peppermint");

This has worked very well for a couple of years, and with the 'Season'
selection box on the main form, we can just keep throwing everything into
the same tables from year to year without need to separate one year's
orders
from another, as the only thing that shows up is the orders for the season
selected. As time goes by, though, some of the colors in the inventory
have
changed and been discontinued, and I'd like to alter this crosstab
query
so
the colors in the tab control will be generated rather than coded in
the
'IN' term at the end of the query - something like this

TRANSFORM Sum([Order Details].Quantity) AS SumOfQuantity
SELECT PotSizes.Size
FROM PotSizes INNER JOIN (((CustomerCategories INNER JOIN Customers ON
CustomerCategories.CategoryID = Customers.Category) INNER JOIN Orders
ON
Customers.CustomerID = Orders.CustomerID) INNER JOIN ((Defaults INNER JOIN
Crops ON Defaults.Crop = Crops.CropID) INNER JOIN ([Order Details]
INNER
JOIN Colors ON [Order Details].ColorID = Colors.ColorID) ON
Crops.CropID =
[Order Details].CropID) ON Orders.OrderID = [Order Details].OrderID) ON
PotSizes.PotSizeID = [Order Details].PotSizeID
WHERE ((([Order Details].CropID)=[Defaults]![Crop]) AND
((Customers.Category)=[Forms]![Customer Orders]![txtCategory]) AND
((Year([Order Date]))=[Forms]![Customer Orders]![cboSeason]))
GROUP BY PotSizes.Size
PIVOT Colors.Color;

For some reason that I've been unable to discover this results in the
following error:
'The Microsoft Jet database engine does not recognize [Forms]![Customer
Orders]![cboSeason] as a valid field name or expression.'

If I delete the season criteria as follows, it works fine.

TRANSFORM Sum([Order Details].Quantity) AS SumOfQuantity
SELECT PotSizes.Size
FROM PotSizes INNER JOIN (((CustomerCategories INNER JOIN Customers ON
CustomerCategories.CategoryID = Customers.Category) INNER JOIN Orders
ON
Customers.CustomerID = Orders.CustomerID) INNER JOIN ((Defaults INNER JOIN
Crops ON Defaults.Crop = Crops.CropID) INNER JOIN ([Order Details]
INNER
JOIN Colors ON [Order Details].ColorID = Colors.ColorID) ON
Crops.CropID =
[Order Details].CropID) ON Orders.OrderID = [Order Details].OrderID) ON
PotSizes.PotSizeID = [Order Details].PotSizeID
WHERE ((([Order Details].CropID)=[Defaults]![Crop]) AND
((CustomerCategories.CategoryName)="Special Order"))
GROUP BY PotSizes.Size
PIVOT Colors.Color;

Any ideas why Access can recognize the reference to [Forms]![Customer
Orders]![cboSeason] when the "IN" clause is included and not without it?
Click to expand...
Click to expand...
Click to expand...
 
P

Pete

Thank you again. Looks like that should do it once I figure out how what to
attach it to.

Do you know why this works while you can't just put it in at form design
time?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Negative values in combined crosstabs 2
Need help with query strategy 5
NOT criteria in query not working 5
Select Query "Where" Problem 2
Performance on Northwinds Database for Nested One2Many 1
Query design 1
Trying to summarise orders and payments 1
Problem adding new table to form 2

Top