VB5-DB:INNER JOIN on two crosstab recordsets? 
Author Message
 VB5-DB:INNER JOIN on two crosstab recordsets?

Help!  I'm trying to migrate an access 2.0 database to a VB only system
(and then to Oracle) and I'm having a problem with a report.  In Access
I run two crosstab queries, and then in a separate query I do an inner
join on two common fields.  This was really easy using the QBE grid in
Access, but I cannot figure out how to do this in VB.

I tried embedding SQL code, but it won't let me do this in the FROM
section of the query.

I can build a temporary table, but the table would have to be generate
every time a user ran a report.  Since many users will be running
reports at the same time, I would have to come up with some way to give
each user their own temp table.  None of these options are attractive to
me.

Any ideas?  Thanks in advance!

J.C.



Fri, 07 Jul 2000 03:00:00 GMT  
 VB5-DB:INNER JOIN on two crosstab recordsets?

Hi, John C,

I couldn't get the exact nature of your problem from your explantion,

sSql="INSERT INTO C ...... SELECT x,y FROM a,b WHERE a.fld1=b.fld1"

if you are using Database1.Execute sSQL dbFailOnError           '

should work I suppose! I don't have ORACLE env to test
It might be helpful, if someone can go through the code,

Stellus Pereira


says...

Quote:

>Help!  I'm trying to migrate an access 2.0 database to a VB only system
>(and then to Oracle) and I'm having a problem with a report.  In Access
>I run two crosstab queries, and then in a separate query I do an inner
>join on two common fields.  This was really easy using the QBE grid in
>Access, but I cannot figure out how to do this in VB.

>I tried embedding SQL code, but it won't let me do this in the FROM
>section of the query.

>I can build a temporary table, but the table would have to be generate
>every time a user ran a report.  Since many users will be running
>reports at the same time, I would have to come up with some way to give
>each user their own temp table.  None of these options are attractive to
>me.

>Any ideas?  Thanks in advance!

>J.C.



Sat, 08 Jul 2000 03:00:00 GMT  
 VB5-DB:INNER JOIN on two crosstab recordsets?

Stellus,
Thanks for the help!  The problem is that the FROM clause needs to
contain the output of two different queries.  In your example a and b
would be the results of two different crosstab queries.

The crosstabs are used in a production monitoring report.  The first
crosstab creates a table where every reject station is given a column,
and the rows are described by the date and shift.  The data is the nuber
of units rejected at each station.  The second crosstab has the same
columns, but returns the nuber of good units instead of the number of
rejects.  I would like to combine the two into a single query that I
could then use to calculate some percentages before outputting to a
report.  Any ideas?

Thanks again for the help!

J.C.

Quote:

> Hi, John C,

> I couldn't get the exact nature of your problem from your explantion,

> sSql="INSERT INTO C ...... SELECT x,y FROM a,b WHERE a.fld1=b.fld1"

> if you are using Database1.Execute sSQL dbFailOnError           '

> should work I suppose! I don't have ORACLE env to test
> It might be helpful, if someone can go through the code,

> Stellus Pereira



> says...

> >Help!  I'm trying to migrate an access 2.0 database to a VB only
> system
> >(and then to Oracle) and I'm having a problem with a report.  In
> Access
> >I run two crosstab queries, and then in a separate query I do an
> inner
> >join on two common fields.  This was really easy using the QBE grid
> in
> >Access, but I cannot figure out how to do this in VB.

> >I tried embedding SQL code, but it won't let me do this in the FROM
> >section of the query.

> >I can build a temporary table, but the table would have to be
> generate
> >every time a user ran a report.  Since many users will be running
> >reports at the same time, I would have to come up with some way to
> give
> >each user their own temp table.  None of these options are attractive
> to
> >me.

> >Any ideas?  Thanks in advance!

> >J.C.



Sun, 09 Jul 2000 03:00:00 GMT  
 VB5-DB:INNER JOIN on two crosstab recordsets?

        The Queries tab objects in MSAccess can be called fom VB using
        the QuerydefObject.
        Firts create your two Crosstab queries, and the the third based in the
        two previous.
        In VB:

        dim db as database
        dim qd as querydef
        dim rs as recordset

        set db = Opendatabase("MyDB")
        set qd = db.querydefs("TheThirdQuery")
        set rs = qd.openrecordset

Hope this helps

--
8-) Raul Coba Poot



Quote:
> Stellus,
> Thanks for the help!  The problem is that the FROM clause needs to
> contain the output of two different queries.  In your example a and b
> would be the results of two different crosstab queries.

> The crosstabs are used in a production monitoring report.  The first
> crosstab creates a table where every reject station is given a column,
> and the rows are described by the date and shift.  The data is the nuber
> of units rejected at each station.  The second crosstab has the same
> columns, but returns the nuber of good units instead of the number of
> rejects.  I would like to combine the two into a single query that I
> could then use to calculate some percentages before outputting to a
> report.  Any ideas?

