Results 1 to 5 of 5
  1. #1
    licka is offline Novice
    Windows 10 Access 2007
    Join Date
    Nov 2020
    Posts
    2

    Reporting

    Scenario:
    Two tables were created. tblMainMember with the following columns: PolicyNumber; LastName; FirstName; IDNumber; DOB and the second table: tblDependents with the following columns: PolicyNumber; DPLastName; DPFirstName; DPIDNumber; DPDOB. Within tblMainMember - the PolicyNumber is the primary key. In the tblDependents NO primary key. The relationship between the two tables is ONE/MANY - tblMainMember(ONE) and tblDependents(MANY).

    Use "Form Wizard" to create a frmMainMember with a subform frmDependents. Create 4 main members with all its detail where 3 of the main members have dependents.

    Use "Report Wizard" and create a report with all the detail.

    My problem: The one main member with NO dependents does not appear on the report. Why? Is there a way to show it on the report.

  2. #2
    gsrcmoorthy is offline Novice
    Windows 7 32bit Access 2016
    Join Date
    Nov 2020
    Posts
    1
    I have 10 patients data in my table. How to print one patient report?

  3. #3
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    use a form to enter the patient id

    Code:
    if isnull(txtID) then   'print all
      docmd.OpenReport "rMyReport",acViewPreview 
    else  'print 1
      docmd.OpenReport "rMyReport",acViewPreview ,,"[id]=" & me.txtID
    endif

  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    @licka
    Open the report in design view, open its record source and change the join type from equi-join to outer join (with the arrow pointing towards the dependents table).

    @gsrcmoorthy
    maybe next time start your own thread so original posters still have a chance to get their answers

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  5. #5
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    In addition, I would suggest you revisit your design.

    Consider:

    tblMainMember
    ------------------
    MainMemberID_PK (Autonumber) (Primary Key)
    PolicyNumber;
    LastName;
    FirstName;
    IDNumber;
    DOB


    tblDependents
    ------------------
    DependentID_PK (Autonumber) (Primary Key)
    MainMemberID_FK (Number-Long Integer) (Foreign key)
    PolicyNumber;
    DPLastName;
    DPFirstName;
    DPIDNumber;
    DPDOB


    Relationship 1 to Many
    tblMainMember.MainMemberID_PK --> tblDependents.MainMemberID_FK


    See Microsoft Access Tables: Primary Key Tips and Techniques



    Still need to use the outer join as per Vlad....

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Automated Reporting
    By TIG_Slayer in forum Reports
    Replies: 2
    Last Post: 08-11-2014, 09:44 AM
  2. code for reporting
    By rakhee in forum Reports
    Replies: 9
    Last Post: 04-29-2013, 11:34 PM
  3. Help with reporting
    By stevo7624 in forum Access
    Replies: 1
    Last Post: 09-03-2012, 09:54 PM
  4. reporting
    By jaykappy in forum Reports
    Replies: 10
    Last Post: 02-16-2012, 03:02 PM
  5. Complex reporting (well for me anyway)
    By Rubijon in forum Reports
    Replies: 4
    Last Post: 11-09-2011, 08:01 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums