searching records which have multiple related records

Discussion in 'Microsoft Access Form Coding' started by ionic-fire, Jan 16, 2008.

  1. ionic-fire

    ionic-fire Guest

    Greetings and salutations. I have developed an A2K3 MDB and have a search
    form modeled after Allen Browne's search form (
    http://allenbrowne.com/ser-62.html). It works well except for records which
    have multiple related records in 1:many relationships and many:to:many
    relationships. To view my results, I created a query called qryAllData which
    contains all of the data from several tables for each record.

    For example, say I have one record in the main table, and six related
    records; the search form (which is bound to qryAllData) will display six
    records. All records are duplicates of each other, except for the one field
    which will have a different value for each of the six related records.

    What I have envisioned is to have the search results return one line per
    unique primary key that matches my search criteria, and if there are any
    related records, to have a little plus-sign that the user could click to view
    the related records if need be.

    I have an access report which will display all the results in proper format
    (i.e. no duplicates, etc.). I would also like to export data to excel without
    all the duplicated data.

    What I am seeking is some input as to how I might accomplish this task. Or,
    perhaps, is access/JET even the correct program/engine to be using? Is SQL
    server or Oracle a better engine instead of JET for this particular task?

    --
    Message posted via http://www.accessmonster.com
     
    ionic-fire, Jan 16, 2008
    #1
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Pamela via AccessMonster.com

    Subforms related to same table adding multiple records

    Pamela via AccessMonster.com, Dec 6, 2005, in forum: Microsoft Access Form Coding
    Replies:
    2
    Views:
    183
    Pamela via AccessMonster.com
    Dec 7, 2005
  2. Guest

    Searching for multiple records at once

    Guest, May 31, 2006, in forum: Microsoft Access Form Coding
    Replies:
    3
    Views:
    184
    Allen Browne
    Jun 2, 2006
  3. Scott Parmelee

    duplicate row, related rows, and their related rows

    Scott Parmelee, Jul 19, 2006, in forum: Microsoft Access Form Coding
    Replies:
    2
    Views:
    233
    Scott Parmelee
    Jul 20, 2006
  4. Broomsticks

    searching records which have multiple related records

    Broomsticks, Jan 15, 2008, in forum: Microsoft Access Form Coding
    Replies:
    0
    Views:
    217
    Broomsticks
    Jan 15, 2008
  5. esn
    Replies:
    4
    Views:
    1,260
Loading...

Share This Page