Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Report is breeding!! (2000)

    Hi,
    I have a query that to produce a letter.
    This is the SQL:
    SELECT TblMain.URN, TblMain.Defendant, TblMain.DateFinalisation, TblPayments.UserName, TblCounsel.Chambers
    FROM TblMain INNER JOIN (TblCounsel INNER JOIN TblPayments ON TblCounsel.AdvocateID = TblPayments.Advocate) ON TblMain.URN = TblPayments.URN
    WHERE (((TblMain.URN)=[Forms]![frmMain]![URN]))
    WITH OWNERACCESS OPTION;

    The idea is that this produces a letter in a report format. One letter for each entry in Tbl.Counsel Chambers.
    Ideally I would like this to produce a single letter per Chambers, listing all the advocates that relate to those Chambers.

    I also have a sub-report within this report, based on this query:

    SELECT TblPayments.URN, TblCounsel.Name
    FROM TblCounsel INNER JOIN TblPayments ON TblCounsel.AdvocateID = TblPayments.Advocate;

    This lists the Advocates, with the main report listing the Chambers.
    Currently for each advocate a letter is produced, as opposed to a letter for each Chambers, regardless of the amount of advocates that work for those Chambers.

    I did have help with this about a year ago, but I cannot find the relevant post.

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

    Re: Report is breeding!! (2000)

    From your description, I would guess that you haven't linked the subreport to the main report through the Link Master Fields and Link Child Fields properties.

    You will need to include the Chambers field in the record source of the subreport, and link main and subreport on Chambers.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report is breeding!! (2000)

    The report and sub and linked through two fields, URN (which is unique) and Chambers.
    If I solely link through Chambers a get a long list of duplicated names, presumably for each case that that Chambers and that Advocate has worked on.
    If I link through both URN and Chambers, I only get a list as described previously.

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

    Re: Report is breeding!! (2000)

    Could you post a stripped down copy of the database? You know the procedure.

  5. #5
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report is breeding!! (2000)

    And here it is......the problem lays with the RptLetter

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

    Re: Report is breeding!! (2000)

    You haven't included the subreport in the database you attached.

  7. #7
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report is breeding!! (2000)

    Here's me happily deleting queries, etc...............

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

    Re: Report is breeding!! (2000)

    Open qryLetter2 in design view.
    Click in an empty part of the upper half of the query window.
    Activate the Properties window - its caption should be Query Properties.
    Set the Unique Values property to Yes.
    This should suppress the duplicates.

  9. #9
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report is breeding!! (2000)

    Thanks for having a look, this has naturally solved the problem..................very much appreciated! <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

Posting Permissions

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