Crosstab query in form (1 Viewer)

azhar2006

Registered User.
Local time
Today, 09:51
Joined
Feb 8, 2012
Messages
202
Hello all .
I am trying to display a crosstab query on a form. This query displays a set of items. What I want to show me the results of the query on an unstructured form.
Code:
TRANSFORM Sum(QueryRank.CountOfRtba) AS SumOfCountOfRtba
SELECT QueryRank.CountOfRtba
FROM QueryRank
GROUP BY QueryRank.CountOfRtba
PIVOT QueryRank.ProductColour;
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 09:51
Joined
Oct 29, 2018
Messages
21,358
Could you just display the query in the form as a subform? Would that be okay?
 

azhar2006

Registered User.
Local time
Today, 09:51
Joined
Feb 8, 2012
Messages
202
Could you just display the query in the form as a subform? Would that be okay?
thank you my friend . I do not want to display queries in a datasheet way, I am trying to display them in text boxes arranged in a certain way
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:51
Joined
Oct 29, 2018
Messages
21,358
thank you my friend . I do not want to display queries in a datasheet way, I am trying to display them in text boxes arranged in a certain way
In that case, you would need a "lot" of code to populate those textboxes, because you will probably end up with an unbound form.
 

azhar2006

Registered User.
Local time
Today, 09:51
Joined
Feb 8, 2012
Messages
202
In that case, you would need a "lot" of code to populate those textboxes, because you will probably end up with an unbound form.
And what about the DLookup function, I think it calls the sparse values in the query. Does this solve it?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 16:51
Joined
Feb 19, 2013
Messages
16,553
you can base a form on a subquery without any vba but you need to specify the column headings which you do in the query

e.g.


PIVOT QueryRank.ProductColour IN ( "Red","Blue","Green")

And what about the DLookup function
you would need to know the colours anyway
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:51
Joined
Oct 29, 2018
Messages
21,358
And what about the DLookup function, I think it calls the sparse values in the query. Does this solve it?
Maybe, but it will be slower than using a recordset object.
 

azhar2006

Registered User.
Local time
Today, 09:51
Joined
Feb 8, 2012
Messages
202
No problem to be slow. I don't want the user to run the query in datasheet view. The values will be confused and may cause incorrect information to be transmitted. There is also another solution, but I did not find it, is to put a button for it on the form that prints a summary of the fields of the crosstab query .. And I mean here a paper report
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:51
Joined
Feb 19, 2002
Messages
42,971
If you know what the column names are so that dLookup() would work, then you know what the column names are so you can make a bound form.

If you can fix the number of columns, then you can standardize the column names and make a bound form that way.

I've posted a sample of doing this with a report. The same technique would work for a form.
 

Attachments

  • BoundDenormalizedForm_20210319.zip
    1.5 MB · Views: 454

azhar2006

Registered User.
Local time
Today, 09:51
Joined
Feb 8, 2012
Messages
202
Thank you dear friend . I'll see, of course, that I know the column names. I will open it in a version higher than 2007, maybe you are using a version higher than 2007
 

azhar2006

Registered User.
Local time
Today, 09:51
Joined
Feb 8, 2012
Messages
202
Is there another way without using dLookup() ?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:51
Joined
Feb 19, 2002
Messages
42,971
Did you look at my suggestion? It doesn't require hard-coded dLookup()s.
 

azhar2006

Registered User.
Local time
Today, 09:51
Joined
Feb 8, 2012
Messages
202
Frankly, I didn't understand it. I'm trying to find someone to help me with the code VBA 😞
 

CJ_London

Super Moderator
Staff member
Local time
Today, 16:51
Joined
Feb 19, 2013
Messages
16,553
perhaps you should show some example data and what your form looks like. So far nothing you have said would appear to require the use of VBA
 

June7

AWF VIP
Local time
Today, 08:51
Joined
Mar 9, 2014
Messages
5,423
A query or table as SourceObject of subform container would not use textboxes
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:51
Joined
Feb 19, 2002
Messages
42,971
Frankly, I didn't understand it. I'm trying to find someone to help me with the code VBA
Look at the report. The same technique can be used on a form.
If you want an updateable form, look at the form examples. They look like crosstabs but they are made by using a bunch of queries joined together.
Look at the documentation.
 

oleronesoftwares

Passionate Learner
Local time
Today, 09:51
Joined
Sep 22, 2014
Messages
1,159

Users who are viewing this thread

Top Bottom