General information
Category:
Coding, syntax & commands
You have given an example of a sub SELECT statement. What I need must allow me to access two other pre defined queries which I can then access from a third query. In other words, imagine the first two queries are tables and the third query operates on those two tables. This is easily done in MS Access, VFP, and CBuilder. It should be possible in VB.
Example:
qTotalServices = "SELECT COUNT(tblService.iBatteryID) AS iServices, tblBattery.iBatteryID, tblBattery.cBattery, tblModel.cModel, tblManufacturer.cManufacturer, tblGroup.cGroup, tblSite.cSiteName, tblService.iBatteryStatus
FROM tblService, tblBattery, tblModel, tblManufacturer, tblGroup, tblSite, tblC7Message
WHERE tblBattery.iBatteryID = tblService.iBatteryID
AND
((tblModel.iModelID = tblBattery.iModelID) AND (tblManufacturer.iManufacturerID = tblModel.iManufacturerID))
AND
tblBattery.iGroupID = tblGroup.iGroupID
AND
tblBattery.iSiteID = tblSite.iSiteID
AND
tblService.iBatteryStatus = tblC7Message.iStatusCode
GROUP BY tblBattery.iBatteryID, tblBattery.cBattery, tblModel.cModel, tblManufacturer.cManufacturer, tblGroup.cGroup, tblSite.cSiteName, tblService.iBatteryStatus"
qTotalFailures = "SELECT Count(tblService.iBatteryID) AS iFailures, tblBattery.iBatteryID, tblModel.cModel, tblService.iBatteryStatus
FROM tblBattery, tblService, tblModel
WHERE tblBattery.iBatteryID=tblService.iBatteryID
AND tblModel.iModelID=tblBattery.iModelID
AND tblService.iDisplayStatus<>2
GROUP BY tblService.iBatteryID, tblBattery.iBatteryID, tblModel.cModel, tblService.iBatteryStatus"
qServiceHistory = "SELECT qTotalServices.cBattery, qTotalServices.iBatteryID, qTotalServices.iServices, qTotalFailures.iFailures, (qTotalFailures.iFailures/qTotalServices.iServices) AS PercentFail, qTotalServices.cGroup, qTotalServices.cSiteName, qTotalServices.cModel, qTotalServices.cManufacturer
FROM qTotalServices LEFT JOIN qTotalFailures ON qTotalServices.iBatteryID = qTotalFailures.iBatteryID"
Can I do the above in VB?
Thank you for your reply.
>>Can you provide a small example of one that uses two seperate queries to gererate the third.?
>>
>
>Here's an example:
>
>
>SELECT ProductName
>FROM Products
>WHERE UnitPrice =
> (SELECT UnitPrice
> FROM Products
> WHERE ProductName = 'Sir Rodney''s Scones')
Previous
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only