Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2004
    Posts
    4

    Unanswered: Help with MsAccess Report

    I have an address database that has a main table for my relatives, and a subtable (children) for their kids. Within the subtable, I have colums such as name, marital status, dob, and email. I would like to print this information on one report, where I list the main name, address, telno (thats the easy part), then under that info, I would like to list all the married children alphabetically, and then in another row, I would like to list all the unmarried children. Hope this makes sense....I'm racking my brains.

    Mike

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Are you familiar with Groupings in reports? If not then you would do well to spend a few minutes reading about it in help.

    Your order by could be something like:

    Code:
    Iif(Marital_Status ="Married", 0, 1), Surname, ForeName
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Apr 2004
    Posts
    4

    Re Help with MsAccess Report

    Doesn't Groupings only work if all data is in same table. My sample data is below.

    In the main table I have:
    mainID = 1, mainFname = "Mike", mainFname = "Jones", Address="123 A ST"

    In the children table I have
    childId = 1, childMID = 1, childFname = "Steve", childMstatus = "M"
    childId = 2, childMID = 1, childFname = "Peter", childMstatus = "M"
    childId = 3, childMID = 1, childFname = "David", childMstatus = "M"
    childId = 4, childMID = 1, childFname = "Amy", childMstatus = "S"
    childId = 5 childMID = 1, childFname = "Paul", childMstatus = "S"
    childId = 6, childMID = 1, childFname = "Shila", childMstatus = "S"
    childId = 7, childMID = 1, childFname = "Tanya", childMstatus = "S"
    childId = 8, childMID = 1, childFname = "Scott", childMstatus = "S"

    For my report, I would like it to look like this:

    Mike Jones 123 A ST Steve Amy
    Peter Paul
    David Shila
    Tanya
    Scott


    *** I'm sure my first example didnt describe this.

    Thanks,

    Mike

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by stayanight
    Doesn't Groupings only work if all data is in same table.
    Nope - you would use a query and join the two tables and use that as the report recordsource. Something like:
    Code:
    SELECT Col1, Col2, Col3
    FROM MainTable LEFT OUTER JOIN ChildTable ON
     MainTable.mainID = ChildTable.childMID
    Are you sure your desired output if formatted correctly? I expected something more like:

    Mike Jones 123 A ST
    David
    Peter
    Steve
    Amy
    Paul
    Scott
    Shila
    Tanya
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Apr 2004
    Posts
    4

    Reply to post 'Help with MsAccess Report'

    Ideally I would like to have 5 colums in my report. The first colum to display name info, the second to display address info, the third col to display telephone info, the fourth to display married children, and fifth to display single children. As mentioned earlier, I have the first 3 colums coded, its just that fourth and fifth colum. Do I insert code into the "Control Source"? Not sure how to bring in the data from the married and unmarried tables when the id from main table matches the mainId from children table.

    Thanks

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ah - the html formatting made a mess of it then. I have wound down for the day now but... I think what you are asking is a real nightmare to solve.

    ....then again - you could do it with two sub reports - one for married children and one for the singletons.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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