Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    330
    Thanks
    10
    Thanked 0 Times in 0 Posts

    How to link 2 reports? (Acc 97 sr2 on 95b)

    I have a report that shows the job and hours worked in Ord, T1/2 and T2. It is grouped by the Employee NAME and the sql is as follows:

    SELECT tblContractors.Name, tblContractors.Company, tblContTimes.[Job #], Sum(tblContTimes.OrdT) AS SumOfOrdT, Sum(tblContTimes.[T+Half]) AS [SumOfT+Half], Sum(tblContTimes.DoubleT) AS SumOfDoubleT
    FROM tblContractors INNER JOIN tblContTimes ON tblContractors.ID = tblContTimes.Employee
    WHERE (((tblContTimes.Date) Between [FROM] And [TO]))
    GROUP BY tblContractors.Name, tblContractors.Company, tblContTimes.[Job #], tblContractors.Current
    HAVING (((tblContractors.Current)=True))
    WITH OWNERACCESS OPTION;

    I have another report that I tried to put in as a subreport in the Name group header to show the start and finish time each day for each person. sql as follows:

    SELECT tblContractors.Name, Min(tblContTimes.Start) AS MinOfStart, Max(tblContTimes.Finish) AS MaxOfFinish, tblContTimes.Date
    FROM tblContTimes INNER JOIN tblContractors ON tblContTimes.Employee = tblContractors.ID
    GROUP BY tblContractors.Name, tblContTimes.Date, tblContractors.Current
    HAVING (((tblContTimes.Date) Between [FROM] And [TO]) AND ((tblContractors.Current)=True))
    WITH OWNERACCESS OPTION;

    With this I end up being asked for umpty dozen parameter inputs and then get a blank report.

    How should this be joined properly so that I am only asked for the parameters once and the information show properly?
    "Heading for the deep end"

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

    Re: How to link 2 reports? (Acc 97 sr2 on 95b)

    I don't know if this is still relevant, since your post is two weeks old.

    From looking at the SQL statements, I get the imnpression that you're trying to calculate the earliest start time and last finish time within a group. You can do that directly on the main report, in the group footer. You don't need a subreport for that. The min and max values will be calculated for the appropriate records automatically.

    But I might be completely off. If you really need the subreport, I'd create a form with text boxes to prompt the user for the FROM and TO dates. Then you can refer to the text boxes on the form in the record source of both main report and subreport. Replace

    (tblContTimes.Date) Between [From] And [To]

    by

    tblContTimes.Date Between Forms!frmPrompt!txtFrom And Forms!frmPrompt!txtTo

  3. #3
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    330
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Re: How to link 2 reports? (Acc 97 sr2 on 95b)

    The report was designed to return total hours worked on each job, showing OrdT OT and T2 for a working week. I was trying to insert a subreport, linked by name, showing the start and finish times for each day of the week covered in the main report.
    "Heading for the deep end"

Posting Permissions

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