Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2006
    Posts
    2

    Unanswered: Two queries / one report

    'Two queries / one report' seems to be a common topic, but I still can't find the information I need.

    Normally I am pretty good at finding answers by through google or searching a forum like this one, but in this case I don't know exactly what I'm looking for help on.

    Let's say I have two tables that look like this:

    Employees
    ---------
    ID
    Name
    Hire Date


    Absence
    -------
    ID
    Date
    Reason


    So there is a one-to-many relationship and the tables link on ID.

    I want a report in which each page looks a little something like this:

    =====
    Name: John Smith
    ID: 123

    Absences:
    Dec 12, 2006 sick
    Dec 18, 2006 car trouble
    Dec 22, 2006 cat died
    =====

    I think that putting all these fields into a single query and feeding that into a report would give me a separate page for each absence, would it not?

    I see the word "subreport" thrown around, maybe that is what I'm looking for, with two queries . . ?

    In case it matters, there are a couple other associated tables similar to Absences, so the solution will have to work with more than just one linked table.

    Any help would be greatly appreciated. Thanks in advance.

    -SD

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I think "sorting and grouping" is what you're after...

    Also, looks like John had a rough December.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Dec 2006
    Posts
    2
    Grouping seemed to work when one "sub-table" is involved. With more than one, it didn't work so well, so I investigated Subreports and that seemed to work. THANKS..

    PS. John also had surgery on Dec 28/2006. I now have to find a way to globally set name = "Jane Smith" for ID=123.
    Last edited by StephanieD; 12-29-06 at 10:27.

  4. #4
    Join Date
    Jul 2003
    Posts
    16
    If its fairly simple, like you've described, you could use the table directly or a query, no biggie. But you're on the right track with sub-report. Base the main report on the main info, make sure you have the EmpID on that report. Call this one rpt-MainWhatever. Then create a second report with the secondary info, the EmpID needs to be in this report also for linking purposes. I usually hide this field (looking Properties, Visible). Now use the sub-report wizard and put sub-Whatever into the main report. If the EmpID fields are already linked, Access should choose these as the link. And there you have it. One report per employee with all the Absences showing up in a sub-report on the same page.

Posting Permissions

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