How to transpose row headings as column headings, cross tab query perhaps? (1 Viewer)

[bignose2]

Hi,

I have a table with each day & totals related to that day. Dates will vary and number, perhaps 20 or more but I think will restrict to 30 although data may ask otherwise but will have to worry about that on the screen space.

I know not really how access is meant to process, display data but I have a situation that would be so much better if I could have the dates across the top.

Datex	DSpaces	DSpacesO
18-Aug-23	3	0
19-Aug-23	2	-3
20-Aug-23	1	-4
21-Aug-23	8	-1
22-Aug-23	8	6

I would really like to view it like this on a form.

18-Aug-23	19-Aug-23	20-Aug-23	21-Aug-23	22-Aug-23
3	2	1	8	8
0	-3	-4	-1	6

Would be OK to have two separate subforms but DateX & DSpaces on one & DateX (again) & BSpaces if a limitation of the crosstab (which I read)

I messed around with the crosstab & ended up with lots of data & rows I could not quite work out.

Toying with the ideal of making a table with x (dates) number of AutoColumns using VBA but think messy.

Any ideas,
thanks I/A

 

[ebs17]

I have a table

The initial structure is bad and must be brought into a normalized structure using a UNION query, for example.

TRANSFORM
   MIN(T.MyValue) AS xy
SELECT
   T.Attribute
FROM
   (
      SELECT
         Datex,
         DSpaces AS MyValue,
         "DSpaces" AS Attribute
      FROM
         TableX
      UNION ALL SELECT
         Datex,
         DSpaces0,
         "DSpaces0"
      FROM
         TableX
   ) AS T
GROUP BY
   T.Attribute
PIVOT
   T.Datex

 

[arnelgp]

same but using 3 query.
see query1 and query2.
your final query is a union query, query3.

 

[ebs17]

UNION with SELECT * is extremely optimistic and error-prone.

 

[bignose2]

Hi,

Gone with ebs17 as the first I tried & just worked perfectly & seems an elegant solution
but thanks arnelgp, will have a good look at that solution also.

Thanks again,

 

[arnelgp]

UNION with SELECT * is extremely optimistic and error-prone.

not unless you have same number of columns and exactly the same column names.

 

[ebs17]

same column names

Dynamically generated in a crosstab query?
A few data gaps create chaos.

To be clear, your example works with the data shown. I don't know whether the data shown is representative of reality, I don't think so.

 

[bignose2]

Hi,
Just now looking to implement proper, being fussy now ...

Is there a way to put this in a form, so that the fields can be adjusted, colour, size, centralized etc.

I have added as a subform & the crosstab.query as the source so it works but thinking would like to manipulate the display.

 

[ebs17]

Access Pivot Tables and Pivot Charts | DEVelopers HUT

Exploring how we can use the WebBrowser control to bring back Pivot Tables and Pivot Charts to Access 2013 and later after Microsoft deprecated these features.

www.devhut.net

 

[June7]

Aside from Daniel's amazing tool which may or may not have the formatting capability you want, to adjust color, size, etc. would require bound controls because these are control properties. Since field names in your CROSSTAB are dynamic, cannot bind controls to fields. Review this tutorial to explore how to build report with dynamic CROSSTAB, perhaps could adapt for form https://www.fmsinc.com/MicrosoftAccess/query/crosstab-report/index.html