Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Visual Basic > VB 6 Visual Basic 6 > Pro VB Databases
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
Pro VB Databases Advanced-level VB coding questions specific to using VB with databases. Beginning-level questions or issues not specific to database use will be redirected to other forums.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Pro VB Databases section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old March 3rd, 2007, 09:55 AM
Registered User
 
Join Date: Mar 2007
Location: Karachi, Sindh, Pakistan.
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to kamrans74
Default Query from Recordset into another Recordset

Hello!
I am using VB6. backend Access 2000, I have two tables in Database called Sales and Purchase. I was create a Recordset from both tables :-

dim rs1 as dao.recordset

sSqlText = "select purchase.date,purchase.itemcode,purchase.qty,'P' as status from purchase union select sales.date,sales.itemcode,sales.qty,'S' as status from sales"

set rs1 = db1.OpenRecordset(sSqlText,dbOpenSnapshot)
// this recordset was create successfully

Now I want create another Recordset from rs1, which will sum all the Quantity of all items from rs1 into new Recordset rs2.

REMEMBER I can also doing it in one Recordset but I want to create another recordset which is summarize Quantities from rs1.





Reply With Quote
  #2 (permalink)  
Old March 3rd, 2007, 10:54 AM
Friend of Wrox
 
Join Date: May 2006
Location: San Diego, CA, USA.
Posts: 643
Thanks: 0
Thanked 0 Times in 0 Posts
Default

It is not clean what you mean by "create another recordset".

Do you want to create a dynamic recordset that will only temporarily hold some values and that will not persist the data after you have used it in your code?

Or do you want to sum these values and then actually store the records into another table that holds your summed data permanently?

Or what?

If you just want a temporary storage for your data and you want to use a recordset, you can recreate one on the fly, add the fields you need, and add records to it.

If you want to persist data and you want to be using a recordset, you can query the table that stores your summed records with a query that returns no records, then add records to it and update them as you go. This saves the data to the database.

However, I am unclear as to what you are actually asking.

Woody Z
http://www.learntoprogramnow.com
How to use a forum to help solve problems
Reply With Quote
  #3 (permalink)  
Old March 3rd, 2007, 11:41 AM
Registered User
 
Join Date: Mar 2007
Location: Karachi, Sindh, Pakistan.
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to kamrans74
Default

Thanks for reply!

Let me explain:-

Table List :-
  First Table PURCHASE:-
   ItemCode Date Quantity
    0001 01/03/2007 10 Pcs
    0002 01/03/2007 20 Pcs
    0003 01/03/2007 30 Pcs

  Second Table SALES:-
   ItemCode Date Quantity
    0001 02/03/2007 5 Pcs
    0002 02/03/2007 5 Pcs
    0003 03/03/2007 10 Pcs

I was create a Recordset from these table with following code.

dim rs1 as dao.Recordset
dim rs2 as dao.Recordset
dim sSqlText as String

sSqlText = "select itemcode, date, quantity, 'P' as status from purchase"
sSqlText = sSqlText & " union "
sSqlText = "select itemcode, date, quantity, 'S' as status from sales"

set rs1 = db1.OpenRecordset(sSqlText,dbOpenSnapShot)
the recordset create successfully.

Now I want to Create a Recordset2 from the Recordset1.
for example :-

dim sSqlText2 as string
sSqlText2 = "select itemcode,sum(iif(status='P',quantity,-quantity) as ItemBalance"

set rs2 = rs1.requery(sSqlText2)
The above code is not working and rais an error.

Please give me solution of this Problem or your have any other idea to resolve it.

Thanks





Reply With Quote
  #4 (permalink)  
Old March 4th, 2007, 03:08 AM
Friend of Wrox
 
Join Date: May 2006
Location: San Diego, CA, USA.
Posts: 643
Thanks: 0
Thanked 0 Times in 0 Posts
Default

iif in a select statement? I suppose that works in Access?
What is the error you are getting?
You are using an aggregate function and that might be your problem.
Also, your second sql statement does not have a FROM table.

Woody Z
http://www.learntoprogramnow.com
How to use a forum to help solve problems
Reply With Quote
  #5 (permalink)  
Old March 5th, 2007, 12:36 AM
Registered User
 
Join Date: Mar 2007
Location: Karachi, Sindh, Pakistan.
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to kamrans74
Default

Thanks for reply!

I am using Access 2000 Database, I think your mean is that I store Recordset1 data into Temporary Table then getting sum from this table into Recordset2.

Please tell me that is possible can I Retrive sum data without store into temporary database.

Actually I wan Sum of Records itemcode wize from two Tables.
Table 1st is Purchase & 2nd is Sales.

Query syntax is :-

select itemcode, date, quantity, 'P' as status from purchase group by itemcode
union
select itemcode, date, quantity, 'S' as status from sales group by itemcode

This query will create recordset data like this:-
  ItemCode Date Quantity Status
    0001 01/03/2007 10 P
    0002 01/03/2007 20 P
    0003 01/03/2007 30 P
    0001 02/03/2007 5 S
    0002 02/03/2007 5 S
    0003 03/03/2007 10 S


Now I need a query which show data like this form in recordset2:-
  ItemCode Balance Quantity
    0001 5
    0002 15
    0003 20

Purchase quantiy minus with sales quantity then it is show balance quantity.

I think it is now clear what I want.






Reply With Quote
  #6 (permalink)  
Old March 5th, 2007, 04:17 PM
Friend of Wrox
 
Join Date: Nov 2004
Location: Port Orchard, WA, USA.
Posts: 1,621
Thanks: 1
Thanked 3 Times in 3 Posts
Default

You cannot sum directly from a recordset. You can iterate through the recordset, tallying the total, or you can Sum() the original data.
Perhaps you could do something like
Code:
SELECT SUM(quantity)
FROM   (SELECT quantity
        FROM   purchase
        UNION
        SELECT NZ(quantity, 0) * -1
        FROM   sales)
        to populate your second recordset.
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Clone DAO Recordset into ADO Recordset kamrans74 VB How-To 0 March 6th, 2007 11:57 AM
ADODB.Recordset (0x800A0CB3)Current Recordset does tks_muthu Classic ASP Databases 0 June 16th, 2005 07:22 AM
Convert ADO recordset to DAO recordset andrew_taft Access 1 May 5th, 2004 02:31 PM
code to copy a query recordset to a file Ivan Access VBA 9 October 31st, 2003 06:50 PM
query regarding recordset spraveens Classic ASP Databases 21 September 1st, 2003 06:35 AM



All times are GMT -4. The time now is 01:03 PM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.