Multiple queries on one report problem in Access 97 1

[storl2]

I didn't see another thread mentioning this topic, but please point me to it if it exists. I am working in Access 97.

I am trying to combine multiple queries on one form, but the results that it gives me are not even close to what I need. I have a few tables that list employee information, classes given, and a master list of the available classes. I currently have a report that lists all classes that a person has taken, sorted by their direct manager. I am trying to add an additional section to the report listing the classes in the master list that the person has not taken, based on their position. Both queries work fine one their own, but when I create a report that combines the two queries, the query that Access creates to combine them does not give the correct results. I need a way to put two queries on one report.

The results would look something like:

************************************************
Manager

Employee

Class 1 Taken Date Length
Class 2 Taken Date Length
Class 3 Taken Date Length
.
.
.


Class 1 Needed Length
Class 2 Needed Length

etc.


SQL of the two queries:

SELECT DISTINCT tblClasses.fldClassName, Auditor.[Menu Name], tblClasses.fldClassLength
FROM tblClasses, Auditor INNER JOIN (tbTrainingData INNER JOIN tbAssociateTraining ON tbTrainingData.[Class Number] = tbAssociateTraining.[Class Number]) ON Auditor.LAN_ID = tbAssociateTraining.LAN_ID
GROUP BY tblClasses.fldClassName, Auditor.[Menu Name], tblClasses.fldClassLength, Auditor.Manager, tbTrainingData.[Class Name], Auditor.Status, tblClasses.fldClassType, Auditor.Group
HAVING (((tblClasses.fldClassName)<>[tbTrainingData]![Class Name]) AND ((Auditor.Manager)=[Forms]![frmTrainingReports]![CDM Selection]) AND ((Auditor.Status)="A") AND ((tblClasses.fldClassType)=[Auditor]![Group]));

SELECT Auditor.[Menu Name], tbTrainingData.[Class Name], tbTrainingData.[Number of Hours], tbTrainingData.[Class Date], Auditor.Manager
FROM Auditor INNER JOIN (tbTrainingData INNER JOIN tbAssociateTraining ON tbTrainingData.[Class Number] = tbAssociateTraining.[Class Number]) ON Auditor.LAN_ID = tbAssociateTraining.LAN_ID
GROUP BY Auditor.[Menu Name], tbTrainingData.[Class Name], tbTrainingData.[Number of Hours], tbTrainingData.[Class Date], Auditor.Manager, Auditor.Status
HAVING (((Auditor.Manager)=[Forms]![frmTrainingReports]![CDM Selection]) AND ((Auditor.Status)="A"))
ORDER BY tbTrainingData.[Class Date];

 

[scriverb]

Try using a subreport control in the Group Footer that would list the classes not taken. This would require using the second query as the Recordsource for this subreport. You would not have to change the Detail section at all but just create the subreport with the second query and then add a Group footer level by worker and insert a subreport.

Let me know if that works for you.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[storl2]

Well, that's definitely much closer to what I need. Now the only problem that I have is that it is listing the classes that every direct report needs in the subreport instead of just listing classes needed for just that single direct report. Is there a way to make a subreport refer to the grouping that the parent report uses? Like make it see the manager and employee for the classes taken portion and use that in the subreport? I have the subreport grouped by the employee name like in the parent report.

 

[scriverb]

Yes. I am not sure which queries are for the parent report and which one is for the subreport. But, in theory we want the query for the subreport link to the parent report query by a left join and select records that have a null value in the parent side of the query.

The Link Child and Master fields should keep the records straight so that we are talking about the same worker but the LEFT JOIN with the check for nulls in the parent side of the join will display only those records that have not printed in the detail section.

Post back if this is still confusing to you. If so, please identify the SQL in the parent report and the one you want to use in the subreport.



[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[storl2]

The first query is for the subreport, the second is for the parent report. I think I see what you're saying, but the taken training classes list includes some classes that are not in the master list since there are occasionally one-time classes. I don't know if that would cause any problems, but I thought it would help for you to know.

I have been using whatever SQL Informix uses for so long that Access is a little bit mystical to me since I only started dealing with it a couple of weeks ago. Thanks for all the help.

 

[storl2]

Ok, I used my feeble brain to think on that last post by scriverb for a while and finally figured out what to do. All I had to do was throw the reference for the employee's name on the parent report into the criteria for the employee's name on the subreport's query. It works fine now. Thanks!

 

[scriverb]

I have been away from this for a while on an errand and I was just thinking about posting just what you have done. Because the query for the subreport is for all classes not taken you must par down the list to just those not taken by the staffer in the main report. By adding the employee name into the criteria you done just that. The only problem with employee name is that duplicate names for different people will certainly cause a problem. Try to use a better identification field than name.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]