I have a database with several tables: [Plan Revenue], [Plan Revenue Expense], [Support Provider], [Z Service Codes New]
I've already got a query written that will give me a count of how many expense records are listed for each provider in specific Service Codes over the lat 6 mos. Only problem is, ideally, I'd like it to also list 0's for service codes in which NO EXPENSES have been logged over the last 6 mos.
So, basically, I want to cross the [Z Service Codes New] table with the [Support Providers] table to get every possible matchup between a provider and all service codes. Then, within each service code, I want it to count the number of expense records within the last 6 mos (well, I've actually got it set up with Custom Dates so it's more flexible).
But I'm getting stuck on how to do this so that it's not matching up ALL expense records with ALL service codes, but rather only those expense records falling within the correct service code, if any.
[Plan Revenue].[Service] is a foreign key that matches up to [Z Service Codes New].[Service Code] [Plan Revenue].[Rev ID] is a foreign key that matches up to [Plan Revenue Expense].[RevID] [Plan Revenue Expense].[SP] is a foreign key that matches up to [Support Provider].[ID]
Don't ask me why the database builder didn't make their keys match up from on table to the next for simplicity's sake. It just is what it is...
My current query looks about like this:
SELECT [support provider].[sp last],
[support provider].[sp first],
[support provider].[sp mi],
[support provider].[agency],
[support provider].[de?],
[support provider].[ic?],
[support provider].[agency?] AS [AG?],
[support provider].[generalbus?] AS [GB?],
[ExpsToCount].[counted svc],
Iif(Count([ExpsToCount].[exptocountid]) = 0, 0, Count(
[ExpsToCount].[exptocountid])) AS ExpCount
FROM [support provider]
LEFT JOIN (SELECT [plan revenue expense].[sp] AS [Counted SP],
[z service codes new].[service code] AS [Counted Svc],
[plan revenue expense].[exp id] AS [ExpToCountID]
FROM [z service codes new]
LEFT JOIN ([plan revenue]
LEFT JOIN [plan revenue expense]
ON [plan revenue].[rev id] =
[plan revenue expense].[revid])
ON [z service codes new].[service code] =
[plan revenue].[service]
WHERE ( [plan revenue expense].[checkdt] >=
Format([period beginning "mm/dd/yyyy"], "short date")
AND [plan revenue expense].[checkdt] <=
Format([periond ending "mm/dd/yyyy"], "short date") ))
AS ExpsToCount
ON [ExpsToCount].[counted sp] = [support provider].[id]
WHERE [support provider].[inactive?] = false
AND ( [support provider].[de?] = true
OR [support provider].[ic?] = true )
GROUP BY [support provider].[sp last],
[support provider].[sp first],
[support provider].[sp mi],
[support provider].[agency],
[support provider].[de?],
[support provider].[ic?],
[support provider].[agency?],
[support provider].[generalbus?],
[ExpsToCount].[counted svc]
ORDER BY [ExpsToCount].[counted svc],
[support provider].[agency],
[support provider].[sp last],
[support provider].[sp first],
[support provider].[sp mi],
[ExpsToCount].[counted svc];
[IC?], [DE?], [Agency?], [GeneralBus?] are just boolean fields denoting the type of Support provider.
Again, why the database guy chose this rather than a single field with a foreign key so you could only be ONE type of provider, I don't know... Just roll with it.
So, I'm currently using a sub-query, too.
But, it's not giving me zeroes for services codes no expenses are associated with. It's only giving me counts of ACTUAL expenses, which would be fine if I didn't care about the 0's, which I kind of do... If only for my own edification.
I feel like I've been going rounds with this thing for the last day or so & my brain is friend. Need a second set of eyes.
I feel like I need to cross-join [Support Provider] with [Z Service Codes New] in order to get all the possible matchups and then somehow selectively either give a 0 if no associated expense records are there or a COUNT if there are expense records. But I want ever single service code for every single Support Provider, regardless.
Not sure what the syntax is for combining a CROSS JOIN with an INNER JOIN or OUTER JOIN of some sort in Access? And what the best combination would be for my purposes. Can I get away without a sub-query somehow, or do I still need that too? Is there some better way of doing this entirely vs. CROSS JOIN? (I THINK that's kind of what CROSS JOIN was designed for... But I could be wrong?)
Hope that all makes sense. Thoughts? Oh, and I'm on Access 2003 Professional SP3.
Edit:
Okay, and my own elegant solution (well, fine, I don't know whether it's elegant, but it works!) is to make two custom SELECT statements in my main FROM line, then JOIN the two SELECT statements based on two matching criteria:
SELECT [ProvidersXCodes].[SP Last], [ProvidersXCodes].[SP First], [ProvidersXCodes].[SP MI], [ProvidersXCodes].[Agency], [ProvidersXCodes].[SvcCode], IIf(COUNT([ExpsToCount].[ExpCountID])=0,0,COUNT([ExpsToCount].[ExpCountID])) AS [Exp Count]
FROM (SELECT [Support Provider].[ID] AS [CodeProvider], [Support Provider].[SP Last], [Support Provider].[SP First], [Support Provider].[SP MI], [Support Provider].[Agency], [Z Service Codes New].[Service Code] AS [SvcCode]
FROM [Support Provider], [Z Service Codes New]
WHERE [Support Provider].[Inactive?]=FALSE
AND ([Support Provider].[DE?]=TRUE OR [Support Provider].[IC?]=TRUE)
AND NOT ([Z Service Codes New].[Service Code] LIKE '111*'
OR [Z Service Codes New].[Service Code] LIKE '222*'
OR [Z Service Codes New].[Service Code] LIKE '333*')
GROUP BY [Support Provider].[ID], [Support Provider].[SP Last], [Support Provider].[SP First], [Support Provider].[SP MI], [Support Provider].[Agency], [Z Service Codes New].[Service Code]) AS [ProvidersXCodes]
LEFT JOIN (SELECT [Support Provider].[ID] AS [ExpCountProv], [Plan Revenue].[Service] AS [ExpCountSvc], [Plan Revenue Expense].[Exp ID] AS [ExpCountID]
FROM [Plan Revenue], [Plan Revenue Expense], [Support Provider]
WHERE [Plan Revenue].[Rev ID]=[Plan Revenue Expense].[RevID]
AND [Plan Revenue Expense].[SP]=[Support Provider].[ID]
AND [Support Provider].[Inactive?]=FALSE
AND ([Support Provider].[DE?]=TRUE OR [Support Provider].[IC?]=TRUE)
AND ([Plan Revenue Expense].[CheckDt]>=Format([Period Beginning "MM/DD/YYYY"],"Short Date") AND [Plan Revenue Expense].[CheckDt]<=Format([Periond Ending "MM/DD/YYYY"],"Short Date"))
GROUP BY [Support Provider].[ID], [Plan Revenue].[Service], [Plan Revenue Expense].[Exp ID]) AS [ExpsToCount] ON ([ProvidersXCodes].[CodeProvider]=[ExpsToCount].[ExpCountProv] AND [ProvidersXCodes].[SvcCode]=[ExpsToCount].[ExpCountSvc])
GROUP BY [ProvidersXCodes].[SP Last], [ProvidersXCodes].[SP First], [ProvidersXCodes].[SP MI], [ProvidersXCodes].[Agency], [ProvidersXCodes].[SvcCode]
ORDER BY [ProvidersXCodes].[SvcCode], [ProvidersXCodes].[Agency], [ProvidersXCodes].[SP Last], [ProvidersXCodes].[SP First], [ProvidersXCodes].[SP MI];
Problem solved!
I think having to verbalize the problem sort of helped come to the solution, as I made myself consider what the actual problem was and which specific pieces of information needed to be a cross-product versus which pieces of information needed to be more standard join operations and how to properly link the two sets...
~MG (me)
