Combining 20+ Tables into One 
Author Message
 Combining 20+ Tables into One

I'm working on a project where information from 20 or so tables must be
combined into one table.  No problem right?  I made a query that links all
of the tables together by their correct fields and all worked well, until
the query started returning duplicate records (not exactly dups., just
multiple records for one record from the main table).

Here's how:

There's one main table where I want every row returned.  Say it has 5 rows,
I want all 5 of those rows to show up in the query.

All the other tables link off of that one.  The tables are linked by an ID
field, the relationship type is one-to-many and the join type is #2
 display all records form the parent table, only records from the daughter
table where the ID fields match)

The problems come in when the daughter tables have more than one record that
matches the main table.

Example Tables:
(Numbers are ID fields, letters are data to be displayed)

Main Table        Daughter1        Daughter2
1    A                1    F                1    L
2    B                1    G                1    M
3    C                2    H                2    N
4    D                2    I                 2    O
5    E                3    K                3    P

The following would return:

AFL
AFM
AGL
AGM
BHN
BHO
BIN
BIO
CKP
D
E

The numbers in this example were scaled down for clarity.  I'm working with
a main table that has 40,000 unique records, and the daughter tables
sometimes have more than 2 records that match the record from the main
table.  When 20+ daughter tables are linked to the main table, the size of
the output table grows exponentially!  Populating a query based on a main
table of 40,000 records returns an output table of over 2 million records!
One row from the main table can produce hundreds of thousands of records.

Queries returning this much data are too big and take too long to return.
Does anyone have any suggestions of another way to retrieve all this data?
Are there procedures I could write in Access that could combine this data
more efficiently?  By the way, this data will have to be accessible thru an
ODBC link.

Please also reply to me email!

Thanks,
Courtney



Mon, 23 Sep 2002 03:00:00 GMT  
 Combining 20+ Tables into One
What kind of data are you holding in the daughter tables?  What do you want
to get from them?
What kind of result are you looking for?

Michael Kucan

Quote:

>I'm working on a project where information from 20 or so tables must be
>combined into one table.  No problem right?  I made a query that links all
>of the tables together by their correct fields and all worked well, until
>the query started returning duplicate records (not exactly dups., just
>multiple records for one record from the main table).

>Here's how:

>There's one main table where I want every row returned.  Say it has 5 rows,
>I want all 5 of those rows to show up in the query.

>All the other tables link off of that one.  The tables are linked by an ID
>field, the relationship type is one-to-many and the join type is #2
> display all records form the parent table, only records from the daughter
>table where the ID fields match)

>The problems come in when the daughter tables have more than one record
that
>matches the main table.

>Example Tables:
>(Numbers are ID fields, letters are data to be displayed)

>Main Table        Daughter1        Daughter2
>1    A                1    F                1    L
>2    B                1    G                1    M
>3    C                2    H                2    N
>4    D                2    I                 2    O
>5    E                3    K                3    P

>The following would return:

>AFL
>AFM
>AGL
>AGM
>BHN
>BHO
>BIN
>BIO
>CKP
>D
>E

>The numbers in this example were scaled down for clarity.  I'm working with
>a main table that has 40,000 unique records, and the daughter tables
>sometimes have more than 2 records that match the record from the main
>table.  When 20+ daughter tables are linked to the main table, the size of
>the output table grows exponentially!  Populating a query based on a main
>table of 40,000 records returns an output table of over 2 million records!
>One row from the main table can produce hundreds of thousands of records.

>Queries returning this much data are too big and take too long to return.
>Does anyone have any suggestions of another way to retrieve all this data?
>Are there procedures I could write in Access that could combine this data
>more efficiently?  By the way, this data will have to be accessible thru an
>ODBC link.

>Please also reply to me email!

>Thanks,
>Courtney



Mon, 23 Sep 2002 03:00:00 GMT  
 Combining 20+ Tables into One
I'm sorry I didn't make what I wanted clear.  I know the results I'm getting
are correct.  I would like to somehow combine the data into one row or find
a way to return all the same info faster.

Maybe instead of:

1    AFL
2    AFM
3    AGL
4    AGM
5    BHN
6    BHO
7    BIN
8    BIO
9    CKP
10    D
11    E

Something like:
      (-------one column---------)
1    AFL, AFM, AGL, AGM
2    BHN, BHO, BIN, BIO
3    CKP
4    D
5    E

Combining all the info into one row wouldn't really make it faster but it'd
make it easier on the progrmmers that have to use the table.  Is there
anything else I do to return all the info faster.  Multiple queries?  I
don't know.

Courtney


Quote:
> What kind of data are you holding in the daughter tables?  What do you
want
> to get from them?
> What kind of result are you looking for?

> Michael Kucan


> >I'm working on a project where information from 20 or so tables must be
> >combined into one table.  No problem right?  I made a query that links
all
> >of the tables together by their correct fields and all worked well, until
> >the query started returning duplicate records (not exactly dups., just
> >multiple records for one record from the main table).

> >Here's how:

