Combining Crosstab queries (1 Viewer)

[dkmoreland]

I have one crosstab query that generates these results:

Vendor Total Jobs Jan Feb Mar
Vendor A 1 1
Vendor B 12 6 6
Vendor C 7 2 2 3

Here's the SQL:

TRANSFORM Count([Subcontractor jobs].[Job #]) AS [CountOfJob #]
SELECT [Subcontractor jobs].[Vendor Name], Count([Subcontractor jobs].[Job #]) AS [Total Of Job #]
FROM [Subcontractor jobs]
GROUP BY [Subcontractor jobs].[Vendor Name]
PIVOT [Subcontractor jobs].[Month] In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

I have a second crosstab query that generates how many of the jobs shown above also have an NCR record. Not all Vendors will have NCR records but all will have job records.

Vendor Total NCRs Jan Feb Mar
Vendor A 1 1
Vendor B
Vendor C 4 3 1

SQL for 2nd query:

TRANSFORM Count([Subcontractor jobs with NCRs v2].[NCR#]) AS [CountOfNCR#]
SELECT [Subcontractor jobs with NCRs v2].[Vendor Name], Count([Subcontractor jobs with NCRs v2].[NCR#]) AS [Total Of NCR#]
FROM [Subcontractor jobs with NCRs v2]
GROUP BY [Subcontractor jobs with NCRs v2].[Vendor Name]
PIVOT [Subcontractor jobs with NCRs v2].Month In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

These queries work great separately. What I need to ask is for suggestions on how to combine them so I can show both total jobs and total NCRs, by month, for each vendor. I need to pull all this on a report but I figured it would be easier to write the query first that will be the report source.

Yes, I know the field names are poorly designed. This is an inherited system and I don't think I'll have time to change it before this contract ends.

Anyway, I would appreciate some advice on combining these queries, please and thank you.

 

[CJ_London]

for a horizontal view, create a new query, bring your two crosstabs in and join on vendor name. Select the fields you want

for a vertical view, use a union distinctrow query with perhaps an additional field to indicate which query the data came from

 

[dkmoreland]

for a horizontal view, create a new query, bring your two crosstabs in and join on vendor name. Select the fields you want

for a vertical view, use a union distinctrow query with perhaps an additional field to indicate which query the data came from

I tried the new query joined on vendor name - it only shows vendors that have ncrs. I need all the vendors, whether they have NCRS or not.

I've not done a union query before - what would the SQL look like?

 

[Pat Hartman]

Then use an outer join rather than an inner join. Put the controlling table on the left and the table that might not always be populated on the right. Draw the join line. Double click on the join line and choose the option that does what you want.

 

[isladogs]

Syntax for UNION query
SQLForFirstQuery UNION SQLForSecondQuery

Example:
SELECT PupilData.PupilID, PupilData.Surname, PupilData.Forename, PupilData.YearGroup, PupilData.TutorGroup,0 AS Leaver
FROM PupilData
ORDER BY PupilData.Surname, PupilData.Forename;
UNION
SELECT LeaversPupilData.PupilID, LeaversPupilData.Surname, LeaversPupilData.Forename, LeaversPupilData.YearGroup, LeaversPupilData.TutorGroup,-1 AS Leaver
FROM LeaversPupilData
ORDER BY LeaversPupilData.Surname, LeaversPupilData.Forename;

NOTE that you MUST have:
The same number of fields in each query in the same order with the same datatypes.
The names don't have to be identical - the query will use the field names from the first named query
You can add 'dummy fields' if necessary where you have gaps
You can only create a union query in SQL view (though you can join two or more queries created using the query designer)

 

[dkmoreland]

Syntax for UNION query
SQLForFirstQuery UNION SQLForSecondQuery

Example:
SELECT PupilData.PupilID, PupilData.Surname, PupilData.Forename, PupilData.YearGroup, PupilData.TutorGroup,0 AS Leaver
FROM PupilData
ORDER BY PupilData.Surname, PupilData.Forename;
UNION
SELECT LeaversPupilData.PupilID, LeaversPupilData.Surname, LeaversPupilData.Forename, LeaversPupilData.YearGroup, LeaversPupilData.TutorGroup,-1 AS Leaver
FROM LeaversPupilData
ORDER BY PupilData.Surname, PupilData.Forename;

NOTE that you MUST have:
The same number of fields in each query in the same order with the same datatypes.
The names don't have to be identical - the query will use the field names from the first named query
You can add 'dummy fields' if necessary where you have gaps
You can only create a union query in SQL view (though you can join two or more queries created using the query designer)

Cool - thanks. Do I put the PIVOT statement at the end of each query or just at the end of the second one?

 

[isladogs]

Cool - thanks. Do I put the PIVOT statement at the end of each query or just at the end of the second one?

If you are UNIONing 2 queries you copy them in their entirety
Having said that I've never joined 2 crosstabs together in any way (neither using UNION nor linked fields) and wonder what the performance will be like

Did you notice Pat's suggestion about using an OUTER join to link the two crosstabs?

 

[dkmoreland]

Yes - I was just about to say that Pat's suggestion of the outer join seems to do the trick. I think I need to customize some fields names but that shouldn't be a problem.

I am going to try a union query on the two crosstabs just to see what happens. Now I'm curious.

Thanks to you and Pat both for your help.

 

[Pat Hartman]

A union query stacks the rows of of two or more queries one on top of the other. A join matches a row in one table to a row in the other and so produces a horizontal result.

Whenever you join two tables/queries with identically named columns, you need to alias one or both sides.

@Colin,
Joining crosstabs is very useful when you want both a sum and a count for example. A crosstab can only pivot one column so one crosstab creates the sum and the second creates the count. Then you join the crosstabs to produce your report.

 

[isladogs]

Joining crosstabs is very useful when you want both a sum and a count for example. A crosstab can only pivot one column so one crosstab creates the sum and the second creates the count. Then you join the crosstabs to produce your report.

I'm struggling to find a practical use for that in crosstabs
I've done that plenty of times in a single aggregate query e.g.

SELECT Year([UsageDate]) & "-" & Month([UsageDate]) AS YearMonth, Sum(tblAccountUsage.UsageCount) AS SumOfUsageCount, Count(tblAccountUsage.UsageCount) AS CountOfUsageCount
FROM tblAccountUsage
GROUP BY Year([UsageDate]) & "-" & Month([UsageDate]);

 

[Pat Hartman]

How about a report of customer orders by month. But you don't want just the count of orders, you want the Average or Total amount as well?

 

[isladogs]

I would normally only do that for a single customer at a time

My problem is visualising how that would look in practice for multiple customers.
Do you have an example? Or is there one in Northwind perhaps?

 

[Pat Hartman]

It looks however you want it to look. The query that joins the the two queries orders the fields however you want them ordered. People tend to order the query fields in the same order they want them displayed when using a continuous view. So it might be something like:

Cust, JanSum, JanCount, FebSum, FebCount, etc.

The month fields in the individual queries are named Jan, Feb, Mar, etc so you alias them to differentate the sums from qry1 from the counts from qry2.