Thanks Duane. I thought the same thing. Unfortunately, putting a button on
my form that accesses the crosstab query results in a run-time error 2186
everytime I open up the form. When I replace the button reference from the
crosstab query to the underlying query, I don't get the run-time error.
That's why I'm looking to avoid the crosstab query. Here's the sql for each:
CrosstabNumberOfBuilds
TRANSFORM Sum(qryNumberOfBuilds.SumOfproducts_quantity) AS
SumOfSumOfproducts_quantity
SELECT "Builds" AS Total
FROM qryNumberOfBuilds
GROUP BY "Builds"
ORDER BY qryNumberOfBuilds.Week
PIVOT qryNumberOfBuilds.Week;
Underlying Query: qryNumberOfBuilds
SELECT Sum(orders_products.products_quantity) AS SumOfproducts_quantity,
Format(Now()+(DateDiff("ww",Now(),[invoice_date])*7),"yyyy mm-dd") AS Week
FROM orders_status INNER JOIN (((orders INNER JOIN
orders_products_attributes ON
orders.orders_id=orders_products_attributes.orders_id) INNER JOIN
orders_total ON orders.orders_id=orders_total.orders_id) INNER JOIN
orders_products ON
orders_products_attributes.orders_products_id=orders_products.orders_products_id) ON orders_status.orders_status_id=orders.orders_status
WHERE (((orders.invoice_date) Between FromDate() And ToDate()) AND
((orders_products_attributes.products_options_values) Like "*Build*") AND
((orders_products_attributes.products_options) Like "*Build*") AND
((orders_total.class)="ot_total") AND
((orders_status.orders_status_name)="Shipped"))
GROUP BY Format(Now()+(DateDiff("ww",Now(),[invoice_date])*7),"yyyy mm-dd");
Any additional suggestions or things to investigate would be helpful.
Duane Hookom said:
A crosstab should work. I expect you need to add a Row Heading of a constant
like:
Field: Total:"Builds"
Crosstab: Row Heading
--
Duane Hookom
Microsoft Access MVP
:
Is there a way to transpose a query so that it runs horizontally instead of
vertically without utilizing a crosstab query? I get a 2186 error everytime
I put it into a Crosstab query.
My query now reads like this:
Wk # of builds
01/01/07 100
01/08/07 123
01/15/07 267
etc.
I'd like it to read as such:
Wk1 Wk2 Wk3
Builds 100 123 267
Alberto
