Union Crosstab querie

[Gismo]

and here is your database (Combine query), using Allen Browne's

Thank you so much

 

[arnelgp]

when you view the query, the columns are in "alternating" (qty then vol).
you can drag to arrange the columns and "group" them (group by qty or volume).

 

[Gismo]

when you view the query, the columns are in "alternating" (qty then vol).
you can drag to arrange the columns and "group" them (group by qty or volume).

Trying to get this query into a form now

also trying to have place holders for additional locations

 

[arnelgp]

you need to set the ControlSource of each "#Name?" textbox to the column in your Query.

 

[Gismo]

you need to set the ControlSource of each "#Name?" textbox to the column in your Query.

I tried that but there is no addition control sources to select in the Form

These are the only controls to select from when I select the query as record source

 

[June7]

Can't set ControlSource to fields that don't exist.

If you want "placeholders" columns then need to specify names in the CROSSTAB PIVOT clause with IN().

PIVOT [Stock Location] & " (" & [Column] & ")" IN("Main Office (Qty)", "The Hub (Qty)", "Airport (Qty)", "Agent - Laura (Qty)", "Agent - Johnny (Qty)", "Main Office (Volume)", "The Hub (Volume)", "Airport (Volume)", "Agent - Laura (Volume)", "Agent - Johnny (Volume)")

Again, look at Allen Browne's article for more info.

 

[Gismo]

I tried that but there is no addition control sources to select in the Form

View attachment 106367
View attachment 106368

These are the only controls to select from when I select the query as record source

Can't set ControlSource to fields that don't exist.

If you want "placeholders" columns then need to specify names in the CROSSTAB PIVOT clause with IN(). Again, look at Allen Browne's article for more info.

The queries makes provision for any additional locations from the table should they be added
The form only shows main office as available controls even though there are 2 other locations in the table and the query

 

[June7]

Look at my previous post again. I had edited it.

CROSSTAB definitely has its limitations. Building a stable form or report based on CROSSTAB is not easy.

 

[Gismo]

Got it thank you

 

[arnelgp]

I recreated your query and form.
LocationProductsQry is a CrossJoin on Product and Location

the query will include all Locations and All Products (Manufactured).

 

[arnelgp]

Got it thank you

look at Agent Laura and Johny, they both have same qty/volume and the Total is not Tally?

 

[spaLOGICng]

I tried the below code but got a lot of errors

Please take Plog's advice above.

First perform the Union in SELECT Statements, then create the Crosstab.

Union Queries require that all columns are property ordered and of the same datatype. If the number of columns and types from first crosstab do not the number and type of columns from the second crosstab, it will throw errors out the kazoo.