Problem with complex query on Crystal Report

[RZillmer]

I have two queries. One groups all line items on a sales order and sums them...in the end giving me a list of sales orders and their total costs. My other query gathers information from a bunch of tables. I want to link these two queries together by sales order number on my crystal report. I've tried many ways, but have yet to figure out how to get two queries on to one Crystal report. It lets me select the first, but not the second. I tried combining the two queries in the Crystal SQL Designer and can't figure out how to do it there, either. I also can't think of how I could write the SQL to make this into one huge query.

I don't know if it matters, but all my data is coming from an ODBC connection...which I know is working. I can see the data, I just can't get both queries in the same place at the same time. I've already done this report in Access so I figure Crystal MUST be able to do it.

Here are my two queries...don't know if anyone will want to see them:

SELECT 
  JT_H7HistoryWorkTickets."SalesOrderNumber", 
  JT_H7HistoryWorkTickets."WTNumber", 
  SUM (JT_H7HistoryWorkTickets."LaborCost"), 
  SUM (JT_H7HistoryWorkTickets."PartsCost")
FROM 
  "JT_H7HistoryWorkTickets" JT_H7HistoryWorkTickets
GROUP BY 
  JT_H7HistoryWorkTickets."SalesOrderNumber", 
  JT_H7HistoryWorkTickets."WTNumber"
ORDER BY 
  JT_H7HistoryWorkTickets."SalesOrderNumber" ASC , 
  JT_H7HistoryWorkTickets."WTNumber" ASC

SELECT 
  ARN_InvHistoryHeader."SOTaxableSalesAmt", 
  ARN_InvHistoryHeader."SONonTaxableSalesAmt", 
  ARN_InvHistoryHeader."SOFrghtAmount", 
  ARN_InvHistoryHeader."WTClass", 
  ARO_InvHistoryDetail."SOItemNumber", 
  ARO_InvHistoryDetail."WTParent", 
  ARO_InvHistoryDetail."WTNumber", 
  ARN_InvHistoryHeader."SOOrderNoSort", 
  ARN_InvHistoryHeader."CustomerNumber", 
  ARN_InvHistoryHeader."InvoiceDate", 
  ARN_InvHistoryHeader."CustomerNameSort"
FROM 
  "ARN_InvHistoryHeader" ARN_InvHistoryHeader, 
  "ARO_InvHistoryDetail" ARO_InvHistoryDetail
WHERE 
  ARN_InvHistoryHeader."InvoiceNumber" = ARO_InvHistoryDetail."InvoiceNumber" AND 
  ARN_InvHistoryHeader."HeaderSeqNumber" = ARO_InvHistoryDetail."HeaderSeqNumber" AND 
  ARN_InvHistoryHeader."WTClass" <> 'STK' AND 
  ARO_InvHistoryDetail."WTParent" = 'Y'

Thanks in advance for any help!

 

[MColeman]

If your database is SQL, you could use a stored procedure.

Or, you could put the second data group in a subreport.

 

[RZillmer]

My database isn't SQL. We're using some MRP system that has a proprietary database system...so I'm pretty much stuck with what they give me.

I have made some progress, though, using subreports like you suggested. It seems like I have all the data and have linked the report and subreport correctly.

Now my problem is calculations. If you look at the two queries above, you'll notice I'm getting a majority of info and the sales amount from one. The other is the cost of labor and parts. My cost query is my subreport. Now I have to figure out how to calculate things like gross profit (sales - cost) and gross profit% ((sales-cost)/sales). I also need to total all these things per item number.

Thanks very much for your help. If it helps to explain what I'm trying to accomplish, I'll upload a screen cap of my Access report's output to some webspace for viewing.