VB & Database, VB5-DB:INNER JOIN on two crosstab recordsets?

VB5-DB:INNER JOIN on two crosstab recordsets?

Author	Message
John Curti #1 / 5	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

stellus perei #2 / 5	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

John Curti #3 / 5	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

Raul Ricardo Coba Poo #4 / 5	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

John Curti #5 / 5	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

Page 1 of 1

[ 5 post ]