cssamerican
02-21-2008, 09:32 AM
I have two SQL queries, and what I want to do is query both queries to get the data I want. Is this possible or do I have to create temporary tables within the database?
If I do have to create temporary tables in the database that is fine, but I have a couple of questions about that. It is a SQL database provided by a third party and I can?t mess it up; therefore, I need to always check and make sure when I create my temp tables I am not overwriting a table they just put in, and when my query is done I need to remove the tables I created. Is this possible and if so how do I go about doing that?
Here are the two queries that I want to query to get the data I want:
strSQLJE = "SELECT Orders.StatusDate, Orders.JobNumber, Customers.CompanyName, Orders.AccountNo, Orders.QuotePrice " & vbNewLine & _
"FROM Orders " & vbNewLine & _
"INNER JOIN Customers " & vbNewLine & _
"ON Orders.CustomerID = Customers.CustomerID " & vbNewLine & _
"WHERE Orders.StatusID = '3' AND StatusDate BETWEEN '" & ReportForm.SDate.Value & "' AND '" & ReportForm.EDate.Value & "'"
strSQLTEST = "SELECT Orders.JobNumber, OrderStockDetail.NumImpressions, ResourceFPSDetail.ResourceClickCharge " & vbNewLine & _
"FROM Orders " & vbNewLine & _
"INNER JOIN OrderStockDetail " & vbNewLine & _
"ON Orders.OrderID = OrderStockDetail.OrderID " & vbNewLine & _
"LEFT JOIN ResourceFPSDetail " & vbNewLine & _
"ON OrderStockDetail.ResourceID = ResourceFPSDetail.ResourceID " & vbNewLine & _
"WHERE Orders.StatusID = '3' AND StatusDate BETWEEN '" & ReportForm.SDate.Value & "' AND '" & ReportForm.EDate.Value & "' AND OrderStockDetail.ResourceID <7"
I also have an issue in that I want a column created from the product of OrderStockDetail.NumImpressions and ResourceFPSDetail.ResourceClickCharge does anyone have any ideas on how to accomplish this?
If I do have to create temporary tables in the database that is fine, but I have a couple of questions about that. It is a SQL database provided by a third party and I can?t mess it up; therefore, I need to always check and make sure when I create my temp tables I am not overwriting a table they just put in, and when my query is done I need to remove the tables I created. Is this possible and if so how do I go about doing that?
Here are the two queries that I want to query to get the data I want:
strSQLJE = "SELECT Orders.StatusDate, Orders.JobNumber, Customers.CompanyName, Orders.AccountNo, Orders.QuotePrice " & vbNewLine & _
"FROM Orders " & vbNewLine & _
"INNER JOIN Customers " & vbNewLine & _
"ON Orders.CustomerID = Customers.CustomerID " & vbNewLine & _
"WHERE Orders.StatusID = '3' AND StatusDate BETWEEN '" & ReportForm.SDate.Value & "' AND '" & ReportForm.EDate.Value & "'"
strSQLTEST = "SELECT Orders.JobNumber, OrderStockDetail.NumImpressions, ResourceFPSDetail.ResourceClickCharge " & vbNewLine & _
"FROM Orders " & vbNewLine & _
"INNER JOIN OrderStockDetail " & vbNewLine & _
"ON Orders.OrderID = OrderStockDetail.OrderID " & vbNewLine & _
"LEFT JOIN ResourceFPSDetail " & vbNewLine & _
"ON OrderStockDetail.ResourceID = ResourceFPSDetail.ResourceID " & vbNewLine & _
"WHERE Orders.StatusID = '3' AND StatusDate BETWEEN '" & ReportForm.SDate.Value & "' AND '" & ReportForm.EDate.Value & "' AND OrderStockDetail.ResourceID <7"
I also have an issue in that I want a column created from the product of OrderStockDetail.NumImpressions and ResourceFPSDetail.ResourceClickCharge does anyone have any ideas on how to accomplish this?