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.

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

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

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

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.

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         to populate your second recordset.