> >There's one main table where I want every row returned.  Say it has 5
rows,
> >I want all 5 of those rows to show up in the query.

> >All the other tables link off of that one.  The tables are linked by an
ID
> >field, the relationship type is one-to-many and the join type is #2
> > display all records form the parent table, only records from the
daughter
> >table where the ID fields match)

> >The problems come in when the daughter tables have more than one record
> that
> >matches the main table.

> >Example Tables:
> >(Numbers are ID fields, letters are data to be displayed)

> >Main Table        Daughter1        Daughter2
> >1    A                1    F                1    L
> >2    B                1    G                1    M
> >3    C                2    H                2    N
> >4    D                2    I                 2    O
> >5    E                3    K                3    P

> >The following would return:

> >AFL
> >AFM
> >AGL
> >AGM
> >BHN
> >BHO
> >BIN
> >BIO
> >CKP
> >D
> >E

> >The numbers in this example were scaled down for clarity.  I'm working
with
> >a main table that has 40,000 unique records, and the daughter tables
> >sometimes have more than 2 records that match the record from the main
> >table.  When 20+ daughter tables are linked to the main table, the size
of
> >the output table grows exponentially!  Populating a query based on a main
> >table of 40,000 records returns an output table of over 2 million
records!
> >One row from the main table can produce hundreds of thousands of records.

> >Queries returning this much data are too big and take too long to return.
> >Does anyone have any suggestions of another way to retrieve all this
data?
> >Are there procedures I could write in Access that could combine this data
> >more efficiently?  By the way, this data will have to be accessible thru
an
> >ODBC link.

> >Please also reply to me email!

> >Thanks,
> >Courtney



Tue, 24 Sep 2002 03:00:00 GMT  
 Combining 20+ Tables into One
The best way to speed up a query is the proper use of indexes (Unique
Primary, if you can get away with it).  Make sure that each field you are
linking on (in each table) has an index.  That's about my only solution,
other than upgrading your whole system to SQL Server.

Sorry I couldn't be of more help.
Michael Kucan

Quote:

>I'm sorry I didn't make what I wanted clear.  I know the results I'm
getting
>are correct.  I would like to somehow combine the data into one row or find
>a way to return all the same info faster.

>Maybe instead of:

>1    AFL
>2    AFM
>3    AGL
>4    AGM
>5    BHN
>6    BHO
>7    BIN
>8    BIO
>9    CKP
>10    D
>11    E

>Something like:
>      (-------one column---------)
>1    AFL, AFM, AGL, AGM
>2    BHN, BHO, BIN, BIO
>3    CKP
>4    D
>5    E

>Combining all the info into one row wouldn't really make it faster but it'd
>make it easier on the progrmmers that have to use the table.  Is there
>anything else I do to return all the info faster.  Multiple queries?  I
>don't know.

>Courtney



>> What kind of data are you holding in the daughter tables?  What do you
>want
>> to get from them?
>> What kind of result are you looking for?

>> Michael Kucan


>> >I'm working on a project where information from 20 or so tables must be
>> >combined into one table.  No problem right?  I made a query that links
>all
>> >of the tables together by their correct fields and all worked well,
until
>> >the query started returning duplicate records (not exactly dups., just
>> >multiple records for one record from the main table).

>> >Here's how:

>> >There's one main table where I want every row returned.  Say it has 5
>rows,
>> >I want all 5 of those rows to show up in the query.

>> >All the other tables link off of that one.  The tables are linked by an
>ID
>> >field, the relationship type is one-to-many and the join type is #2
>> > display all records form the parent table, only records from the
>daughter
>> >table where the ID fields match)

>> >The problems come in when the daughter tables have more than one record
>> that
>> >matches the main table.

>> >Example Tables:
>> >(Numbers are ID fields, letters are data to be displayed)

>> >Main Table        Daughter1        Daughter2
>> >1    A                1    F                1    L
>> >2    B                1    G                1    M
>> >3    C                2    H                2    N
>> >4    D                2    I                 2    O
>> >5    E                3    K                3    P

>> >The following would return:

>> >AFL
>> >AFM
>> >AGL
>> >AGM
>> >BHN
>> >BHO
>> >BIN
>> >BIO
>> >CKP
>> >D
>> >E

>> >The numbers in this example were scaled down for clarity.  I'm working
>with
>> >a main table that has 40,000 unique records, and the daughter tables
>> >sometimes have more than 2 records that match the record from the main
>> >table.  When 20+ daughter tables are linked to the main table, the size
>of
>> >the output table grows exponentially!  Populating a query based on a
main
>> >table of 40,000 records returns an output table of over 2 million
>records!
>> >One row from the main table can produce hundreds of thousands of
records.

>> >Queries returning this much data are too big and take too long to
return.
>> >Does anyone have any suggestions of another way to retrieve all this
>data?
>> >Are there procedures I could write in Access that could combine this
data
>> >more efficiently?  By the way, this data will have to be accessible thru
>an
>> >ODBC link.

>> >Please also reply to me email!

>> >Thanks,
>> >Courtney



