CODEWITHSUNDEEP
sqlms-access
jerry
jerry

Reputation: 2789

SQL - how to make a query account for all potential IDs

There are 10 possible construction sites that I need to account for in my report. However, as my code is now, when a construction site is not in my database, it isn't accounted for at all, which makes sense, but I would prefer it list all of the possible construction sites and put 0 as the value instead of returning nothing. The reason I need to do this is because I am creating reports based off these queries, and it's hard to line everything up unless I consistently have all of the construction sites accounted for every time. Here is the SQL:

TRANSFORM Count(Main.ID) AS CountOfID
SELECT 'Total IDs' AS [Construction site   >>>]
FROM Research INNER JOIN Main ON Research.Primary_ID = Main.ID
GROUP BY 'Total IDs'
PIVOT Research.Construction_site;

By the way, I am using MS Access 2007 is that makes a difference.

Thanks

Upvotes: 0

Views: 102

Answers (2)

Jaymz
Jaymz

Reputation: 6348

If I'm reading your question correctly, you want all fields from the Research table, regardless of whether they are in the Main table. In which case, you just need a LEFT OUTER JOIN:

TRANSFORM Count(Main.ID) AS CountOfID
SELECT 'Total IDs' AS [Construction site   >>>]
FROM Research LEFT OUTER JOIN Main ON Research.Primary_ID = Main.ID
GROUP BY 'Total IDs'
PIVOT Research.Construction_site;

This will return all rows from the Research table at least once - and multiple times if they exist more than once in the Main table.

Upvotes: 1

Andriy M
Andriy M

Reputation: 77707

Most probably you need to replace INNER JOIN with LEFT JOIN. (I.e. simply change 'INNER' to 'LEFT'.) That way the construction sites not represented in Main won't be filtered out.

Upvotes: 0

Related Questions