Variable column Crosstab query

[April15Hater]

Hi-

I am trying to run a regular tabular query (qtrJobSheet) that contains two crosstab queries (qxtFunctionPrice and qxtFunctionCost). Both crosstabs return matching records but whenever I run the tabular it it returns zero records where I know it should be returning several. The crosstabs to have a variable amount of columns and I think htat might be the culprit. I've searched around but don't know what exactly to search for. Any ideas guys?

Here is the SQL code for the tabular:

SELECT [tblClient].[EndCustomer] & " (" & [ClientLocation] & ")" AS ClientName, tblJob.JobName, tblFunction.[Function Name], qxtFunctionCost.*, qxtFunctionPrice.*
FROM (tblClient INNER JOIN tblJob ON tblClient.ClientID = tblJob.ClientID) INNER JOIN (qxtFunctionPrice INNER JOIN (qxtFunctionCost INNER JOIN tblFunction ON qxtFunctionCost.FunctionID = tblFunction.FunctionID) ON qxtFunctionPrice.FunctionID = tblFunction.FunctionID) ON tblJob.JobID = tblFunction.JobID;

Here is the SQL for qxtFunctionPrice:

TRANSFORM Sum(tblPriceFunction.FunctionPrice) AS SumOfFunctionPrice
SELECT tblFunction.FunctionID, tblFunction.JobID
FROM tblFunction INNER JOIN tblPriceFunction ON tblFunction.FunctionID = tblPriceFunction.FunctionID
GROUP BY tblFunction.FunctionID, tblFunction.JobID
PIVOT tblPriceFunction.FunctionType;

and the sql for qxtFunctionCost

TRANSFORM Sum(tblContractorFunction.FunctionCost) AS SumOfFunctionCost
SELECT tblFunction.FunctionID, tblFunction.JobID
FROM tblFunction INNER JOIN tblContractorFunction ON tblFunction.FunctionID = tblContractorFunction.FunctionID
WHERE (((tblFunction.JobID)=20))
GROUP BY tblFunction.FunctionID, tblFunction.JobID
PIVOT tblContractorFunction.FunctionType;

 

[namliam]

SELECT [tblClient].[EndCustomer] & " (" & [ClientLocation] & ")" AS ClientName,
      tblJob.JobName, 
      tblFunction.[Function Name], 
      qxtFunctionCost.*, 
      qxtFunctionPrice.*
FROM (tblClient 
INNER JOIN tblJob ON tblClient.ClientID = tblJob.ClientID) 
INNER JOIN (qxtFunctionPrice 
INNER JOIN (qxtFunctionCost 
INNER JOIN tblFunction ON qxtFunctionCost.FunctionID = tblFunction.FunctionID) 
              ON qxtFunctionPrice.FunctionID = tblFunction.FunctionID) 
              ON tblJob.JobID = tblFunction.JobID;

You are joining much more than just your queries, because it is all inner joins it is required that your Job Client and Function tables also having matching data.

Other than that your query seems to be correct in the sence that yes, if there is data it should return data, even with changing column headers from the pivots.

Try changing it all to (left) outerjoins to see which table/query is causing you problems.

 

[April15Hater]

Here's the new code:

SELECT tblFunction.[Function Name], qxtFunctionCost.*, qxtFunctionPrice.*
FROM qxtFunctionPrice RIGHT JOIN (qxtFunctionCost RIGHT JOIN tblFunction ON qxtFunctionCost.FunctionID = tblFunction.FunctionID) ON qxtFunctionPrice.FunctionID = tblFunction.FunctionID;

I was able to get some records to show by getting rid of two of the tables and right joining, but the two crosstab queries have similar columns so it is combining those columns.

My ultimate goal is to create a report: "Job Sheet" that basically gives a summary of the Job (names, address, etc which is stored in tblClient and tblJob) as well as the costs and prices for all functions that we perform for that job. I have the Costs(tblContractorFunction) and Prices(tblPriceFunction) seperated into two different tables and related through a separate function table(tblFunction). The problem is that the same functions are in both the price and cost tables (thus the queries.), so it is going to combine those columns. I hope that was clear, met me know if it wasn't.

Thanks!

 

[namliam]

OK, so you have your problem defined... You must have missing information in one of your additional tables.

If this query works to your satisfaction, you can add one table and see if that works or not. If you then have missing records, then that table is lacking.

Offcourse outerjoining everything should resolve that, but will return NULL information.

 

[April15Hater]

I finally got it. I actually had to do a few things to get it to work. I had the code filtering using a where clause in the crosstabs. I changed it and put the where clause in the tabular. I also rebuilt the tabular from scratch, and between those two it seems to work now.

These queries and reports are pretty tough. Designing the forms and putting the data in was way less challenging, lol! Thanks again for your help!

Joe

 

[namliam]

Hey if it were all easy, no need for us 'educated' people to get payed....

Glad you have it figured out

Have a merry weekend !