Linking multiple records

[David Price]

Ok I have a DB with 11 tables in. All of these tables are linked in some
way to each other. I am trying to create a query that brngs out most of the
information from most of the tables but I have a problem.

For example I have a row in my PT table where the primary key is a 6 digit
number. I then have another table called ML linked in which also has this 6
digit number along with its own unique identifying 6 digit number. The
record in the PT table (i.e. 123456) can have multiple records associated to
it from the ML table. When I run a query with both of these tables included
it will not group the records together. Instead it creates a new duplicate
record (except for the code from the ML table). So instead of having one
record from the PT table with 3 associated and grouped records, I now have 3
seperate records in my query results. I am supposed to have 714 records
from the PT table in my query results but instead I am getting well in
excess of 1500 because of this duplication.

Can anybody help? Is it possilbe to group records together like that in a
query? It works fine in the table view, all of the records show as linked
together.

P.S. I am able to email the database if anyone would like to take a look but
it is about 4.5MB in size at the moment.

 

[John Vinson]

Ok I have a DB with 11 tables in. All of these tables are linked in some
way to each other. I am trying to create a query that brngs out most of the
information from most of the tables but I have a problem.

For example I have a row in my PT table where the primary key is a 6 digit
number. I then have another table called ML linked in which also has this 6
digit number along with its own unique identifying 6 digit number. The
record in the PT table (i.e. 123456) can have multiple records associated to
it from the ML table. When I run a query with both of these tables included
it will not group the records together. Instead it creates a new duplicate
record (except for the code from the ML table).

That is exactly and precisely how a Query is designed to work. You get
one "grouped" record for every valid combination of the records in the
two tables. This is working correctly; it's your expectation that is
off!

So instead of having one
record from the PT table with 3 associated and grouped records, I now have 3
seperate records in my query results. I am supposed to have 714 records
from the PT table in my query results but instead I am getting well in
excess of 1500 because of this duplication.

Can anybody help? Is it possilbe to group records together like that in a
query? It works fine in the table view, all of the records show as linked
together.

Are you talking about Subdatasheets? If so, that is NOT WHAT IS IN THE
TABLE; the subdatasheet is a *display tool*, not a storage medium.

I think what you want is to use a Report, using the Sorting and
Grouping feature to group by the PT primary key; put the PT
information in the Group Header and the ML information in the Detail
section. This will show you each record in PT with the accompanying
records from ML beneath it.

John W. Vinson[MVP]