Subreport Not Showing All Fields All The Time 1

[mcelligott]

Ok, I am admit it. I am totally stumpted.

I have a report which lists all complaints received for a user defined period of time. For each complaint there can be an infinite number of employees involved. I therefore have a subreport that lists all the employees involved for each complaint (and it does this part correctly). The report and subreport have are linked by the complaint #.

In the subreport, for each employee involved I need to also display the final result of their part of the complaint. I need to list if their part is founded or unfounded, what type of error that employee made in the complaint and what discipline each employee may have received as a result of the error. While I do also have other fields that are displayed, they are not as critical as the ones listed above.

The problem: The subreport does not always display what is in those fields for each employee invovled. It only displays the information for a small sub-set of them. I have been unable to determine a rhime or reason behind it. I have the following SQL behind it:

SELECT [Complaints - Employees Involved].[Complaint #], [Last Name] & ', ' & [First Name] AS Employee, [Complaints - Employees Involved].[Results Of Investigation], [Complaints - Employees Involved].[Sitting on], [Complaints - Employees Involved].[Over 8 Hours], [Complaints - Employees Involved].[Day of Wheel], [Complaints - Employees Involved].[Other Comments], [Employee History Table].[Error Types], [Employee History Table].[Entry Type], [Employees table].[Adj Hire Date], [Employees table].Title
FROM ([Complaints - Employees Involved] LEFT JOIN [Employee History Table] ON ([Complaints - Employees Involved].[Complaint #] = [Employee History Table].[Complaint #]) AND ([Complaints - Employees Involved].IBM = [Employee History Table].IBM)) INNER JOIN [Employees table] ON [Complaints - Employees Involved].IBM = [Employees table].IBM;

I would appreciate any suggestions anyone has. I really need to get this report to work.

Thanks in advance,

Bob

 

[dhookom]

The first thing I do is check the record source query in datasheet to see if the information is there.

Are you missing records or just fields from records?

Duane
Hook'D on Access
MS Access MVP

 

[mcelligott]

Ok, did that. Only information in fields are missing. I have also gone back to the originating table and confirmed that there is actually data in the fields that are not showing in the query/report.

 

[dhookom]

Ok, did that" did what and what did you see? Apparently the values did not display in the query. Is this assumption correct? If so, play with the joins and other stuff in the queries.

Duane
Hook'D on Access
MS Access MVP

 

[mcelligott]

I looked at the query for the report in datasheet view but only some of the fields showed the data. All records showed the complaint # and employee involved but did not always show the remaining fields (specifically it all 3753 employees involved in the complaints but only showed 162 outcomes (if their part is founded or unfounded, what type of error that employee made in the complaint and what discipline each employee may have received as a result of the error). There are a total of three tables it is to pull the data from (Complaints, Employees involved, and Employee History). I have tried changing the joins with varying results. I think the problem is in having the two joins between the Employees Involved and Employee History tables and am unsure how to make it work (I realize it is not normal). Here is what the pertinent table structure looks like:
[tt]
Complaints Employees Involved Employee History
---------- ------------------ ----------------
Complaint # <----> Complaint # <------> Complaint #
: IBM # <------> IBM #
: Founded/Unfounded Type of Error
: : Discipline
: : :
: : :[/tt]

 

[dhookom]

I would make sure the joins included all records from complaints to Employees Involved and then all records from Employees Involved to Employee History.

Duane
Hook'D on Access
MS Access MVP

 

[mcelligott]

That worked for the subreport but created another problem.

Now that I can see all the information I need when I display the subreport on its own, when I add it to the main report, no records display at all. There is only one master/child connection between them (Complaint #).

Any thoughts?

 

[dhookom]

I just looked back and I'm confused by "final result of their part of the complaint".

Can we assume the main report is based on the complaints table? Did you confirm the link master child is Complaint #?

Maybe this is the penalty for having spaces and symbols in field names ;-)

Duane
Hook'D on Access
MS Access MVP

 

[mcelligott]

Ok, I admit it, I am still stuck. My head is starting to hurt either from banging it against the desk too many times or just from trying to get this to work. I apologize for a such a long message.

I am still having the same basic issue. I can not get all the fields to display their data but I am getting the correct records and number of records (checked it manually).

I will provide as much detail as I can think of to help anyone that can help me with this problem. Here is the query I have so far:

SELECT [Complaints - Employees Involved].[Complaint #], [Complaints - Employees Involved].IBM, [Employee History Table].[Entry Type], [Employee History Table].[Error Types]
FROM [Complaints - Employees Involved] LEFT JOIN [Employee History Table] ON [Complaints - Employees Involved].[Complaint #] = [Employee History Table].[Complaint #];

Here is how it looks in design view:
(both joins are many-to-many)
[tt]

Compliants - Employees Involved Employee History
------------------------------- ----------------
Complaint # <---> Complaint #
IBM <---> IBM
Entry Type
Error

Here is a sample of the result I get:

Complaint # IBM Entry Type Error
----------- --- ---------- -----
I02-0001 7123
I02-0001 7746
I02-0002 7002
I02-0003 7010
I03-0001 7887 Verbal Address Error
I03-0002 7200 C/M Delay in Dispatch
I03-0002 7343 Verbal Run Card Error
I03-0003 7480
I03-0004 7520 Verbal Address Error
I04-0001 7666
I04-0001 7229
I04-0002 7384
I04-0002 7444
I04-0003 7998
[/tt]

I have looked at the raw information for each of the results and their is data is each of the missing Error Type and Error fields.

Thanks in advance for any help.

Bob

 

[dhookom]

Your join description suggests a join on [Complaint #] and [IBM] but your SQL only joins on [Complaint #].

Duane
Hook'D on Access
MS Access MVP

 

[mcelligott]

What I described and your understanding of what I described is correct. I copy and pasted the wrong SQL. Here is the correct one.

SELECT [Complaints - Employees Involved].[Complaint #], [Complaints - Employees Involved].IBM, [Complaints - Employees Involved].[Results Of Investigation], [Complaints - Employees Involved].[Sitting on], [Complaints - Employees Involved].[Over 8 Hours], [Complaints - Employees Involved].[Day of Wheel], [Complaints - Employees Involved].[Other Comments], [Employee History Table].[Entry Type], [Employee History Table].[Error Types]
FROM [Complaints - Employees Involved] LEFT JOIN [Employee History Table] ON ([Complaints - Employees Involved].IBM = [Employee History Table].IBM) AND ([Complaints - Employees Involved].[Complaint #] = [Employee History Table].[Complaint #]);

 

[dhookom]

If I had to trouble-shoot, I would first check the screens where the information is susposed to be entered. I would then copy a sampling of the records at issue into Excel to see if there is something noticeable.

Duane
Hook'D on Access
MS Access MVP

 

[mcelligott]

That did it. I am such an idiot. The Complaint # in the Employee History table is not storing the same as it is in the other table.

Thanks for all your help Duane. It is greatly appreciated.