Solved Report from dynamic crosstab query.. Help on Column Heading (1 Viewer)

[Manos39]

hello
i would like some help,
have this crosstab query QrPlanCross, with

TRANSFORM Max(QrPlan.EidYpirSynt) AS MaxOfEidYpirSynt
SELECT QrPlan.Epitheto, QrPlan.[A/A], Max(QrPlan.eidosypiresiasID) AS [Total Of eidosypiresiasID]
FROM QrPlan
GROUP BY QrPlan.Epitheto, QrPlan.[A/A]
ORDER BY QrPlan.[A/A]
PIVOT QrPlan.Title;

my Column Heading [Title] is a concarnation of three fields from source select report QrPlan

PARAMETERS [Forms]![ypovolesfrm]![cboVardia] Long;
SELECT Format([dutyrecordstbl].[Ypiresiadate],"dd") & " " & Left(Format([dutyrecordstbl].[Ypiresiadate],"dddd"),1) & " " & [shift] AS Title, dutyrecordstbl.vardiaID, [QrYpiresiesVardiaA)A].[A/A], dutyrecordstbl.YpiresiaDate, dutyrecordstbl.shift, [QrYpiresiesVardiaA)A].ypallilosID, [QrYpiresiesVardiaA)A].Epitheto, [QrYpiresiesVardiaA)A].eidosypiresiasID, [QrYpiresiesVardiaA)A].EidYpirSynt, [QrYpiresiesVardiaA)A].orarioID, [QrYpiresiesVardiaA)A].Orario
FROM dutyrecordstbl LEFT JOIN [QrYpiresiesVardiaA)A] ON (dutyrecordstbl.vardiaID = [QrYpiresiesVardiaA)A].vardiaID) AND (dutyrecordstbl.YpiresiaDate = [QrYpiresiesVardiaA)A].Ypiresiadate)
GROUP BY Format([dutyrecordstbl].[Ypiresiadate],"dd") & " " & Left(Format([dutyrecordstbl].[Ypiresiadate],"dddd"),1) & " " & [shift], dutyrecordstbl.vardiaID, [QrYpiresiesVardiaA)A].[A/A], dutyrecordstbl.YpiresiaDate, dutyrecordstbl.shift, [QrYpiresiesVardiaA)A].ypallilosID, [QrYpiresiesVardiaA)A].Epitheto, [QrYpiresiesVardiaA)A].eidosypiresiasID, [QrYpiresiesVardiaA)A].EidYpirSynt, [QrYpiresiesVardiaA)A].orarioID, [QrYpiresiesVardiaA)A].Orario
HAVING (((dutyrecordstbl.vardiaID)=[Forms]![ypovolesfrm]![cboVardia]))
ORDER BY Format([dutyrecordstbl].[Ypiresiadate],"dd") & " " & Left(Format([dutyrecordstbl].[Ypiresiadate],"dddd"),1) & " " & [shift], dutyrecordstbl.vardiaID, [QrYpiresiesVardiaA)A].[A/A];

in order to be helped (i did that) "for only one column Heading to rule", and this is dynamic..

i would like a solution to make my report fit (31) dates and also to make it handle headings
i tryied with pivot Title in
("1"; "2"; etc) the field dissapears ...

 

[ebs17]

In the PIVOT part of the query and thus as a column header, you can only use a field from the basic query, i.e. one that already exists.

So if you want to use numbers (without an enclosing "), they have to be generated beforehand: Day(AnyDate), a sequential numbering or something similar.
The content that the query returns is used dynamically. So if you have gaps, you can find them in the column headings. Column headings are sorted, so it makes a difference whether you have numbers or text.
Fixed column headings alone define the column headings, including their order.

 

[Pat Hartman]

Are you sure you can fit 31 columns on the report? If you can't, you might be happier with the result if you exported it to Excel and formatted it there.

Otherwise, Here's a link to a sample I call "bound denormalized form". It shows how to make a normalized table look like a spreadsheet for data entry. But, there is also a sample report that takes variable input and creates the headers for it in the report.

Bound Denormalized Form

I've left the old versions here but ALWAYS go to the last post to get the current version. This database contains two examples (using the same technique) of how to take data that is properly normalized and display it so that it looks like a spreadsheet (rows displayed as columns) but is...

www.access-programmers.co.uk

I also included a pdf with some documentation on the way the "spreadsheet" like forms work.

 

[Manos39]

Thank you i shall try that

 

[arnelgp]

you upload a sample db and i will make a dynamic Report for you.
i also made similar Dynamic crosstab report here:
SubForm using Crosstab Query (Dynamic Column Problem) | Access World Forums (access-programmers.co.uk)

 

[Manos39]

Hi arnel,
this is my sample db with picture of desired report to be made
for info, when form ypovolesfrm opens, we select year, month and a team, (BARDIA) then we click on plan and a crosstab opens.
It's column heading for info, is a concarnation of month date, date name initial and shift for that team on that month
Database has data for this year, until current month
Thank you

 

[arnelgp]

sorry, can't get the Form to Open without error.
it's because of non-english characters.

 

[Manos39]

I undreastand one sould change system locale to greek for that. What would you need to post istead as source for that report

 

[arnelgp]

see if this is the report you want (report1).
open your form and press the "plan" button.

 

[Manos39]

see if this is the report you want (report1).
open your form and press the "plan" button.

Arnel it is very good, i adopted it in mine, thank you for your work,
one small favour and i will explain

For passed months it is as should be,
but if during a month like now 11th of 2022, column headings should be there even if no info down below against employee excist

(like in crossatab query?) Like that it would be very useful

It is a work plan so one working as secretary places employee according, to its teams and it's shift amongst other needs, like the capture of the crosstab with the concarnated column headings.... Otherwise he / she will be looking for that in papers again..
I attach yours working ( i dont have words! thank you !!!)

 

[arnelgp]

the question is why does the "schedule" not complete for the whole month?

 

[arnelgp]

here is the db i am working with.
i added some more codes.

 

[Manos39]

here is the db i am working with.
i added some more codes.

Thank you i shall try later. To explain, this on paper schedule is the one from which we place one to work according to his team s shift. So because there are five teams it is convinient to have the shifts written to see how to place someone on duty and decide how to place someone on duty in the future. I hope i explained

 

[Manos39]

Arnel almost perfect but a a little more help please!
I would like Date Name left(format([date];"ddd"="S");1) at list included in that column head so having that, to show Sundays as Red i am trying in code to inlude it no luck here.. So S or M etc and Shift at least please

 

[Manos39]

This is after experiment, on your code working paint Sundays red, in past month as desired,
but with current month (which is not finished yet) column headings arent the same
i would like coumn headings produced same way as month 10 Arnel

 

[arnelgp]

here i put it back again.

 

[Manos39]

I almost would let you in peace Arnel, all perfect but another problem sould be fixed,

i noticed that report wrogly shows something.

Someone who is placed on duty (eg like in screenshot) on Friday, 10th of November, while QrPlancross shows correct, 10nth working in that box,

the Report places him on the next day 4th November (after has data or something)

Arnel in case on wrongly one selects a month with no data yet, could it be fixed not to show Runtime Error '94' Invalid use of Null ?

 

[June7]

In the PIVOT part of the query and thus as a column header, you can only use a field from the basic query, i.e. one that already exists.

No, PIVOT clause can have calculation. Review https://stackoverflow.com/questions/64400911/pivot-query-in-ms-access

 

[ebs17]

I agree and am happy to be corrected.

 

[arnelgp]

you also copy modAutoFitTextbox (if you are using it)., i made changes to the module.