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 online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    8,528
    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 online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    1,720
    @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,036
    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....
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

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 - Senior Forums