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?

    --
     
    ionic-fire, Jan 16, 2008
    #1
    1. Advertisements

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

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.
Similar Threads
  1. TC

    Add records to related tables

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

    Access table with related records

    ReidarT, Dec 2, 2003, in forum: Microsoft Access Form Coding
    Replies:
    1
    Views:
    193
    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:
    276
    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:
    251
    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:
    245
    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:
    463
    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:
    363
    Broomsticks
    Jan 15, 2008
  8. esn
    Replies:
    4
    Views:
    1,502
Loading...