Tue, 24 Sep 2002 03:00:00 GMT  
 Combining 20+ Tables into One


Quote:
>Combining all the info into one row wouldn't really make it faster but it'd
>make it easier on the progrmmers that have to use the table.  Is there
>anything else I do to return all the info faster.  Multiple queries?  I
>don't know.

Combining it can be done using VBA: go to Dev Ashish's site
www.mvps.org/access and search for Concatenate.

It will certainly slow the process down, not speed it up, however.

Since I really have no idea how your programmers are looking at this
data, how they are using it, or what the data looks like, it's awfully
hard to make any useful suggestions.  Speed will be affected by the
indexing of your tables; but if you've set relationships and compacted
the database, a single query will probably be the fastest option.

           John W. Vinson[MVP]    
http://go.compuserve.com/msdevapps?LOC=US



Tue, 24 Sep 2002 03:00:00 GMT  
 Combining 20+ Tables into One
HI Courtney,

I am suspicious that if you are returning "millions" of records that you may
doing something to invoke a Cartesian Product situation. In Access, that will
happen if you are building a query and neglect to put a join line between two
tables. If each table has 3 rows, the cartesian product result will be 9 rows.
In other words, number of rows in tabl1 times number of rows in table2.

Norm

Quote:

> I'm sorry I didn't make what I wanted clear.  I know the results I'm getting
> are correct.  I would like to somehow combine the data into one row or find
> a way to return all the same info faster.

> Maybe instead of:

> 1    AFL
> 2    AFM
> 3    AGL
> 4    AGM
> 5    BHN
> 6    BHO
> 7    BIN
> 8    BIO
> 9    CKP
> 10    D
> 11    E

> Something like:
>       (-------one column---------)
> 1    AFL, AFM, AGL, AGM
> 2    BHN, BHO, BIN, BIO
> 3    CKP
> 4    D
> 5    E

> Combining all the info into one row wouldn't really make it faster but it'd
> make it easier on the progrmmers that have to use the table.  Is there
> anything else I do to return all the info faster.  Multiple queries?  I
> don't know.

> Courtney



> > What kind of data are you holding in the daughter tables?  What do you
> want
> > to get from them?
> > What kind of result are you looking for?

> > Michael Kucan


> > >I'm working on a project where information from 20 or so tables must be
> > >combined into one table.  No problem right?  I made a query that links
> all
> > >of the tables together by their correct fields and all worked well, until
> > >the query started returning duplicate records (not exactly dups., just
> > >multiple records for one record from the main table).

> > >Here's how:

> > >There's one main table where I want every row returned.  Say it has 5
> rows,
> > >I want all 5 of those rows to show up in the query.

> > >All the other tables link off of that one.  The tables are linked by an
> ID
> > >field, the relationship type is one-to-many and the join type is #2
> > > display all records form the parent table, only records from the
> daughter
> > >table where the ID fields match)

> > >The problems come in when the daughter tables have more than one record
> > that
> > >matches the main table.

> > >Example Tables:
> > >(Numbers are ID fields, letters are data to be displayed)

> > >Main Table        Daughter1        Daughter2
> > >1    A                1    F                1    L
> > >2    B                1    G                1    M
> > >3    C                2    H                2    N
> > >4    D                2    I                 2    O
> > >5    E                3    K                3    P

> > >The following would return:

> > >AFL
> > >AFM
> > >AGL
> > >AGM
> > >BHN
> > >BHO
> > >BIN
> > >BIO
> > >CKP
> > >D
> > >E

> > >The numbers in this example were scaled down for clarity.  I'm working
> with
> > >a main table that has 40,000 unique records, and the daughter tables
> > >sometimes have more than 2 records that match the record from the main
> > >table.  When 20+ daughter tables are linked to the main table, the size
> of
> > >the output table grows exponentially!  Populating a query based on a main
> > >table of 40,000 records returns an output table of over 2 million
> records!
> > >One row from the main table can produce hundreds of thousands of records.

> > >Queries returning this much data are too big and take too long to return.
> > >Does anyone have any suggestions of another way to retrieve all this
> data?
> > >Are there procedures I could write in Access that could combine this data
> > >more efficiently?  By the way, this data will have to be accessible thru
> an
> > >ODBC link.

> > >Please also reply to me email!

> > >Thanks,
> > >Courtney



Thu, 26 Sep 2002 03:00:00 GMT  
 
 [ 6 post ] 

 Relevant Pages 

1. PC Install from 20/20 software

2. Stateless search from database (Return 20 and 20 records)

3. How to make a sinus output from 20 Hz to 20 KHz

4. Combining two tables with one field in common

5. Create table from a combined field in another table

6. SQL - Join one table in one destination to another table in another destination (DBF)

7. QB45: difference between DIM a$(20) and DIM a(20) AS STRING?

8. Combine multiple records into one

9. Combine multple records into one expression??

10. Combine multiple records into one

11. Combining two or three fields into one?

12. Combining data from several fields into one

 

 
Powered by phpBB® Forum Software