Manipulating Recordsets

[gazza]

I have a requirement to create two recordsets from with VB6 - no problems here, but I then need to compare records within the two recordsets (there are two common fields in each recordset) and either create a new recordset or change one of the existing recordsets.

Is this possible - if so how do I do it.

[gazza]

Maybe my initial request was a bit untidy. I have this very complex query that I can only achieve by performing a comparison of two recordsets within the VB code.

My problem is that I need the result to be a recordset also. ( I use this recordset for a crystal report). I have so far performed the comparison and saved the data to a temporary table and fields within the database and then used this recordset for crystal.

I believe this is a very clumsy way of doing the task and would prefer not to use my database for temporary data storage.

Hopefully I have explained a little better. So - is there a way I can compare two recordsets and create another recordset or something. Any guidance will be appreciated.

GRS

[Wokawidget]

What is the query for the 2 recordsets and what is the conditions for creating the new recordset?

Woka

[gazza]

Thanks Woka. I am finding it very hard to make any progress here as my experience with SQL statements is limited. You gave some assistance on a previous thread, but use of the UNION ALL statement did not help.

I have a database (access2000) with several tables but the three I am interested in are:

Employees - with fields: EmployID, lastname, firstname,
Occupation1, Occupation2 etc (others not used for this problem)

InductionsOps - with fields: OperationID, OpInductCounter, OpInductDue (OperationID links to another table that provides name but I do not need names)

InductionsSites - with fields: SiteID, OpInductCounter, SiteInductDue (Similar to the table above siteID references a name table that is not required. OPinductCounter is a link between the two tables InductionsOp and InductionsSite - Sites are like a child of operations and the opInductcounter tells me what Operation (parent) the sites (children) belong to.

I need to find out lastname and firstnames of people who have have been inducted on a selected operation (loperationID) and a selected site (lSiteID). Access will not allow me to combine the query because of ambiguous outer joins. I tried UNION ALL between the two but that does not give me correct results so i run two queries as follows:

OpnSQL = "SELECT Employee.LastName, Employee.FirstName,"
OpnSQL = OpnSQL & " InductionsOps.OperationID, InductionsOps.OpInductCounter,"
OpnSQL = OpnSQL & " InductionsOps.OPInductDue FROM Employee INNER JOIN"
OpnSQL = OpnSQL & " InductionsOps ON Employee.EmployID = InductionsOps.EmployID"
OpnSQL = OpnSQL & " WHERE (InductionsOps.OperationID)= " & lOperationID

Set rsOpnSearch = dbEmployee.OpenRecordset(OpnSQL)

I then use the result from this query to create a string (sopCount) which contains a list of OpInductCounters (eg (InductionsSites.OpInductCounter = 5) or (InductionsSites.OpInductCounter = 7))

SiteSQL = "SELECT Employee.LastName, Employee.FirstName, Employee.ReportHide,"
SiteSQL = SiteSQL & " InductionsSites.SiteID, InductionsSites.OPInductCounter,"
SiteSQL = SiteSQL & " InductionsSites.SiteInductDue FROM Employee INNER JOIN"
SiteSQL = SiteSQL & " InductionsSites ON Employee.EmployID = InductionsSites.EmployID"
SiteSQL = SiteSQL & " WHERE (Employee.ReportHide = 0) AND (InductionsSites.SiteID)= " & lSiteID
SiteSQL = SiteSQL & " AND " & sOpCount

Set rsSiteSearch = dbEmployee.OpenRecordset(SiteSQL)

This all works and I get a list of names who have completed the selected Operation and site inductions.

Now I need to figure in what Occupations they are

OccSQL = "SELECT DISTINCTROW Employee.LastName, Employee.FirstName,"
OccSQL = OccSQL & " Employee.OccupationID1, Employee.OccupationID2,"
OccSQL = OccSQL & " Employee.OccupationID3 From Employee"
If lOccupationID = 0 Then
OccSQL = OccSQL & " WHERE (Employee.OccupationID1)> 1 "
Else
OccSQL = OccSQL & " WHERE (Employee.OccupationID1)= " & lOccupationID
End If
If lOccupationID = 0 Then
OccSQL = OccSQL & " OR (Employee.OccupationID2)> 1 "
Else
OccSQL = OccSQL & " OR (Employee.OccupationID2)= " & lOccupationID
End If

Set rsOccSearch = dbEmployee.OpenRecordset(OccSQL)

Again this works and I have a list of names that have selected occupations.

Problem: I now need a recordset that contains common names between the two recordsets.
Appreciate your assistance.

[Wokawidget]

SELECT Employee.LastName, Employee.FirstName
FROM Employee
INNER JOIN InductionsSites 
ON InductionsSites.EmployID = Employee.EmployID "
AND InductionsSites.SiteID = 3
INNER JOIN InductionsOps 
ON InductionsOps.OpInductCounter = InductionsSites.OpInductCounter
AND InductionsOps.OperationID = 3

Hope that helps...