Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2012
    Location
    New Zealand
    Posts
    10

    Unanswered: Report subreport problem

    I have a form that records accidents
    a subform selects employee can be more than one - involved
    another subform records notes - date employee notes. All works fine
    Both these draw from the same tblEmployess

    In the report the Detail lists the notes by date and all works fine . As soon as i put the sub report into the header the detail will only show notes by the employee listed in the subreport
    subreport has it's own query
    attched is a screenshot of the relationships
    As soon as I put the tblEmpJoin into the reports query the problem starts. Of course I need the EmpTblFK to link the master to the IDEmployee child.
    I'm obviously doing something wrong here
    Attached Thumbnails Attached Thumbnails incss.png  
    Last edited by libraccess; 02-05-13 at 20:59.

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    I admit confusion, but one thing I do see. Sub reports don't belong in headers. By definition, they are a level BELOW the main detail, not above it. In practice, they should be in the Detail Section. If there is a need, you can manufacture a new header section containing the main data, with the sub-report in the Detail Section.

    Sam

  3. #3
    Join Date
    Feb 2012
    Location
    New Zealand
    Posts
    10
    mmm OK not sure how that will go I will have the notes in the detail and if I put the EmpTblFK in there it will repeat for every note .Can I have two details? It is not putting the subreport in the header that is the problem. I can have it there and it will sow every employee. It is when I want to do the master child link to show only results for this record I need to add the tblEmpJoin and then the Details revert to only one record. I don't even need to add IDemployee to the query as soon as the tblEmpJoin is put in the design view the error occurs so i guess it must be query related


    Query with tblEmpJoin included
    SELECT tblIncident.ID, tblIncType.IncType, tblIncSeverity.IncSeverity, tblIncident.IncDate, tblIncident.IncTime, tblIncSeverity.IncSeverity, tblEmployee.Surname, tblEmployee.NameFirst, tblIncNotes.NotebyFK, tblIncident.Injury, tblIncident.Damage, tblRegion.strRegion, tblPersonnel.StaffName, tblIncident.[Cost-Est], tblIncident.[Cost-Final], tblIncident.[Lost-Days], tblIncident.Status, tblIncident.DateResolved, tblIncident.MultipleIncRef, tblIncident.LocationofInc, tblIncNotes.NoteDate, tblIncNotes.notes, tblIncident.[Vehicle-id]
    FROM (tblEmployee INNER JOIN tblEmpJoin ON tblEmployee.IDEmployee = tblEmpJoin.EmpTblFK) INNER JOIN ((tblIncSeverity INNER JOIN (tblPersonnel INNER JOIN (tblIncType INNER JOIN (tblRegion INNER JOIN tblIncident ON tblRegion.RegionID = tblIncident.UserFK) ON tblIncType.IDIncType = tblIncident.IncTypeFK) ON tblPersonnel.lngUserID = tblIncident.PersonnelFK) ON tblIncSeverity.IDIncSeverity = tblIncident.SeverityFK) INNER JOIN tblIncNotes ON tblIncident.ID = tblIncNotes.inc_id) ON (tblIncident.ID = tblEmpJoin.EmpJoinFK) AND (tblEmployee.IDEmployee = tblIncNotes.NotebyFK);

  4. #4
    Join Date
    Feb 2012
    Location
    New Zealand
    Posts
    10
    OK your suggestion got me thinking
    leave the tblEmpJoin out of the Report Query
    the sub report query looks like this
    with the Where condition taking care of the inability to do a master child link



    SELECT tblIncident.ID, tblEmpJoin.EmpTblFK, [Namefirst] & " " & [Surname] AS Expr1
    FROM tblIncident INNER JOIN (tblEmployee INNER JOIN tblEmpJoin ON tblEmployee.IDEmployee = tblEmpJoin.EmpTblFK) ON tblIncident.ID = tblEmpJoin.EmpJoinFK
    WHERE (((tblIncident.ID)=[Forms]![frmIncident]![ID]));

    so it now works subreport in the header and the Detail still working as it should
    thanks

  5. #5
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    I think the problem is having tblEmpJoin and tblIncNotes both as intersection tables between tblEmployee and tblIncident. This structure will allow you to enter notes for an incident under an employee that was not involved!
    Instead, if you do not require notes for every employee involved in an incident, consider delinking tblIncNotes from tblIncident and instead hanging it off tblEmpJoin. You can still use the incident and employee IDs as primary keys, and this will mean that you can only enter notes that relate to valid incident/employee combinations.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •