Combining 12 MS Access queries with different "where" criteria into one query

Question

I have twelve different SELECT queries, each with different WHERE criteria. Each query has the same ID code (APINumber), and I'd like to create an output of:

APINumber | Data from Query 1 | Data from Query 2 | etc

I currently have each query linked to excel, and after refreshing each query in excel, I have a separate tab of vlookups that combines all of the data. My issue is that the queries take a very long time to refresh, so going through excel to then be combined is very inefficient. Ultimately, my goal is to link to a Tableau file for visualizations.

I've found message boards of how to link 2 queries, but I've overwhelmed with trying to link all 12. Some of the data fields will be blank for a given ID code, but I still want the ID code to show.

https://dba.stackexchange.com/questions/63506/merge-two-select-queries-with-different-where-clauses

Here are my queries:

Total Water

SELECT dbo_RegistryUpload.APINumber, Avg(dbo_RegistryUpload.TotalWater) AS AvgOfTotalWater
FROM (dbo_RegistryUpload AS dbo_RegistryUpload INNER JOIN (dbo_RegistryUploadPurpose INNER JOIN dbo_RegistryUploadIngredients ON dbo_RegistryUploadPurpose.pKey = dbo_RegistryUploadIngredients.pKeyPurpose) ON dbo_RegistryUpload.pKey = dbo_RegistryUploadPurpose.pKeyRegistryUpload) INNER JOIN [API Count] ON dbo_RegistryUpload.APINumber = [API Count].APINumber
WHERE ((([API Count].Howmany)=1))
GROUP BY dbo_RegistryUpload.APINumber
HAVING (((dbo_RegistryUpload.APINumber) Like "42*" Or (dbo_RegistryUpload.APINumber) Like "42*" Or (dbo_RegistryUpload.APINumber) Like "42*" Or (dbo_RegistryUpload.APINumber) Like "42*" Or (dbo_RegistryUpload.APINumber) Like "42*"));

Water %

SELECT dbo_RegistryUpload_1.APINumber, Sum(dbo_RegistryUploadIngredients.PercentHFJob) AS WaterPerc
FROM (dbo_RegistryUpload AS dbo_RegistryUpload_1 INNER JOIN (dbo_RegistryUploadPurpose INNER JOIN dbo_RegistryUploadIngredients ON dbo_RegistryUploadPurpose.pKey = dbo_RegistryUploadIngredients.pKeyPurpose) ON dbo_RegistryUpload_1.pKey = dbo_RegistryUploadPurpose.pKeyRegistryUpload) INNER JOIN [API Count] ON dbo_RegistryUpload_1.APINumber = [API Count].APINumber
WHERE (((dbo_RegistryUploadIngredients.IngredientName) Like "*base*") AND (([API Count].Howmany)=1)) OR (((dbo_RegistryUploadIngredients.IngredientName) Like "*Carrier*") AND (([API Count].Howmany)=1)) OR ((([API Count].Howmany)=1) AND ((dbo_RegistryUploadPurpose.Purpose) Like "*base*")) OR ((([API Count].Howmany)=1) AND ((dbo_RegistryUploadPurpose.Purpose) Like "*carrier*")) OR ((([API Count].Howmany)=1) AND ((dbo_RegistryUploadPurpose.Purpose) Like "*water*"))
GROUP BY dbo_RegistryUpload_1.APINumber
HAVING (((dbo_RegistryUpload_1.APINumber) Like "42*" Or (dbo_RegistryUpload_1.APINumber) Like "42*" Or (dbo_RegistryUpload_1.APINumber) Like "42*" Or (dbo_RegistryUpload_1.APINumber) Like "42*" Or (dbo_RegistryUpload_1.APINumber) Like "42*"));

Proppant

