Combine sql queries so that they show up on the vb6 datagrid

How can I combine these two sql queries so that they both show up on the datagrid in vb6?  I want the first query to be combined with the bottom code.


    esql = "select H.Agency,H.RegID,R.LastName,R.FirstName, H.ActivityID,A.ActivityName,H.Narrative, H.ActivityDate, H.Program, H.Hours, ltrim(right(convert(varchar(25), H.HourTimeFrom, 100), 7)) as 'Time From', " & _
        "ltrim(right(convert(varchar(25), H.HourTimeTo, 100), 7)) as 'Time To', H.SubGroup,H.Area, H.ParticipantHour, H.VolunteerHour, H.Fiscal, H.EntryTime " & _
        "from tblOrgHours H " & _
        "JOIN tblOrgRegistrations R on H.RegID = R.RegID " & _
        "JOIN tblOrgActivities A on H.ActivityID = A.ActivityID " & _
        "Where H.AgencyID = '" & AgencyID & "' And H.ActivityDate >= '" & DTPicker1 & "' And H.ActivityDate <= '" & DTPicker2 & "'" & " " & String1 & " order by r.LastName,r.FirstName, H.Program, A.ActivityName, H.ActivityDate,H.HourTimeFrom"
        

Open in new window


esql = "select AgencyID from tblOrgProfile where [System Name] ='" & Combo1 & "'" 

If rec.State = adStateOpen Then
        rec.Close
End If

      rec.CursorType = adOpenStatic
      rec.CursorLocation = adUseClient
      rec.LockType = adLockOptimistic
      rec.Open esql, conn, , , adCmdText

Text1 = rec.RecordCount

If rec.EOF And rec.BOF Then
    Set DataGrid1.DataSource = Nothing
    MsgBox "There are no Records for this Query. ", vbOKOnly, "No Data Found"
    Exit Sub
    
End If

      
        Set DataGrid1.DataSource = rec
        
        
    rec.MoveFirst
    

Open in new window

al4629740Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
Depends on what "combining" means.. In SQL (btw, what dialect) this is done by using the UNION ALL operator:

SELECT * FROM Query1
UNION ALL
SELECT * FROM Query2;

Open in new window

or

SELECT columnList FROM Query1
UNION ALL
SELECT columnList FROM Query2;

Open in new window


Precondition: the number of columns must be equal. The data type of the columns must be implicitly castable (by position of column in the column list).

So, as your second query is returning only one column (AgencyID) and your first multiple ones, this question makes not really sense.

Another way of "combining" would be a simple JOIN. E.g.

SELECT   H.Agency ,
         H.RegID ,
         R.LastName ,
         R.FirstName ,
         H.ActivityID ,
         A.ActivityName ,
         H.Narrative ,
         H.ActivityDate ,
         H.Program ,
         H.Hours ,
         LTRIM(RIGHT(CONVERT(VARCHAR(25), H.HourTimeFrom, 100), 7)) AS [Time From] ,
         LTRIM(RIGHT(CONVERT(VARCHAR(25), H.HourTimeTo, 100), 7)) AS [Time To] ,
         H.SubGroup ,
         H.Area ,
         H.ParticipantHour ,
         H.VolunteerHour ,
         H.Fiscal ,
         H.EntryTime ,
         CASE WHEN P.AgencyID IS NULL THEN 'No Data Found'
              ELSE 'Data Found'
         END AS HasAgency
FROM     tblOrgHours H
         INNER JOIN tblOrgRegistrations R ON H.RegID = R.RegID
         INNER JOIN tblOrgActivities A ON H.ActivityID = A.ActivityID
         LEFT JOIN tblOrgProfile P ON P.AgencyID = H.AgencyID
--WHERE	H.AgencyID = '" & AgencyID & "' 
--	AND H.ActivityDate >= '" & DTPicker1 & "' 
--	AND H.ActivityDate <= '" & DTPicker2 & "'" & " " & String1 & " 
ORDER BY R.LastName ,
         R.FirstName ,
         H.Program ,
         A.ActivityName ,
         H.ActivityDate ,
         H.HourTimeFrom;

Open in new window


But this is just a guess..
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
al4629740Author Commented:
Combining means no use of join but rather combine yet separate.   So that two record sets can be seen from the data grid
0
Fabrice LambertFabrice LambertCommented:
With a union query, you can use explicit null columns in the case queries don't have the same number of columns:
SELECT Col1, Col2, Col3
FROM MyFirstTable
UNION ALL
SELECT Col1, Null, Null
FROM MySecondTable;

Open in new window

0
al4629740Author Commented:
Would it be possible for someone to demonstrate using the columns and table structure that I sent.  I took time to post those for that reason.


Thanks
0
Fabrice LambertFabrice LambertCommented:
Something like this ?
esql = vbNullString
esql = esql  & "SELECT	H.Agency,		H.RegID,	R.LastName," & vbCrLf
esql = esql & "		R.FirstName, 		H.ActivityID,	A.ActivityName," & vbCrLf
esql = esql & "		H.Narrative, 		H.ActivityDate, H.Program," & vbCrLf
esql = esql & "		H.Hours," & vbCrLf
esql = esql & " 	ltrim(right(convert(varchar(25), H.HourTimeFrom, 100), 7)) as 'Time From'," & vbCrLf
esql = esql & "		ltrim(right(convert(varchar(25), H.HourTimeTo, 100), 7)) as 'Time To'," & vbCrLf
esql = esql & "		H.SubGroup,H.Area, 	H.ParticipantHour, 	H.VolunteerHour," & vbCrLf
esql = esql & "		H.Fiscal, 		H.EntryTime" & vbcrlf
esql = esql & "FROM	(    tblOrgHours H JOIN tblOrgRegistrations R" & vbCrLf
esql = esql & "			    on H.RegID = R.RegID)" & vbCrLf
esql = esql & "		JOIN tblOrgActivities A" & vbcrlf
esql = esql & "			on H.ActivityID = A.ActivityID" & vbCrLf
esql = esql & "WHERE H.AgencyID = '" & AgencyID & "'" & vbCrLf
esql = esql & "  AND H.ActivityDate >= '" & DTPicker1 & "'" & VbCrLf
esql = esql & "  AND H.ActivityDate <= '" & DTPicker2 & "'" & vbCrLf
esql = esql & String1 & vbCrLf
esql = esql & "ORDER BY	r.LastName,	r.FirstName,	H.Program," & VbCrLf
esql = esql & "		A.ActivityName, H.ActivityDate,	H.HourTimeFrom" & vbCrLf
esql = esql & "UNION ALL" & vbCrLf
esql = esql & "SELECT	AgencyID,		Null,	Null," & vbCrLf
esql = esql & "		Null,			Null,	Null," & vbCrLf
esql = esql & "		Null,			Null,	Null," & vbCrLf
esql = esql & "		Null," & vbCrLf
esql = esql & "		Null," & vbCrLf
esql = esql & "		Null," & vbCrLf
esql = esql & "		Null,			Null,	Null," & vbCrLf
esql = esql & "		Null,			Null,	Null," & vbCrLf
esql = esql & "		Null,			Null,	Null," & vbCrLf
esql = esql & "		Null,			Null" & vbCrLf
esql = esql & "FROM 	tblOrgProfile" & vbCrLf
esql = esql & "WHERE	[System Name] = '" & Combo1 & "';"

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.