Crosstab query help (1 Viewer)

[Indigo]

I have been trying to figure this one out all afternoon and I'm getting nowhere fast. I am running Access 2010 and I am trying to create a crosstab query that will populate a form. I have a table of employee names with their corresponding group name. I also have a table that lists all the job processes that the TMs could perform in their groups. Finally I have a table that stores Observation Audits that are performed on the employees by their supervisor on the job processes. I want to create a dynamic crosstab query that shows the following:

 Employe Name          Process1    Process2    Process3  ......
 J. Doe                           100%
 B. Smith                                         90%
 D. Chan                         100%                       75%

I need to have dynamic column headings that change based on which group is audited as each group has different jobs. As well I need to show all employees in each group not just the ones who have been audited.

Now I have created a query which gives me all processes but only the employees who have been audited, and the column headings are not dynamic:

PARAMETERS [Forms]![frmSecurity]![GroupName] Text ( 255 );
TRANSFORM Avg(qryAllSortScore.AvgScore) AS AvgOfAvgScore
SELECT qryAllSortScore.EmpName
FROM qryAllSortScore
GROUP BY qryAllSortScore.EMpName
PIVOT qryAllSortScore.qryProcessCC.Process;

based on qryAllSortScore:

SELECT qryScoreSort.GroupName, qryProcessCC.Process, qryScoreSort.AvgScore
FROM qryScoreSort RIGHT JOIN qryProcessCC ON qryScoreSort.Process = qryProcessCC.Process
ORDER BY qryScoreSort.AvgScore;

But I can't figure out how to get dynamic headings -- all the references I have found show using dates as dynamic headings, but that's not what I need. Also, how can I show all employees and not just those who have been audited.... or is this too complex? Any help /direction would be appreciated. Thank you.

 

[plog]

a crosstab query that will populate a form

Before we try and get your crosstab working, I have to tell you that the end goal is a very bad idea and will cause even more headaches.

Forms that are bound to tables/queries need to know what the field names are that will be feeding their inputs. When you have dynamically named fields you can't easily build a form off of it. How do you suppose to overcome that?

As for getting the correct field to use for your columns in the cross-tab, you put that field in the PIVOT clause:

PIVOT qryAllSortScore.qryProcessCC.Process;

Looks like you did it correctly. Perhaps you can demonstrate with data what you want to occur. Looks like you posted your expected results. Now could you show us what data is in qryAllSortScore that will generate that?

 

[Indigo]

Before we try and get your crosstab working, I have to tell you that the 
 end goal is a very bad idea and will cause even more headaches.

If you have a better suggestion, then I am all ears (eyes)!

I have the users inputting their audit observations on a form and wanted to have a subform with their group results on the same main form so that they can see their overall group results for each process as they are entering in new audits. I thought that a crosstab query would yield the results that I wanted to show.

As for:

Now could you show us what data is in qryAllSortScore that will generate that?

Sorry, I missed a field in when I posted the SQL earlier:

SELECT qryScoreSort.GroupName, qryScoreSort.EmpName, qryProcessCC.Process, qryScoreSort.AvgScore
FROM qryScoreSort RIGHT JOIN qryProcessCC ON qryScoreSort.Process = qryProcessCC.Process
ORDER BY qryScoreSort.AvgScore;

Does that make more sense?

 

[plog]

If you have a better suggestion, then I am all ears (eyes)!

1. I don't think you need to display it to users as they are entering data.
2. I would use a more traditional layout--not a cross tab. Just use the data that feeds the cross-tab.

Does that make more sense?

No. Give me data. Provide 2 sets:

A. Starting sample data from your datasource. Include datasource name (I'm guessing qryScorSort and qryProcessCC ) and field names. Also, include enough sample data to cover all cases.

B. Expected results of A. Show me what you ultimately hope to end up with when you feed your cross-tab the data in A.

 

[Galaxiom]

Forms that are bound to tables/queries need to know what the field names are that will be feeding their inputs. When you have dynamically named fields you can't easily build a form off of it. How do you suppose to overcome that?

It is trivial using VBA in the Form's Load Event. Loop through the Form's Recordset.Fields Collection to retrieve each field's Name property and apply the names to the ControlSource property of the items in the Controls Collection.

For example (air code)

 Dim n as Integer
 Dim fld As Variant
  
  
 For Each fld in Me.Recordset.Fields
     Do
         With Me.Controls(n)
              If .ControlType = actextbox
                .ControlSource = Me.fld.Name
                .Controls(0).Caption = Me.fld.Name
                 n = n + 1
                 Exit Do
              End If
           End With
           n = n + 1
      Loop
 Next

.Controls(0) refers to the textbox's label.

 

[CJ_London]

to retrieve each field's Name property

or the field caption property if using it

Also if using a crosstab in a subform and are happy with a datasheet view then set the subform sourceobject to

Query.NameofCrosstabquery

then rather than requerying use

me.subformname.sourceobject="Query.NameofCrosstabquery"

 

[plog]

It is trivial using VBA in the Form's Load Event

I will take that bet.

 

[Indigo]

Sorry - didn't see that this question took on a life of it's own. I did get my problem sorted and working, btw.

I have over 250 job/processes managed by a dozen working groups and some had 12 processes and some had as much as 30. So I added another field to my job/process table and numbered each process by working group. These became my column names in my crosstab query and then I used a DLookup function on my form to give me the proper job names.