SELECT dbo_RegistryUpload.APINumber, Sum(dbo_RegistryUploadIngredients.PercentHFJob) AS Prop
FROM [API Count] INNER JOIN ((dbo_RegistryUpload INNER JOIN dbo_RegistryUploadPurpose ON dbo_RegistryUpload.pKey = dbo_RegistryUploadPurpose.pKeyRegistryUpload) INNER JOIN dbo_RegistryUploadIngredients ON dbo_RegistryUploadPurpose.pKey = dbo_RegistryUploadIngredients.pKeyPurpose) ON [API Count].APINumber = dbo_RegistryUpload.APINumber
WHERE (((dbo_RegistryUploadPurpose.Purpose) Like "*prop*" And (dbo_RegistryUploadPurpose.Purpose) Not Like "*GEL*") AND (([API Count].Howmany)=1)) OR (((dbo_RegistryUploadPurpose.Purpose) Like "*sand*" And (dbo_RegistryUploadPurpose.Purpose) Not Like "*gel*") AND (([API Count].Howmany)=1))
GROUP BY dbo_RegistryUpload.APINumber
HAVING (((dbo_RegistryUpload.APINumber) Like "42*" Or (dbo_RegistryUpload.APINumber) Like "42*" Or (dbo_RegistryUpload.APINumber)="42"));

100 Mesh Proppant

SELECT dbo_RegistryUpload.APINumber, Sum(dbo_RegistryUploadIngredients.PercentHFJob) AS 100mesh
FROM [API Count] INNER JOIN ((dbo_RegistryUpload INNER JOIN dbo_RegistryUploadPurpose ON dbo_RegistryUpload.pKey = dbo_RegistryUploadPurpose.pKeyRegistryUpload) INNER JOIN dbo_RegistryUploadIngredients ON dbo_RegistryUploadPurpose.pKey = dbo_RegistryUploadIngredients.pKeyPurpose) ON [API Count].APINumber = dbo_RegistryUpload.APINumber
WHERE (((dbo_RegistryUploadPurpose.TradeName) Like "*100 mesh*") AND (([API Count].Howmany)=1))
GROUP BY dbo_RegistryUpload.APINumber
HAVING (((dbo_RegistryUpload.APINumber) Like "42*"));

20/40 Proppant

SELECT dbo_RegistryUpload.APINumber, Sum(dbo_RegistryUploadIngredients.PercentHFJob) AS 2040
FROM [API Count] INNER JOIN ((dbo_RegistryUpload INNER JOIN dbo_RegistryUploadPurpose ON dbo_RegistryUpload.pKey = dbo_RegistryUploadPurpose.pKeyRegistryUpload) INNER JOIN dbo_RegistryUploadIngredients ON dbo_RegistryUploadPurpose.pKey = dbo_RegistryUploadIngredients.pKeyPurpose) ON [API Count].APINumber = dbo_RegistryUpload.APINumber
WHERE (((dbo_RegistryUploadPurpose.TradeName) Like "*20/40*") AND (([API Count].Howmany)=1)) OR (((dbo_RegistryUploadPurpose.TradeName) Like "*20-40*") AND (([API Count].Howmany)=1))
GROUP BY dbo_RegistryUpload.APINumber
HAVING (((dbo_RegistryUpload.APINumber) Like "42*" Or (dbo_RegistryUpload.APINumber) Like "42*"));

30/50 Proppant

SELECT dbo_RegistryUpload.APINumber, Sum(dbo_RegistryUploadIngredients.PercentHFJob) AS 3050
FROM [API Count] INNER JOIN ((dbo_RegistryUpload INNER JOIN dbo_RegistryUploadPurpose ON dbo_RegistryUpload.pKey = dbo_RegistryUploadPurpose.pKeyRegistryUpload) INNER JOIN dbo_RegistryUploadIngredients ON dbo_RegistryUploadPurpose.pKey = dbo_RegistryUploadIngredients.pKeyPurpose) ON [API Count].APINumber = dbo_RegistryUpload.APINumber
WHERE (((dbo_RegistryUploadPurpose.TradeName) Like "*30/50*") AND (([API Count].Howmany)=1)) OR (((dbo_RegistryUploadPurpose.TradeName) Like "*30-50*") AND (([API Count].Howmany)=1))
GROUP BY dbo_RegistryUpload.APINumber
HAVING (((dbo_RegistryUpload.APINumber) Like "42*" Or (dbo_RegistryUpload.APINumber) Like "42*"));

