Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Report hell - Two datasources (2000)

    I have a rather complicated (to me) query that feeds a report, the query is basically taking individual records and summing certain fields, while performing various checks on date fields. This then goes into a report showing totals.


    My problem, is that in the final report, i want to show individual records from Feed 1. (ie: the detail of the report, that gives me the numbers)
    <font color=red>(specifically, MEMOID.fsdate,MEMOID.rsdate, and NUMSICKDAYS)</font color=red> The final report looks like this:-

    Name Department Shift Attendance%

    Bob Training A 96%
    etc..etc..

    <font color=blue>25march 20 april 20 days
    14may 16 june 23 days</font color=blue>

    but underneath each record, i want to show the actual sickness records. (shown in Blue) This information comes from feed 1



    I cannot attempt to bring this information through the queries, as i would normally do, the query becomes too complex.

    I'm not sure if its any help, but the SQL is posted below.


    Feed 1 is a query to grab the raw data
    <font color=blue>SELECT MEMOID.memoid, MEMOID.mr, MEMOID.deptcode, sickr.fsdate, sickr.rsdate, MEMOID.ies, sickr.sshift, NumSickDays([fsdate],[rsdate]) AS DaysSick
    FROM MEMOID INNER JOIN sickr ON MEMOID.memoid = sickr.memoid
    GROUP BY MEMOID.memoid, MEMOID.mr, MEMOID.deptcode, sickr.fsdate, sickr.rsdate, MEMOID.ies, sickr.sshift, NumSickDays([fsdate],[rsdate]);</font color=blue>

    Feed 2 is the big query
    <font color=blue>SELECT [fname] & " " & [sname] AS Name, Sum(nz([DaysSick])) AS Sickness, [Sickness]/[Expr1]*100 AS Sick, DeltaDays(Forms!Switchboard!Begin_Date,Forms!Switc hboard!Finish_Date,[stdate],[datelc],[discounted]) AS Expr1, 100- <img src=/S/sick.gif border=0 alt=sick width=15 height=15> AS Attend, MEMOID.mr, MEMOID.deptcode, MEMOID.shiftcode, MEMOID.discounted, MEMOID.stdate, MEMOID.datelc, MEMOID.discounted, IIf([discounted] And ([datelc]<Forms!Switchboard!Begin_Date),True,False) AS TEST, MEMOID.ies, IIf([discounted] And ([datelc]<Forms!Switchboard!Begin_Date),True,False) AS Expr2, IIf(([stdate]>Forms!Switchboard!Finish_Date),True,False) AS Expr3
    FROM Sick_Totals_Feed1 RIGHT JOIN MEMOID ON Sick_Totals_Feed1.memoid = MEMOID.memoid
    GROUP BY [fname] & " " & [sname], DeltaDays(Forms!Switchboard!Begin_Date,Forms!Switc hboard!Finish_Date,[stdate],[datelc],[discounted]), MEMOID.mr, MEMOID.deptcode, MEMOID.shiftcode, MEMOID.discounted, MEMOID.stdate, MEMOID.datelc, MEMOID.discounted, MEMOID.ies, IIf(([stdate]>Forms!Switchboard!Finish_Date),True,False)
    HAVING (((MEMOID.discounted)=No) AND ((MEMOID.ies)<>"NONE") AND ((IIf([discounted] And ([datelc]<[Forms]![Switchboard]![Begin_Date]),True,False))=False) AND ((IIf(([stdate]>[Forms]![Switchboard]![Finish_Date]),True,False))=False))
    ORDER BY Sum(nz([DaysSick])) DESC;</font color=blue>

    Feed 3, (which feeds the report) just takes the percentage required and produces the report.
    <font color=blue>SELECT Sick_Totals_Feed2.Name, Sick_Totals_Feed2.Attend, Sick_Totals_Feed2.deptcode, Sick_Totals_Feed2.shiftcode, Sick_Totals_Feed2.mr
    FROM Sick_Totals_Feed2
    WHERE (((Sick_Totals_Feed2.Attend)<[Forms]![Switchboard]![Chosen_Perc]))
    ORDER BY Sick_Totals_Feed2.Attend;</font color=blue>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Report hell - Two datasources (2000)

    I can think of two different approaches. I don't know enough about your database to tell if both will work.

    1. Create a subreport to report the individual sickness records and put it on the main report, linked by the appropriate field(s).
    2. Base the report on the individual sickness records and do the calculations in the report itself.

  3. #3
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report hell - Two datasources (2000)

    Hans, i think option 1 is more likely, but i've never done it before. I know how to place a subreport on a form. but i've never placed one on a report before..

    . gimme a hint as to how to go about that. ?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Report hell - Two datasources (2000)

    Subreports are to reports as subforms are to forms.

    If necessary, start by creating a query that returns the individual sickness records.
    Then, create a report for the sickness records. You can create it manually, or with a Wizard and tweak the result. You'll probably want the report to be in tabular form (each record is one row). The report can have a report header and footer if you like, but since it is going to be used as a subreport on the main report, it shouldn't have a page header and footer. The report should not be wider than the main report. Save the report so that it appears in the database window.

    Next, open the main report in design view. Make some space where the subreport is going to be (probably in the detail section). There are several ways to place a subreport:
    1. Arrange the database window and report so that both are visible. Drag the subreport from the database window onto the report.
    2. Use the Subform/Subreport button on the Toolbox.
    2a. If you have activated Wizards, you'll be prompted to select the source object and the linking fields.
    2b. If you have not activated Wizards, you'll have to set these properties manually.

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Report hell - Two datasources (2000)

    In addition to the methods Hans has suggested, if you create the query and then drag it from the database window to the design view of the report, the subreport wizard will pop up and walk you through creating the subreport. Assuming you have the wizards installed, of course.
    Charlotte

  6. #6
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report hell - Two datasources (2000)

    Thanks all,,,,


    trying to do it now....

Posting Permissions

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