> Thanks again for the help!

> J.C.

> > Hi, John C,

> > I couldn't get the exact nature of your problem from your explantion,

> > sSql="INSERT INTO C ...... SELECT x,y FROM a,b WHERE a.fld1=b.fld1"

> > if you are using Database1.Execute sSQL dbFailOnError           '

> > should work I suppose! I don't have ORACLE env to test
> > It might be helpful, if someone can go through the code,

> > Stellus Pereira



> > says...

> > >Help!  I'm trying to migrate an access 2.0 database to a VB only
> > system
> > >(and then to Oracle) and I'm having a problem with a report.  In
> > Access
> > >I run two crosstab queries, and then in a separate query I do an
> > inner
> > >join on two common fields.  This was really easy using the QBE grid
> > in
> > >Access, but I cannot figure out how to do this in VB.

> > >I tried embedding SQL code, but it won't let me do this in the FROM
> > >section of the query.

> > >I can build a temporary table, but the table would have to be
> > generate
> > >every time a user ran a report.  Since many users will be running
> > >reports at the same time, I would have to come up with some way to
> > give
> > >each user their own temp table.  None of these options are attractive
> > to
> > >me.

> > >Any ideas?  Thanks in advance!

> > >J.C.



Sun, 09 Jul 2000 03:00:00 GMT  
 VB5-DB:INNER JOIN on two crosstab recordsets?

Raul,
Thanks for the help!  The only problem is that I would like to avoid
using the Jet engine (for speed reasons), so I would like to have all of
the SQL and recordsets in VB.  I tried defining three querydefs
(temporary), but I can't seem to link them within VB.

Thanks again for the help.

John

p.s.  Which country is .mx?  Just curious.

Quote:
>         The Queries tab objects in MSAccess can be called fom VB using

>         the QuerydefObject.
>         Firts create your two Crosstab queries, and the the third
> based in the
>         two previous.
>         In VB:

>         dim db as database
>         dim qd as querydef
>         dim rs as recordset

>         set db = Opendatabase("MyDB")
>         set qd = db.querydefs("TheThirdQuery")
>         set rs = qd.openrecordset

> Hope this helps

> --
> 8-) Raul Coba Poot



> > Stellus,
> > Thanks for the help!  The problem is that the FROM clause needs to
> > contain the output of two different queries.  In your example a and
> b
> > would be the results of two different crosstab queries.

> > The crosstabs are used in a production monitoring report.  The first

> > crosstab creates a table where every reject station is given a
> column,
> > and the rows are described by the date and shift.  The data is the
> nuber
> > of units rejected at each station.  The second crosstab has the same

> > columns, but returns the nuber of good units instead of the number
> of
> > rejects.  I would like to combine the two into a single query that I

> > could then use to calculate some percentages before outputting to a
> > report.  Any ideas?

> > Thanks again for the help!

> > J.C.

> > > Hi, John C,

> > > I couldn't get the exact nature of your problem from your
> explantion,

> > > sSql="INSERT INTO C ...... SELECT x,y FROM a,b WHERE
> a.fld1=b.fld1"

> > > if you are using Database1.Execute sSQL dbFailOnError           '

> > > should work I suppose! I don't have ORACLE env to test
> > > It might be helpful, if someone can go through the code,

> > > Stellus Pereira



> > > says...

> > > >Help!  I'm trying to migrate an access 2.0 database to a VB only
> > > system
> > > >(and then to Oracle) and I'm having a problem with a report.  In
> > > Access
> > > >I run two crosstab queries, and then in a separate query I do an
> > > inner
> > > >join on two common fields.  This was really easy using the QBE
> grid
> > > in
> > > >Access, but I cannot figure out how to do this in VB.

> > > >I tried embedding SQL code, but it won't let me do this in the
> FROM
> > > >section of the query.

> > > >I can build a temporary table, but the table would have to be
> > > generate
> > > >every time a user ran a report.  Since many users will be running

> > > >reports at the same time, I would have to come up with some way
> to
> > > give
> > > >each user their own temp table.  None of these options are
> attractive
> > > to
> > > >me.

> > > >Any ideas?  Thanks in advance!

> > > >J.C.



Sun, 09 Jul 2000 03:00:00 GMT  
 
 [ 5 post ] 

 Relevant Pages 

1. SELECT . INNER JOIN--INNER JOIN

2. Performing an INNER JOIN in two different databases.

3. Problem adding new record to inner join recordset...

4. Problem adding new record to inner join recordset...

5. Problem adding new record to inner join recordset...

6. Problem adding new record to inner join recordset...

7. Using ADO recordsets in an inner join

8. Query/Recordset problem on inner join with sql oledb provider

9. error updating a recordset using an inner join - please help

10. Using ado recordset in an SQL Server inner join

11. VB5: Inner Join with 2 ON clause

12. VB5 Newbie - Inner Joins on DBGrid Control

 

 
Powered by phpBB® Forum Software