40/70 Proppant

SELECT dbo_RegistryUpload.APINumber, Sum(dbo_RegistryUploadIngredients.PercentHFJob) AS 4070
FROM [API Count] INNER JOIN ((dbo_RegistryUpload INNER JOIN dbo_RegistryUploadPurpose ON dbo_RegistryUpload.pKey = dbo_RegistryUploadPurpose.pKeyRegistryUpload) INNER JOIN dbo_RegistryUploadIngredients ON dbo_RegistryUploadPurpose.pKey = dbo_RegistryUploadIngredients.pKeyPurpose) ON [API Count].APINumber = dbo_RegistryUpload.APINumber
WHERE (((dbo_RegistryUploadPurpose.TradeName) Like "*40/70*") AND (([API Count].Howmany)=1)) OR (((dbo_RegistryUploadPurpose.TradeName) Like "*40-70*") AND (([API Count].Howmany)=1))
GROUP BY dbo_RegistryUpload.APINumber
HAVING (((dbo_RegistryUpload.APINumber) Like "42*" Or (dbo_RegistryUpload.APINumber) Like "42*"));

Breaker %

SELECT dbo_RegistryUpload.APINumber, Sum(dbo_RegistryUploadIngredients.PercentHFJob) AS BreakerPerc
FROM [API Count] INNER JOIN ((dbo_RegistryUpload INNER JOIN dbo_RegistryUploadPurpose ON dbo_RegistryUpload.pKey = dbo_RegistryUploadPurpose.pKeyRegistryUpload) INNER JOIN dbo_RegistryUploadIngredients ON dbo_RegistryUploadPurpose.pKey = dbo_RegistryUploadIngredients.pKeyPurpose) ON [API Count].APINumber = dbo_RegistryUpload.APINumber
WHERE ((([API Count].Howmany)=1) AND ((dbo_RegistryUploadPurpose.Purpose) Like "*breaker*"))
GROUP BY dbo_RegistryUpload.APINumber
HAVING (((dbo_RegistryUpload.APINumber) Like "42*" Or (dbo_RegistryUpload.APINumber) Like "42*"));

CrossLink Mass %

SELECT dbo_RegistryUpload.APINumber, Sum(dbo_RegistryUploadIngredients.PercentHFJob) AS Crosslink
FROM ((dbo_RegistryUpload INNER JOIN dbo_RegistryUploadPurpose ON dbo_RegistryUpload.pKey = dbo_RegistryUploadPurpose.pKeyRegistryUpload) INNER JOIN dbo_RegistryUploadIngredients ON dbo_RegistryUploadPurpose.pKey = dbo_RegistryUploadIngredients.pKeyPurpose) INNER JOIN [API Count] ON dbo_RegistryUpload.APINumber = [API Count].APINumber
WHERE (((dbo_RegistryUploadPurpose.Purpose) Like "cross*" And (dbo_RegistryUploadPurpose.Purpose) Not Like "*breaker*") AND (([API Count].Howmany)=1))
GROUP BY dbo_RegistryUpload.APINumber
HAVING (((dbo_RegistryUpload.APINumber) Like "42*" Or (dbo_RegistryUpload.APINumber) Like "42*" Or (dbo_RegistryUpload.APINumber) Like "42*"));

Gel Mass

