How to Query the data from Recordset 1

[muraliambt]

Hi All,

Is it possible to query the data from recordset using the SELECT Sql statement.

Thanks in advance,
Muraliambt.

 

[Sheco]

Yes, but you have to really want it.

 

[muraliambt]

Hi Sheco,

Yes, i really wanted.

Muraliambt.

 

[Sheco]

One thing that can be confusing when you first start using ADO is that there are several ways to populate a recordset object.

Do you have the ADO connection object opened and working?

Show us the code that you have so far.

 

[muraliambt]

Hi,

Yes, i have ADO Connection and working fine. This is my requirement.

I am querying data from 3 tables and putting it in temporary ADO record set using UNION ALL. From the recordset i want to SUM records based on the date and insert this into table.

I'm getting struck in the second part(SUM) the recordset.

Rgds,
Muraliambt.

 

[HarleyQuinn]

Can you do it in your original query before you populate the recordset?

Harleyquinn

---------------------------------
For tsunami relief donations

 

[muraliambt]

Hi,

It is not possible, because i have group by based on the date, same date may appear in 3 tables also.

May be other way out, but i don't know how to do using the SQL query.

Muraliambt.

 

[HarleyQuinn]

I've got to agree with Sheco now then, could you show us your code (and SQL query) so we can take a look at them??

Cheers

Harleyquinn

---------------------------------
For tsunami relief donations

 

[muraliambt]

I am using VB 6.0 AND ACCESS Database.

Here is My Query.

Dim ssql1 As String, ssql2 As String, ssql3 As String
Dim ssql4 As String, ssql5 As String, ssql6 As String
Dim trs As New ADODB.Recordset
Dim sSql As String

Dim FromDate As Date
Dim ToDate As Date

FromDate = #1/7/2004#
ToDate = Date

Dim rsTemp As New ADODB.Recordset
Dim rsPrefifo As New ADODB.Recordset

' Cash Voucher Local
ssql1 = "select pr_code, upd_mth, voudt, sum(credit - debit) as loc_conv, "
ssql1 = ssql1 & " sum((credit - debit) / exch_rate) as usd_conv "
ssql1 = ssql1 & " from cash_dt "
ssql1 = ssql1 & " where trim(gl_code) = '1210' "
ssql1 = ssql1 & " and upd_mth >= #" & FromDate & "# "
ssql1 = ssql1 & " and upd_mth <= #" & ToDate & "# "
ssql1 = ssql1 & " and cur_code = '" & sBaseCurrency & "' "
ssql1 = ssql1 & " group by pr_code, upd_mth, voudt "
ssql1 = ssql1 & " order by pr_code, upd_mth, voudt "

' Cash Voucher USD
ssql2 = "select pr_code, upd_mth, voudt, sum((credit - debit) * exch_rate) as loc_conv, "
ssql2 = ssql2 & " sum(credit - debit) as usd_conv "
ssql2 = ssql2 & " from cash_dt "
ssql2 = ssql2 & " where trim(gl_code) = '1210' "
ssql2 = ssql2 & " and upd_mth >= #" & FromDate & "# "
ssql2 = ssql2 & " and upd_mth <= #" & ToDate & "# "
ssql2 = ssql2 & " and cur_code = '" & ForeignCurrency & "' "
ssql2 = ssql2 & " group by pr_code, upd_mth, voudt "
ssql2 = ssql2 & " order by pr_code, upd_mth, voudt "

' Bank Voucher Local
ssql3 = "select pr_code, upd_mth, voudt, sum(credit - debit) as loc_conv, "
ssql3 = ssql3 & " sum((credit - debit) / exch_rate) as usd_conv "
ssql3 = ssql3 & " from bank_dt "
ssql3 = ssql3 & " where trim(gl_code) = '1210' "
ssql3 = ssql3 & " and upd_mth >= #" & FromDate & "# "
ssql3 = ssql3 & " and upd_mth <= #" & ToDate & "# "
ssql3 = ssql3 & " and cur_code = '" & sBaseCurrency & "' "
ssql3 = ssql3 & " group by pr_code, upd_mth, voudt "
ssql3 = ssql3 & " order by pr_code, upd_mth, voudt "

