searching records which have multiple related records

I

ionic-fire

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?
 

Ask a Question

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. After that, you can post your question and our members will help you out.

Ask a Question

Top