Search code examples
sqlms-accessvb6

Move an MS Access query into a VB6 query that uses the first

I have two tables, one table called Act_Reg and the other is Active_Pay. I have two queries: one query is a view in MS Access and it gives me the result as Paycoach :

SELECT Act_Reg.member_id, Active_Pay.date_pay, Active_Pay.kind_sport, 
       Active_Pay.kind_prac, Active_Pay.coach, Active_Pay.tuition, Active_Pay.discount
FROM   Act_Reg 
INNER JOIN Active_Pay ON Act_Reg.member_id = Active_Pay.member_id;

The second query I use in VB6 for getting the result of query one:

rstemp1.Open "SELECT sum(tuition)-sum(discount) FROM paycoach where date_pay Between '" & Trim(txtdatein.text) & "' And '" & Trim(txtdateto.text) & "' and coach='" & Cbocoach.text & "' and kind_sport='" & cbosport.text & "' and kind_prac='normal' group by tuition", db, adOpenKeyset, adLockOptimistic

I want to calculate the payment for each coach, according to paid membership and discount in a date range. The two queries work well, but one query is in MS Access as view and the second is in VB6.

How can I combine these two queries into one query, which I can use in Visual Basic 6?

Solution

  • I think you can do just what you ask by doing exactly how you describe it. It's probably not the most efficient, but this should work by simply inserting your Access query SQL directly into your VB6 query SQL

    your paycheck query SQL

    SELECT Act_Reg.member_id, Active_Pay.date_pay, Active_Pay.kind_sport, 
       Active_Pay.kind_prac, Active_Pay.coach, Active_Pay.tuition, Active_Pay.discount
FROM   Act_Reg 
INNER JOIN Active_Pay ON Act_Reg.member_id = Active_Pay.member_id;

    Your rs.Open sql

    "SELECT sum(tuition)-sum(discount) FROM paycoach where date_pay Between '" & _
Trim(txtdatein.text) & "' And '" & Trim(txtdateto.text) & "' and coach='" & _
Cbocoach.text & "' and kind_sport='" & cbosport.text & "' and kind_prac='normal' group by tuition"

    Modify your rs.open to use Combined statement

    Dim sql as string

sql = "SELECT sum(tuition)-sum(discount) FROM "
sql = sql & "(SELECT Act_Reg.member_id, Active_Pay.date_pay, Active_Pay.kind_sport, "
sql = sql & "Active_Pay.kind_prac, Active_Pay.coach, Active_Pay.tuition, Active_Pay.discount "
sql = sql & "FROM Act_Reg INNER JOIN Active_Pay ON Act_Reg.member_id = Active_Pay.member_id) "
sql = sql & "As paycoach "
sql = sql & "where date_pay Between '"
sql = sql & Trim(txtdatein.text) & "' And '" & Trim(txtdateto.text) & "' and coach='"
sql = sql & Cbocoach.text & "' and kind_sport='" & cbosport.text & "' and kind_prac='normal' group by tuition"

rstemp1.Open sql, db, adOpenKeyset, adLockOptimistic