' Bank Voucher USD
ssql4 = "select pr_code, upd_mth, voudt, sum((credit - debit) * exch_rate) as loc_conv, "
ssql4 = ssql4 & " sum(credit - debit) as usd_conv "
ssql4 = ssql4 & " from bank_dt "
ssql4 = ssql4 & " where trim(gl_code) = '1210' "
ssql4 = ssql4 & " and upd_mth >= #" & FromDate & "# "
ssql4 = ssql4 & " and upd_mth <= #" & ToDate & "# "
ssql4 = ssql4 & " and cur_code = '" & ForeignCurrency & "' "
ssql4 = ssql4 & " group by pr_code, upd_mth, voudt "
ssql4 = ssql4 & " order by pr_code, upd_mth, voudt "

' JV Voucher Local
ssql5 = "select pr_code, upd_mth, voudt, sum(credit - debit) as loc_conv, "
ssql5 = ssql5 & " sum((credit - debit) / exch_rate) as usd_conv "
ssql5 = ssql5 & " from jv_dt "
ssql5 = ssql5 & " where trim(gl_code) = '1210' "
ssql5 = ssql5 & " and upd_mth >= #" & FromDate & "# "
ssql5 = ssql5 & " and upd_mth <= #" & ToDate & "# "
ssql5 = ssql5 & " and cur_code = '" & sBaseCurrency & "' "
ssql5 = ssql5 & " group by pr_code, upd_mth, voudt "
ssql5 = ssql5 & " order by pr_code, upd_mth, voudt "

' JV Voucher USD
ssql6 = "select pr_code, upd_mth, voudt, sum((credit - debit) * exch_rate) as loc_conv, "
ssql6 = ssql6 & " sum(credit - debit) as usd_conv "
ssql6 = ssql6 & " from jv_dt "
ssql6 = ssql6 & " where trim(gl_code) = '1210' "
ssql6 = ssql6 & " and upd_mth >= #" & FromDate & "# "
ssql6 = ssql6 & " and upd_mth <= #" & ToDate & "# "
ssql6 = ssql6 & " and cur_code = '" & ForeignCurrency & "' "
ssql6 = ssql6 & " group by pr_code, upd_mth, voudt "
ssql6 = ssql6 & " order by pr_code, upd_mth, voudt "

sSql = sSql & ssql1 & " union all "
sSql = sSql & ssql2 & " union all "
sSql = sSql & ssql3 & " union all "
sSql = sSql & ssql4 & " union all "
sSql = sSql & ssql5 & " union all "
sSql = sSql & ssql6

rsTemp.Open sSql, sDBconn, adOpenStatic, adLockBatchOptimistic

sSql = " select pr_code, upd_mth, voudt, sum(tsh_conv) as amt,"
sSql = sSql & " sum(usd_conv) as usd_amt "
sSql = sSql & " from " & rsTemp & ""
sSql = sSql & " group by pr_code, upd_mth, voudt "
sSql = sSql & " order by pr_code, upd_mth, voudt "

rsPrefifpen sSql, sDBconn, adOpenStatic, adLockBatchOptimistic


From the above code, i am getting compile error "type mismatch " where marked in red

Suggest me if there is any other way of doing it.

Muraliambt.

 

[HarleyQuinn]

I can't test this but this should work:

sSql = sSql & ssql1 & " union all "
sSql = sSql & ssql2 & " union all "
sSql = sSql & ssql3 & " union all "
sSql = sSql & ssql4 & " union all "
sSql = sSql & ssql5 & " union all "
sSql = sSql & ssql6

sSqlFinal = " select pr_code, upd_mth, voudt, sum(tsh_conv) as amt,"
sSqlFinal = sSqlFinal & " sum(usd_conv) as usd_amt "
sSqlFinal = sSqlFinal & " from (" & sSql & ")" 
sSqlFinal = sSqlFinal & " group by pr_code, upd_mth, voudt "
sSqlFinal = sSqlFinal & " order by pr_code, upd_mth, voudt "

rsTemp.Open sSql, sDBconn, adOpenStatic, adLockBatchOptimistic

Hope this helps


Harleyquinn

---------------------------------
For tsunami relief donations

 

[muraliambt]

Hi Harley,

Thanks a lot, it works fine.

Is there any other short way of doing this instead of writing such a big sql query.

Muraliambt.

 

[HarleyQuinn]

I can't think of one off the top of my head but I'll have a think and get back to you if I come up with anything.

Cheers

Harleyquinn

---------------------------------
For tsunami relief donations

 

[muraliambt]

That's fine, still u didn't tell me how to query recordset.


Muraliambt.