PDA

View Full Version : Solved: Using multiple SQL Queries to get what I want



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?

RonMcK
02-21-2008, 10:27 AM
cssamerican,

I'll tackle your last question.


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?
Have you tried inserting 'OrderStockDetail.NumImpressions * ResourceFPSDetail.ResourceClickCharge' in the appropriate SELECT statement in the position that corresponds to your desired column position?

Have you tested your queries using MS SQL's Query Analyzer (or whatever they now call their ad hoc query tool)? For testing and creating one-off reports, I did this to create a result set; I manually copied/pasted the result set to MS Excel, using it to prepare my one-off reports.

Regards,

Ron
Orlando, FL

cssamerican
02-21-2008, 11:36 AM
That worked...
So this is what I have now for output:

Query 1
Date|Job Number|Department|Account Number|Full Amount

Query 2
Job Number|Partial Amount

There are some job numbers that will not have a partial amount in which case I want that amount to be 0.

The final report should look like this:

Date|Job Number|Department|Account Number||(Amount-Partial Amount)|Partial Amount|Amount

Anymore ideas on how to get that?

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, SUM(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" & vbNewLine & _
"GROUP BY Orders.JobNumber"

RonMcK
02-21-2008, 12:51 PM
cssamerican,

I'll look at some of my books at home to see if I can help you with your remaining questions. I have the suspicion that there is another join or two that are needed but I need to check the references.

Out of curiosity, did you ask your quesiton, here in Excel help rather than in SQL help for a particular reason?

later,

Ron
Orlando, FL

cssamerican
02-21-2008, 02:36 PM
cssamerican,
Out of curiosity, did you ask your quesiton, here in Excel help rather than in SQL help for a particular reason?

I asked in the SQL forum as well, but I got no replies. I had something like this come up before and I "cheated" by writing data to a worksheet then I queried the worksheet. In this case I would rather do it all in SQL; however, if the best answer is to output the first recordset to the worksheet and the step through the second recordset comparing on Job Number before writing the necessary data onto the worksheet, then so be it. If that is the best answer it is more likely someone here would tell me that and give me an explaination about how you do that.

This forum gets a lot more traffic than the SQL forum as well, so you get more ideas for problems when your stumped.

cssamerican
02-22-2008, 12:56 PM
It doesn't look like anyone here knows how to do it in SQL, maybe it isn't possible. Does any one know how to step through a recordset while writing it to the sheet so I can do something like I described in my previous post?

RonMcK
02-23-2008, 01:43 PM
cssamerican,

Can you make a View that Unions your two queries together, giving you your desired final result?

Something on the order of:


CREATE VIEW rptname (fld1, fld2, ... fldn)
AS SELECT (your first select)
FROM ...
GROUP BY ... )
Union
(SELECT (your second select)
FROM ...
GROUP BY ... ));
I found this discussed in Joe Celko's SQL for Smarties: Advanced SQL Programming Second Edition, Morgan Kaufmann, 2000.


Regards,

Ron
Orlando, FL

cssamerican
02-25-2008, 10:50 AM
It was solved in the SQL forum..
http://vbaexpress.com/forum/showthread.php?t=17869

Thanks for your help!

RonMcK
02-25-2008, 12:54 PM
Thanks for the reference to your solution.

Ron
Orlando, FL