UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Crosstab Column Headers Linked To Form Controls, Access 2016    
 
   
DeMiNiX
post May 15 2018, 11:38 PM
Post#1



Posts: 4
Joined: 28-March 18



Hey everyone!

I've been scratching my head hard over this one. The database that I'm currently working on is essentially a checklist that different users have to report for every project. I have a main form (with a report subform) that will filter the subform and show the applicable status that each user has reported for each feature.

I created a crosstab table that does exactly what I want: it displays the status that each user has reported for a feature. HOWEVER, since some projects will have different users, I run into a pickle. I need the crosstab columns (userID) to change per the userID control on the main form.

Attached File  Crosstab1.jpg ( 243.3K )Number of downloads: 13

Attached File  Crosstab2.jpg ( 176.68K )Number of downloads: 10

My SQL is as follows:

SQL
TRANSFORM First(tblData.Status) AS FirstOfStatus
SELECT tblData.projectID, tblData.subprojectID, tblData.featureID, tblData.subfeatureID, tblData.Feature, tblData.Subfeature
FROM tblData WHERE (((tblData.userID)=[Forms]![frmProgressMain]![txtUser1] Or (tblData.userID)=[Forms]![frmProgressMain]![txtUser2] Or (tblData.userID)=[Forms]![frmProgressMain]![txtUser3]))
GROUP BY tblData.projectID, tblData.subprojectID, tblData.featureID, tblData.subfeatureID, tblData.Feature, tblData.Subfeature
PIVOT tblData.userID In ("1","2","3","4");


Anyone have any suggestions on how I can refer the columns to the userID controls (txtUser1, txtUser2, txtUser3)?
Attached File  Crosstab3.jpg ( 129.92K )Number of downloads: 6



Go to the top of the page
 
Doug Steele
post May 16 2018, 09:23 AM
Post#2


UtterAccess VIP
Posts: 21,962
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Something doesn't look right here. The screenshot of the query builder you posted has two tables in it, yet the SQL you posted only has one.

What happens if you change the reference to column userID in table tblUsers to User, as indicated below?

Attached File  Crosstab2.jpg ( 137.41K )Number of downloads: 5

Go to the top of the page
 
RJD
post May 16 2018, 09:37 AM
Post#3


UtterAccess VIP
Posts: 8,955
Joined: 25-October 10
From: Gulf South USA


Hi: Yes, the results you seem to want can be accomplished (dynamic matrix with appropriate headers), but not the way you are going about it (at least not in any easy way that I can see)..

First, I would create the crosstab a different way, using a query as the source (instead of the table), with the query using the table with criteria to limit the appropriate users. And I would get the users by limiting the project on a request form.

Second, I would generalize the crosstab to sequence number the user codes (1, 2, 3, etc. no matter the actual ID codes) gained from another query using the new crosstab record source query that is limited. I would link in (JOIN) the sequence query to the crosstab to generalize the PIVOT to 1, 2, 3, etc. no matter the actual IDs.

Third, I would show the results in a report only - not in a form. That just seems too cumbersome. You can always preview the report, and then print it if you want to. The headers can be gotten by referring to the sequence query to relate the user name to the newly created sequence.

And I would make everything bound - it seems your form and embedded report are unbound now.

So, I would get the results you want with an entirely different approach than you are using now. And the report matrix would be dynamic just by choosing the project you want and calling the report. The number of columns and the column headers will adjust with this approach.

Think about this approach, and post your db for us to look at (no sensitive data, zipped, 2MB max size) so we can help you with this if you are interested.

HTH
Joe
Go to the top of the page
 
DeMiNiX
post May 16 2018, 06:26 PM
Post#4



Posts: 4
Joined: 28-March 18



Huh! Interesting approach! I'll try that method, and report back!
Go to the top of the page
 
RJD
post May 16 2018, 06:59 PM
Post#5


UtterAccess VIP
Posts: 8,955
Joined: 25-October 10
From: Gulf South USA


Do give it try and let us know if we can help. It can seem a bit tricky, but ince done flows smoothly.

HTH
Joe
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    22nd October 2018 - 06:59 AM