Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Missing Records In Report (XP)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29>
    I'm in need of a little <img src=/S/help.gif border=0 alt=help width=23 height=15>. I have a report which I have forced to display a single record per page. The underlying query, when run, correctly displays 244 records. However, when I run the report, only 137 records appear.

    Can anyone shed some light on the problem.

    TIA
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Missing Records In Report (XP)

    Just making sure you are execute the EXACT same query separately to come up with 244 records. (My first thought is that the discrepancy is cause by use/ommission of the DISTINCT keyword.) And when you say the report only has 137 records, are you getting this from the page count or some other means? How are you forcing a new page? (is it property setting in Detail section?)
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Missing Records In Report (XP)

    Hi Mark. Thanks for responding.

    The query which the report is based is as follows:

    SELECT tblStudent.Forename, tblStudent.Surname, tblCompany.[Company Name], tblCompany.Address1, tblCompany.Address2, tblCompany.[Town/City], tblCompany.PostCode, tblCompany.[Contact Name], tblPlacement.Week, tblStudent.[Tutor Group], tblCompany.TelNo, tblCompany.[Contact Name], tblCompany.Times, tblCompany.SpecialReq
    FROM tblStudent INNER JOIN (tblCompany INNER JOIN tblPlacement ON (tblCompany.CompanyID = tblPlacement.CompanyID) AND (tblCompany.CompanyID = tblPlacement.CompanyID) AND (tblCompany.CompanyID = tblPlacement.CompanyID)) ON (tblStudent.StudentID = tblPlacement.StudentID) AND (tblStudent.StudentID = tblPlacement.StudentID)
    ORDER BY tblStudent.[Tutor Group];

    Yes, I am getting the record number from the page count. I've looked at each page but nothing seems amiss.

    I am getting each record to display in a single page (allegedly) by copying the data textboxes and labels and pasting them into the Forename Header (Forename is the first data element for each record) created when I used the report wizard. Then adjusting the size of the headers and footers by trial and error.

    I don't know of any other way to display one record per page.
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Missing Records In Report (XP)

    The easiest way to get 1 record per page is to set the Detail section's "Force New Page" property to True. Put all your controls in the Detail section, not in a header section. The nyou can eliminate the headers and footers.

    I'm guessing you don't have anything in the Detail section now. The report is grouping based on the header, and there would be multiple records printed per header, except you aren't seeing them because you don't have anything in the Detail section.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  5. #5
    4 Star Lounger SteveH's Avatar
    Join Date
    Jan 2001
    Location
    Chelsea, Gtr London, United Kingdom
    Posts
    587
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Missing Records In Report (XP)

    A pound to a pinch the reason you are seeing less pages than student records is that you appear to be grouping on forename.
    If you have 'John Smith' and 'John Doe' they will group together, thus appearing to lose a record.
    You can only group properly on your Primary Key field.

    Just a SWAG.
    Steve H
    IT Lecturer/Access Developer
    O2K SR3/O2010; Win7Pro

  6. #6
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Missing Records In Report (XP)

    Many thanks, Mark and Steve. I actually worked out this one myself <img src=/S/fanfare.gif border=0 alt=fanfare width=31 height=23>but didn't have the opportunity to post back, until now.
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  7. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Missing Records In Report (XP)

    Are you going to let us in on it?

  8. #8
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Missing Records In Report (XP)

    Hi Pat

    Steve and Mark had rightly divined that the report was grouped on forename so all records with the same forename effectively became one record. My first solution was to add an invisible control (StudentID} and grouped it on that field. I eventually did what Mark advised, namely to copy all controls to the Details section and delete the header. I hope that explains it sufficiently.

    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

Posting Permissions

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