• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 35
  • Last Modified:

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

0
al4629740
Asked:
al4629740
  • 2
  • 2
2 Solutions
 
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
 
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

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now