Crosstab query in form

[azhar2006]

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.

TRANSFORM Sum(QueryRank.CountOfRtba) AS SumOfCountOfRtba
SELECT QueryRank.CountOfRtba
FROM QueryRank
GROUP BY QueryRank.CountOfRtba
PIVOT QueryRank.ProductColour;

 

[theDBguy]

Could you just display the query in the form as a subform? Would that be okay?

 

[azhar2006]

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]

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]

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]

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]

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]

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]

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.

 

[azhar2006]

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]

Is there another way without using dLookup() ?

 

[Pat Hartman]

Did you look at my suggestion? It doesn't require hard-coded dLookup()s.

 

[azhar2006]

Frankly, I didn't understand it. I'm trying to find someone to help me with the code VBA

 

[CJ_London]

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

 

[azhar2006]

Could you just display the query in the form as a subform? Would that be okay?

Thanks theDBguy . Will it appear as text boxes or as a table?

 

[June7]

A query or table as SourceObject of subform container would not use textboxes

 

[Pat Hartman]

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]

Make summary data easier to read by using a crosstab query - Microsoft Support

Crosstab queries are useful for summarizing data, calculating statistics and then grouping the results.

support.microsoft.com

Crosstab Queries in Microsoft Access

Create a Crosstab Query in Microsoft Access to Summarize Data from Two Fields

www.599cd.com

You can go through the links to have basic understnading of cross tab queries