Cross tab querry on sub form

[LOUIX]

I actually have a crosstab querry which is filtered from a form. The form will display the kilometres range like 90K and the crosstab will querry it as 15, 30, 45 .... to 90
if the range is 105 it will goes from column heading 15, 30, 45, 60... to 105. but i cannot display it on a subform, it does not function.
I tries to make a table from a querry, but the textbox in the subform remain fix and does not change.
I need help please, attach a picture

[June7]

What does "does not function" mean - error message, wrong result, nothing happens?

Exactly what do you need to fix? If Mileage box is not bound and you want it to change, use code to do so.

Why limit the mileage range? Why not just always show fields through 105 (or whatever top limit could be possible)?

Can use IN() to specify field headers and force query to always have fields through 105.

PIVOT kilometres IN (15,30,45,60,75,90,105);

[LOUIX]

hi,
When i drag the cross querry on a form, nothing appears, the subform is blank, despite the crosstab function.
Why i dont show it to the maximum, because its a vehicle service schedule plan, the customer may purchase a service plan for only 100,000 km or more or less.
I would like to display only the package he has choosen, that is 15, 30, 45,.... up to the package he chose
I dont know how to do it via code, i have google, but could not see or i did not understand.
Would you be able to help me

[June7]

Two ways to establish a query as subform.

1. main form in Layout view, drag query onto form - Access create a subform container control with query as SourceObject

2. main form in Design view, drag subform container control onto form, set its SourceObject property to query

Use PARAMETERS clause along with WHERE clause to filter crosstab query. Review http://allenbrowne.com/ser-67.html#Param

If you still need help, provide your db for analysis. Follow instructions at bottom of my post.

[CJ_London]

try setting the subform source object to

Query.nameofquery

Oops already suggssted

[LOUIX]

Thanks guys for your prompt answers, really appreciate,
Attached the db where i removed all other forms so that it reduced in size, you will see the textbox mileage in grey where you can insert either 60000, 75000, 80000, 90000, 150,000 or more can be inserted and the subform will be filtered. then when you click on the queries " qrySP_Crosstab" it will show horizontally as per mileage inserted.
Supposed you insert 60000, you wil see only 15,30,45, 60 . if it was 90,000, you will see it till 90.
Same should be inserted as a subform on the mainform and also it should be inserted on a report as well to give to the customer.
Is it possible.
If you need more info, please tell, thanks guysezyzip.zip

[June7]

Okay, the CROSSTAB query is not displaying data. Since CROSSTAB is based on another query that has filter, put the PARAMETERS clause in that query. Don't repeat the filter in the CROSSTAB.

However, Access is not accepting this CROSSTAB as subform/subreport container SourceObject. I cannot replicate this issue with my db. Perhaps it is due to your CROSSTAB based on 2 other queries.

Only option may be to write CROSSTAB data to a table and use that as subform/subreport.

[LOUIX]

June7, thanks for your quick reply,
In fact, when you insert 60000, or 75000, or 90000 in the textbox Mileage (In grey), whatever number found in the subform, the subform will be filtered, and the queries "qrySP_Crosstab will display the data as per the mileage you inserted.
Will you be able to do it for me, really, i am not able to understand the principle of Crosstab, im loss.
Thanks if you can assist

Ludovic

[June7]

I did insert 60000. Your CROSSTAB would not display data for me until I removed the PARAMETERS clause and put it into qrySP.

[LOUIX]

oops, may be i missed it. is it possible to insert it on a form or report which will depends on the mileage inserted on the form

[Welshgasman]

Crossposted @ 1700 today https://www.access-programmers.co.uk...b-form.319028/

[June7]

Well, I downloaded db again and this time query works right off. I must have been entering wrong value before.

However, still will not accept this query as SourceObject for subform/subreport.

[LOUIX]

therefore, there is no other way to use the crosstab in a subform

[June7]

As noted in post #7, I have no problem doing it in my database. However, I tested with static criteria. My guess is the dynamic criteria is cause.

[June7]

Unfortunately, column headers cannot be dynamic in order to be used as SourceObject. This defeats your goal of not always displaying all mileage groups.

You should get an error message when attempting to use crosstab without fixed columns as SourceObject.

Only practical solution I can see is VBA using QueryDefs to modify query object.

Set up query object with IN clause:
PARAMETERS [forms]![frmInterval]![Text25] Long, [forms]![frmInterval]![Text11] Text ( 255 );
TRANSFORM Sum(qrySP.Net) AS SumOfNet
SELECT qrySP.strPN
FROM qrySP
GROUP BY qrySP.strPN
PIVOT qrySP.strKM In (10,20,30);

Code to modify, like:

Private Sub Command36_Click()
Dim qdf As QueryDef, strSQL As String, strColumns As String, x As Integer
Set qdf = CurrentDb.QueryDefs("qrySP_Crosstab")
strSQL = qdf.SQL
strSQL = Left(strSQL, InStr(strSQL, "PIVOT ") - 1)
x = Me.Text23
Do
    strColumns = strColumns & x & ","
    x = x + Me.Text23
Loop Until x > Me.Text25 / 1000
qdf.SQL = strSQL & vbCrLf & "PIVOT qrySP.strKM IN(" & Left(strColumns, Len(strColumns) - 1) & ");"
DoCmd.OpenForm "frmCross"
End Sub