Merging two tables in Dataset? I only want to get the matching info based on the two key fields

1 Dec 2006 11:55 PM

mike11d11

I have a dataset with a account table and another table that contains
transactions for accounts in the account table. I create a crystal
report based off an inner join query of the two tables but takes way
too long to generate. I was told if I can get them into one table that
would fix my speed issue. I'm thinking if there is some way to maybe
create a merged table of the two together and only getting data from
both tables where the Account# field matchup? Any help or ideas would
be great!

2 Dec 2006 3:56 AM

Cor Ligthert [MVP]

Mike,

Using a relation or just using a join direct from your database is in my
opinion the best answer.

However a direct answer to your question you find in this sample.

http://www.vb-tips.com/dbPages.aspx?ID=5fd5a8cf-54dc-4946-a193-8a9529b2b38b

I hope this helps,

Cor

Show quoteHide quote

"mike11d11" <mike11***@yahoo.com> schreef in bericht
news:1165017321.633595.208450@j44g2000cwa.googlegroups.com...
>I have a dataset with a account table and another table that contains
> transactions for accounts in the account table. I create a crystal
> report based off an inner join query of the two tables but takes way
> too long to generate. I was told if I can get them into one table that
> would fix my speed issue. I'm thinking if there is some way to maybe
> create a merged table of the two together and only getting data from
> both tables where the Account# field matchup? Any help or ideas would
> be great!
>

2 Dec 2006 8:35 AM

Rad [Visual C# MVP]

Why don't you make a view (or a stored procedure) in a database that
joins the tables in question and then use that as the data source for
the report?

On 1 Dec 2006 15:55:21 -0800, "mike11d11" <mike11***@yahoo.com> wrote:

>I have a dataset with a account table and another table that contains
>transactions for accounts in the account table. I create a crystal
>report based off an inner join query of the two tables but takes way
>too long to generate. I was told if I can get them into one table that
>would fix my speed issue. I'm thinking if there is some way to maybe
>create a merged table of the two together and only getting data from
>both tables where the Account# field matchup? Any help or ideas would
>be great!

--

Bits.Bytes.
http://bytes.thinkersroom.com

2 Dec 2006 5:49 PM

mike11d11

I thought of that, but I have a filtering process that removes accounts
from the table as people work the records on my form. They might start
with 5K
account records and then filter it down to 2K records by removing rows
that dont meet the criteria that they need to work specific records.
If I do the
join off the amount of records on the server then my report would
reflect the 5K records and there transactions and not the 2K that I
want to see at that time.

3 Dec 2006 2:40 AM

RobinS

How do you know to filter the records out? Do you have a flag in the
table in the recordset?

If dataset.merge doesn't work (and I don't think it does, because
from what I've read of it, it updates your recordset first, and
can't see the filtering you've put in place), you could always
create another datatable either inside your dataset or just a
detached one, and write a manual match/merge of the two tables,
putting the matches into the third table. You could try that and
see what kind of performance you get.

Robin S.
---------------------------------
Show quoteHide quote

"mike11d11" <mike11***@yahoo.com> wrote in message
news:1165081777.490452.266270@79g2000cws.googlegroups.com...
>I thought of that, but I have a filtering process that removes accounts
> from the table as people work the records on my form. They might start
> with 5K
> account records and then filter it down to 2K records by removing rows
> that dont meet the criteria that they need to work specific records.
> If I do the
> join off the amount of records on the server then my report would
> reflect the 5K records and there transactions and not the 2K that I
> want to see at that time.
>

8 Dec 2006 8:16 PM

mike11d11

I think I figured a fast way, I took my trans table and created another
fill query. then within my code I would flip ClearOnFill to False and
Then begin filling the trans table with each account number from my
account list and it fills my trans table with accounts the same as a
join would. It runs quickly and I guess this will be my solution for
now. I filter by containing two list boxes on my form. one populates
with all my column names and the other populates with all the unique
values from the particular column that is chosen. Right now I'm just
removing rows from the dataset that dont match the selected criteria
using the Select method. Its not the fastest solution but it is the
best that i've come up with to report off the specific records in the
table. thanks for all the help!

9 Dec 2006 2:23 AM

RobinS

Cool! I'm glad you found up with a solution!

Robin S.
-------------------
Show quoteHide quote

"mike11d11" <mike11***@yahoo.com> wrote in message
news:1165609014.610251.95760@73g2000cwn.googlegroups.com...
>I think I figured a fast way, I took my trans table and created another
> fill query. then within my code I would flip ClearOnFill to False and
> Then begin filling the trans table with each account number from my
> account list and it fills my trans table with accounts the same as a
> join would. It runs quickly and I guess this will be my solution for
> now. I filter by containing two list boxes on my form. one populates
> with all my column names and the other populates with all the unique
> values from the particular column that is chosen. Right now I'm just
> removing rows from the dataset that dont match the selected criteria
> using the Select method. Its not the fastest solution but it is the
> best that i've come up with to report off the specific records in the
> table. thanks for all the help!
>