SELECT dbo_RegistryUpload.APINumber, Sum(dbo_RegistryUploadIngredients.PercentHFJob) AS GelMass
FROM ((dbo_RegistryUpload INNER JOIN dbo_RegistryUploadPurpose ON dbo_RegistryUpload.pKey = dbo_RegistryUploadPurpose.pKeyRegistryUpload) INNER JOIN dbo_RegistryUploadIngredients ON dbo_RegistryUploadPurpose.pKey = dbo_RegistryUploadIngredients.pKeyPurpose) INNER JOIN [API Count] ON dbo_RegistryUpload.APINumber = [API Count].APINumber
WHERE (((dbo_RegistryUploadPurpose.Purpose) Like "gel*" And (dbo_RegistryUploadPurpose.Purpose) Not Like "*breaker*") AND (([API Count].Howmany)=1)) OR (((dbo_RegistryUploadPurpose.Purpose) Like "*guar*" And (dbo_RegistryUploadPurpose.Purpose) Not Like "*breaker*") AND (([API Count].Howmany)=1))
GROUP BY dbo_RegistryUpload.APINumber
HAVING (((dbo_RegistryUpload.APINumber) Like "42*" Or (dbo_RegistryUpload.APINumber) Like "42*"));

Resin

SELECT dbo_RegistryUpload.APINumber, Sum(dbo_RegistryUploadIngredients.PercentHFJob) AS Resin
FROM [API Count] INNER JOIN ((dbo_RegistryUpload INNER JOIN dbo_RegistryUploadPurpose ON dbo_RegistryUpload.pKey = dbo_RegistryUploadPurpose.pKeyRegistryUpload) INNER JOIN dbo_RegistryUploadIngredients ON dbo_RegistryUploadPurpose.pKey = dbo_RegistryUploadIngredients.pKeyPurpose) ON [API Count].APINumber = dbo_RegistryUpload.APINumber
WHERE (((dbo_RegistryUploadPurpose.Purpose) Like "*resin*") AND (([API Count].Howmany)=1)) OR ((([API Count].Howmany)=1) AND ((dbo_RegistryUploadPurpose.TradeName) Like "*RCS*"))
GROUP BY dbo_RegistryUpload.APINumber
HAVING (((dbo_RegistryUpload.APINumber) Like "42*"));

