dkmoreland
Registered User.
- Local time
- Yesterday, 17:32
- Joined
- Dec 6, 2017
- Messages
- 129
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:
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:
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.
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:
Code:
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:
Code:
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.