How to make crosstab query update in report

[BenSaeed]

Hello,

I made a report with following crosstab query.

TRANSFORM Workersdetail.workername AS CountOfedate
SELECT Workersdetail.[attendance], Count(Workersdetail.[edate]) AS [Total Of edate]
FROM Workersdetail
GROUP BY Workersdetail.[Workername], Workersdetail.[attendance], Workersdetail.[workerhourenter]
PIVOT site+Cstr([workerhourenter])

I wanted to know that, is there any option through which my report gets autoupdate or refresh incase of addition in SITE field (as metioned with PIVOT)?

Best regards,

Ben

 

[dhookom]

There is a solution at Rogers Access Library that creates a dynamic alias for each column.

Your pivot has two fields. Wouldn't you need to dynamically handle each one?

You could add a text field to your site table and name it [SiteColumn]. Add in the values of "01","02", "03", etc. Then pivot on this SiteColumn field rather than the Site field. Then set the Column Headings property of the crosstab to "01","02", "03", etc. adding more values than you currently have you your table.

Duane
Hook'D on Access
MS Access MVP

 

[BenSaeed]

Thank you for the reply,

Let assume my pivot stand on one field only, like this -> PIVOT site

For your perfect understanding my crosstab query result mentioned hereunder.

Columns RJ...........PAL.........Route..........(These are the name of site)
Rows Anthony Ben Christian (These are the workers name coming from workerdetail.workername)

I wanted to make dynamic Pivot, Problem is whenever any addition in the site field for e.g. NR, its getting update in the crosstab query but not in the report. I appreciate your idea but in my case there is 100% chances to addition in SITE field. To the short I want my report update in line with Crosstab query.

Be inform your attached MDB is not working, there is the error of Previous version as I am using Access 2013.

I will be thankful if you could give me more advise in this case.

Thanks

Ben

 

[dhookom]

Can we assume your paper/report has a limited width and will fit a maximum number of columns? Let's assume a maximum of 12 columns.
Can we also assume you have (or could create) a table of sites and add the column as I suggested?
Add values of "01","02",... etc to your site table to use as column headings in your crosstab. I you currently have 8 sites this will be "01" to "08".

Set the Column Headings property of your crosstab query to "01","02",...,"12". Columns 09 to 12 will render blank.

Create your report based on columns from the crosstab of "01","02",..."12". When you add a new site give it the number "09" and it will appear in your report. Use a subreport or just combo boxes as your column headings/labels in the report.

Combo Boxes
Hard-code the control sources to "01","02",...,"12" (are you noticing the pattern?).

The Row Source of the combo boxes would be something like:

SELECT SiteColumn, SiteName FROM tblSites

Bound Column: 1
Column Count: 2
Column Widths: 0,1

If you need more sites/columns you must tell use how these would be displayed since you have run out of room on your paper.

Duane
Hook'D on Access
MS Access MVP

 

[BenSaeed]

Please forgive me if I could not get you clearly, as I am new in using Access.

First my look at my query

TRANSFORM Workersdetail.workername AS CountOfedate
SELECT Workersdetail.[workerhourenter], Count(Workersdetail.[edate]) AS [Total Of edate]
FROM Workersdetail
GROUP BY Workersdetail.[Workername], Workersdetail.[attendance], Workersdetail.[workerhourenter]
PIVOT Workersdetail.Sitecol In ("1","2","3","4","5","6");
.

I made it till the report. Onward as my good understanding I have to replace Labels with combobox with each SITECOL value i.e. 1 then 2. if this is true in according to you,

Problem is, values in the combo box coming from field workername if I only put 1 in control source. 2nd thing, may I ask you about the rowsource you mentioned why I should retrieve values from directly table not query?

Looking forward to your kind advise.

Thanks alot
Ben

 

[dhookom]

Don't you have a table of sites? If not, why would you not create one so you would enter the SiteCol values only once for each site? This table would also be used as the Row Source of your combo boxes that serve as column heading labels in the report.

Is your SiteCol field numeric or text as I suggested?



Duane
Hook'D on Access
MS Access MVP

 

[BenSaeed]

Hi,

Thanks alot for your help, I have done it according to your point and got success.

Just last question. Is there any posibility that I could add columns dynamically.

Rightnow my report shows columns from 1 - 12, however I have data till 1 - 6 and 7-12 cols are unneccessary. What I want is, my report should be show more dynamic 7-12 columns shall be shown when addition of new SITEcol, obviously I have no rows under 7-12.

Thanks alot and please accept my kind regards,

BEN

 

[dhookom]

I don't understand your question. As you add more sites they will display. You can test by changing a sitecol value to 9 and see what happens.

Duane
Hook'D on Access
MS Access MVP