Transposing Query without Utilizing a Crosstab

  • Thread starter Thread starter Alberto
  • Start date Start date
A

Alberto

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
 
A crosstab should work. I expect you need to add a Row Heading of a constant
like:
Field: Total:"Builds"
Crosstab: Row Heading
 
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.
 
It looks like you have dynamic criteria in your base query.
FromDate() And ToDate()
You must set the data types of all dynamic criteria in the Query->Parameters.

--
Duane Hookom
Microsoft Access MVP


Alberto said:
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
Click to expand...
Click to expand...
 
Bingo. That did the trick. Thank you.

Duane Hookom said:
It looks like you have dynamic criteria in your base query.
FromDate() And ToDate()
You must set the data types of all dynamic criteria in the Query->Parameters.

--
Duane Hookom
Microsoft Access MVP


Alberto said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top