"Some of the data fields will be blank for a given ID code, but I still want the ID code to show" sounds like you should read up on outer joins (ms-access docs on left/right join: https://msdn.microsoft.com/en-us/library/bb208894(v=office.12).aspx) — Mikegrann, Aug 29 '16 at 16:56

Wolves · Accepted Answer · 2016-08-29T20:01:12.393

Since you already have all the queries written, why not use them as subqueries and join them together?

select dbo_RegistryUpload.APINumber, AvgOfTotalWater, WaterPerc
from (dbo_RegistryUpload as dbo_RegistryUpload
left join (SELECT dbo_RegistryUpload.APINumber, 

    Avg(dbo_RegistryUpload.TotalWater) AS AvgOfTotalWater
FROM (dbo_RegistryUpload AS dbo_RegistryUpload INNER JOIN (dbo_RegistryUploadPurpose INNER JOIN dbo_RegistryUploadIngredients ON dbo_RegistryUploadPurpose.pKey = dbo_RegistryUploadIngredients.pKeyPurpose) ON dbo_RegistryUpload.pKey = dbo_RegistryUploadPurpose.pKeyRegistryUpload) INNER JOIN [API Count] ON dbo_RegistryUpload.APINumber = [API Count].APINumber
WHERE ((([API Count].Howmany)=1))
GROUP BY dbo_RegistryUpload.APINumber
HAVING (((dbo_RegistryUpload.APINumber) Like "42*" Or (dbo_RegistryUpload.APINumber) Like "42*" Or (dbo_RegistryUpload.APINumber) Like "42*" Or (dbo_RegistryUpload.APINumber) Like "42*" Or (dbo_RegistryUpload.APINumber) Like "42*"))
) as atw on atw.APINumber = dbo_RegistryUpload.APINumber)

left join 

(SELECT dbo_RegistryUpload_1.APINumber, Sum(dbo_RegistryUploadIngredients.PercentHFJob) AS WaterPerc
FROM (dbo_RegistryUpload AS dbo_RegistryUpload_1 INNER JOIN (dbo_RegistryUploadPurpose INNER JOIN dbo_RegistryUploadIngredients ON dbo_RegistryUploadPurpose.pKey = dbo_RegistryUploadIngredients.pKeyPurpose) ON dbo_RegistryUpload_1.pKey = dbo_RegistryUploadPurpose.pKeyRegistryUpload) INNER JOIN [API Count] ON dbo_RegistryUpload_1.APINumber = [API Count].APINumber
WHERE (((dbo_RegistryUploadIngredients.IngredientName) Like "*base*") AND (([API Count].Howmany)=1)) OR (((dbo_RegistryUploadIngredients.IngredientName) Like "*Carrier*") AND (([API Count].Howmany)=1)) OR ((([API Count].Howmany)=1) AND ((dbo_RegistryUploadPurpose.Purpose) Like "*base*")) OR ((([API Count].Howmany)=1) AND ((dbo_RegistryUploadPurpose.Purpose) Like "*carrier*")) OR ((([API Count].Howmany)=1) AND ((dbo_RegistryUploadPurpose.Purpose) Like "*water*"))
GROUP BY dbo_RegistryUpload_1.APINumber
HAVING (((dbo_RegistryUpload_1.APINumber) Like "42*" Or (dbo_RegistryUpload_1.APINumber) Like "42*" Or (dbo_RegistryUpload_1.APINumber) Like "42*" Or (dbo_RegistryUpload_1.APINumber) Like "42*" Or (dbo_RegistryUpload_1.APINumber) Like "42*"))
) as wp on wp.APINumber = dbo_RegistryUpload.APINumber

Hopefully that gets the point across, just do the same thing for the rest of your queries.

In my option, this is going to be the best performance and the most scalable option -- if you ever need to add additional subqueries, it will be easy to do. Plus, you can tune this query easier than twelve separate queries. It is best to handle this kind of aggregation / data joining outside of tableau if you can. Just having tableau create the joins can add additional overhead. just my 2 cents. — Aron, Aug 29 '16 at 17:52
Ok thanks! I'm not familiar with the atw / wp language and I'm getting an error on that particular line - can you elaborate? — E Parker, Aug 29 '16 at 18:48
@E Parker Sure! Can you tell us which variety of SQL you're using? I'm just assigning a table alias (atw for Average of Total Water, wp for Water Percent), and I'm writing this based on T-SQL(Microsoft SQL Server). If you're using a different variety, I might be able to help. Could you also tell us what the error message says? — Wolves, Aug 29 '16 at 19:07
I'm using Access SQL. It's a syntax error, says missing operator in atw.APINumber = dbo_RegistryUpload.APINumber — E Parker, Aug 29 '16 at 19:46
Okay, I think you need to use AS, for example : left join ([query]) AS [atw] — Wolves, Aug 29 '16 at 19:53
Even if I delete that line, it doesn't like the "left join". From other googling, other forums seem to indicate you can't use subquery in Access: http://stackoverflow.com/questions/13567772/is-it-possible-to-use-subquery-in-join-condition-in-access ? — E Parker, Aug 29 '16 at 19:53
Access SQL does support standard joins: https://support.office.com/en-us/article/Access-SQL-FROM-clause-2a7d031c-6912-4e8c-a644-590940ddaed1 That question was specifically about using a subquery in the join itself, which is not what you're trying to do. — Wolves, Aug 29 '16 at 19:56
I think I found the problem: access needs parenthesis for multiple joins: http://stackoverflow.com/questions/7854969/sql-multiple-join-statement I'll edit my answer to include this. It's going to get annoying with the number of tables you're joining, but it should work! — Wolves, Aug 29 '16 at 19:59

Combining 12 MS Access queries with different "where" criteria into one query

1 Answers1