form based on a crosstab query

[danwand]

Hi,

I want a form or report based on the results of a crosstab query which shows summary information as to which topics have been completed by all employees on system, in the following format:

ReferProc Topic john Smith Paul Edwards <--(headings, names added for all employees on system)
A this 11/10/01
B that 12/10/01 8/8/01
etc

The crosstab query is based on the following query that takes data from 3 tables:

qryIndForCrossTab:

SELECT tblSIndTrain.StaffID, tblStaffDetails.FName, tblStaffDetails.LName, ([FName] & " " & [Lname]) AS Name, tblIndTopics.Topic, tblIndTopics.ReferProc, tblSIndTrain.TopicDate
FROM tblStaffDetails INNER JOIN (tblIndTopics INNER JOIN tblSIndTrain ON tblIndTopics.INDID = tblSIndTrain.INDID) ON tblStaffDetails.StaffID = tblSIndTrain.StaffID;

the crosstab query it self is as follows:

qryIndCrossTab:

TRANSFORM First(qryIndForCrossTab.TopicDate) AS FirstOfTopicDate
SELECT qryIndForCrossTab.ReferProc, qryIndForCrossTab.Topic
FROM qryIndForCrossTab
GROUP BY qryIndForCrossTab.ReferProc, qryIndForCrossTab.Topic
PIVOT qryIndForCrossTab.Name;

The problem is that I can easily create a form based on the crosstab but when a new staff member is added to the system although the actual queries (both the crosstab and the query the crosstab is based on) will add the new staff members name and respective dates for each topic done the form originally created will not include a column heading field for the new employee.

So..is there a way to dynamically create a new field on the form in order to keep it upto date with new employees.

Post maybe a little confusing but please ask for clarification.

Any help would be appreciated.

Dan

 

[MichaelRed]

A few issues occur. First, the direct answer is that you need to "CreateControl" (perhaps more than one). This is a common topic and may be found in the ubiquitous {F1}[color] (aka H E L P) and in multiple instnaces herein (Tek-Tips). Another topis which may shed some additional light is "CreateForm", particularly her in Tek-Tips, as it offers somewhat more complete/complex examples of the former.

Then, perhaps you need to consider the structure of the report. Working with report layouts a nit should rapidly convince you that getting more than a dozen (or so) "Columns" across a report becomes an exercise in experation. Even approaching this will lead to 'adjustments' in font size, orientation and a more-or-less rapid degradation in hte appearance / readability of hte report. So, unless the report is VERY specifically limited to an organization which is already quite small and (somehow) constrained in its growth to remain smaller than that which is easily displayed in the format desired - go back to the drawing board and (re)consider the arrangement.

Next, I assume you are also asking (or would need to ask shortly), re gathering the actual "column name" information and palcing the necessary controls on the report. Again, using Mr. Ubiquitous &/or Search herein with the topic / keyword "CreateForm" (or "CreateReport") would show a common technique for extracting the results/value set to use as the column headings to be used in a simple columnar report based on a query.





MichaelRed