In one recordset and not the other

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have two recordsets drawing data from the same tables but for different
years. How can I set a third recordset which gives me all info in recordset
#1 but not in recordset #2? Very easy to do in queries but I need to program
this because there will be minor variations for different companies using the
same front end and I don't want to have to have a couple dozen queries
sitting in the front end.

Here's what I have:
(LY & TY are set as Long and defined by user input)

Set rsLastYear = MyDB.OpenRecordset("SELECT Company.CompanyID " & _
"FROM Company INNER JOIN Membership ON Company.CompanyID =
Membership.CompanyID " & _
"WHERE ((Membership.CurrentBillingYear) = " & LY & ");")
Set rsThisYear = MyDB.OpenRecordset("SELECT Company.CompanyID " & _
"FROM Company INNER JOIN Membership ON Company.CompanyID =
Membership.CompanyID " & _
"WHERE ((Membership.CurrentBillingYear) = " & TY & ");")

******

What would be the proper definition for the third recordset named
rsNotRenewed?

Thanks!
 
I find the easiest way to do this sort of thing is to create what's needed as
a real query, switch to SQL view and lift that for the OpenRecordset
statement.

You may already know this but another useful thing to do when not wanting to
use real queries is to create action queries in code as query definitions
without a name between the brackets - e.g. Set MyQry =
MyDB.CreateQueryDef(""), then set MyQry.SQL equal to the SQL statement (again
lifted from a real action query) before running it with MyQry.Execute. This
way the query never exists outside VBA/DAO.
 
Very easy to do in queries but I need to program
this because there will be minor variations for different companies
using the same front end and I don't want to have to have a couple
dozen queries sitting in the front end.
Click to expand...


select companyid
from company
where companyid in
(
select companyid
from membership
where currentbillingyear = [ThisYear]
)
and companyid not in
(
select companyid
from membership
where currentbillingyear = [LastYear]
)
order by companyid




Hope that helps



Tim F
 
Thanks to both Martin and Tim. I'm not familiar with setting query/table
defs - haven't quite achieved that level for vba, but it is now in my
personal lesson plan.

Tim Ferguson said:
Very easy to do in queries but I need to program
this because there will be minor variations for different companies
using the same front end and I don't want to have to have a couple
dozen queries sitting in the front end.
Click to expand...


select companyid
from company
where companyid in
(
select companyid
from membership
where currentbillingyear = [ThisYear]
)
and companyid not in
(
select companyid
from membership
where currentbillingyear = [LastYear]
)
order by companyid




Hope that helps



Tim F
Click to expand...
 
Back
Top