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. TC

    Add records to related tables

    TC, Nov 28, 2003, in forum: Microsoft Access Form Coding
    Replies:
    5
    Views:
    228
    jcasual
    Nov 29, 2003
  2. ReidarT

    Access table with related records

    ReidarT, Dec 2, 2003, in forum: Microsoft Access Form Coding
    Replies:
    1
    Views:
    181
    Joe Fallon
    Dec 3, 2003
  3. Lars Pedersen

    I Have to press F9, to show changes on not related tables.

    Lars Pedersen, Mar 18, 2005, in forum: Microsoft Access Form Coding
    Replies:
    2
    Views:
    244
    Lars Pedersen
    Mar 19, 2005
  4. 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:
    215
    Pamela via AccessMonster.com
    Dec 7, 2005
  5. Guest

    Searching for multiple records at once

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

    duplicate row, related rows, and their related rows

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

    searching records which have multiple related records

    Broomsticks, Jan 15, 2008, in forum: Microsoft Access Form Coding
    Replies:
    0
    Views:
    292
    Broomsticks
    Jan 15, 2008
  8. esn
    Replies:
    4
    Views:
    1,404
Loading...