IIF or something else

D

darren

I have tables where each parameter is assigned a score (see Table1 and table2
below). I want a query that will return the sum of "Don't know" for each site
and unit. The problem is where "don't know" does not occur such as in table2.
When I run the query joining the two tables with param (criteria set to
"Don't Know" and sum T_score and S_Score, the query returns no records.

Table1
ID Site unit category Param M_Score
1 1 1 material metal 1
2 1 2 material Don't Know 1

Table2
ID Site unit category Param T_Score
1 1 1 tex fine 1
2 1 2 tex coarse 1

Currently, the query returns field headings only as follows:
Site unit M_Score T_Score

What I would like returned is:
Site unit M_Score T_Score
1 1 0 0
1 2 1 0

I appreciate your ideas

Regards
Darren
 
K

KARL DEWEY

Use a union query to build a complete list of site & unit --
UnionSiteUnit --
DELECT Site, unit
FROM Table1
UNION DELECT Site, unit
FROM Table2;

SELECT UnionSiteUnit.Site, UnionSiteUnit.Unit, Sum([M_Score]) AS
SumOfM_Score, Sum([T_Score]) AS SumOfT_Score)
FROM UnionSiteUnit LEFT JOIN M_Score ON (UnionSiteUnit.Site = Table1.Site)
AND (UnionSiteUnit.Unit= Table1.Unit)
GROUP BY UnionSiteUnit.Site, UnionSiteUnit.Unit;
 
K

KARL DEWEY

Post before finished editing --
SELECT UnionSiteUnit.Site, UnionSiteUnit.Unit, Sum([M_Score]) AS
SumOfM_Score, Sum([T_Score]) AS SumOfT_Score)
FROM (UnionSiteUnit LEFT JOIN Table1 ON UnionSiteUnit.Site = Table1.Site
AND UnionSiteUnit.Unit= Table1.Unit) UnionSiteUnit LEFT JOIN Table2 ON
UnionSiteUnit.Site = Table2.Site AND UnionSiteUnit.Unit= Table2.Unit
GROUP BY UnionSiteUnit.Site, UnionSiteUnit.Unit;

KARL DEWEY said:
Use a union query to build a complete list of site & unit --
UnionSiteUnit --
DELECT Site, unit
FROM Table1
UNION DELECT Site, unit
FROM Table2;

SELECT UnionSiteUnit.Site, UnionSiteUnit.Unit, Sum([M_Score]) AS
SumOfM_Score, Sum([T_Score]) AS SumOfT_Score)
FROM UnionSiteUnit LEFT JOIN M_Score ON (UnionSiteUnit.Site = Table1.Site)
AND (UnionSiteUnit.Unit= Table1.Unit)
GROUP BY UnionSiteUnit.Site, UnionSiteUnit.Unit;

darren said:
I have tables where each parameter is assigned a score (see Table1 and table2
below). I want a query that will return the sum of "Don't know" for each site
and unit. The problem is where "don't know" does not occur such as in table2.
When I run the query joining the two tables with param (criteria set to
"Don't Know" and sum T_score and S_Score, the query returns no records.

Table1
ID Site unit category Param M_Score
1 1 1 material metal 1
2 1 2 material Don't Know 1

Table2
ID Site unit category Param T_Score
1 1 1 tex fine 1
2 1 2 tex coarse 1

Currently, the query returns field headings only as follows:
Site unit M_Score T_Score

What I would like returned is:
Site unit M_Score T_Score
1 1 0 0
1 2 1 0

I appreciate your ideas

Regards
Darren
Click to expand...
Click to expand...
 
K

KARL DEWEY

I left the criteria out --
SELECT UnionSiteUnit.Site, UnionSiteUnit.Unit, Sum([M_Score]) AS
SumOfM_Score, Sum([T_Score]) AS SumOfT_Score)
FROM (UnionSiteUnit LEFT JOIN Table1 ON UnionSiteUnit.Site = Table1.Site
AND UnionSiteUnit.Unit= Table1.Unit) UnionSiteUnit LEFT JOIN Table2 ON
UnionSiteUnit.Site = Table2.Site AND UnionSiteUnit.Unit= Table2.Unit
WHERE [Table1].[Param] = "Don't Know" OR [Table2].[Param] = "Don't Know"
GROUP BY UnionSiteUnit.Site, UnionSiteUnit.Unit;


KARL DEWEY said:
Post before finished editing --
SELECT UnionSiteUnit.Site, UnionSiteUnit.Unit, Sum([M_Score]) AS
SumOfM_Score, Sum([T_Score]) AS SumOfT_Score)
FROM (UnionSiteUnit LEFT JOIN Table1 ON UnionSiteUnit.Site = Table1.Site
AND UnionSiteUnit.Unit= Table1.Unit) UnionSiteUnit LEFT JOIN Table2 ON
UnionSiteUnit.Site = Table2.Site AND UnionSiteUnit.Unit= Table2.Unit
GROUP BY UnionSiteUnit.Site, UnionSiteUnit.Unit;

KARL DEWEY said:
Use a union query to build a complete list of site & unit --
UnionSiteUnit --
DELECT Site, unit
FROM Table1
UNION DELECT Site, unit
FROM Table2;

SELECT UnionSiteUnit.Site, UnionSiteUnit.Unit, Sum([M_Score]) AS
SumOfM_Score, Sum([T_Score]) AS SumOfT_Score)
FROM UnionSiteUnit LEFT JOIN M_Score ON (UnionSiteUnit.Site = Table1.Site)
AND (UnionSiteUnit.Unit= Table1.Unit)
GROUP BY UnionSiteUnit.Site, UnionSiteUnit.Unit;

darren said:
I have tables where each parameter is assigned a score (see Table1 and table2
below). I want a query that will return the sum of "Don't know" for each site
and unit. The problem is where "don't know" does not occur such as in table2.
When I run the query joining the two tables with param (criteria set to
"Don't Know" and sum T_score and S_Score, the query returns no records.

Table1
ID Site unit category Param M_Score
1 1 1 material metal 1
2 1 2 material Don't Know 1

Table2
ID Site unit category Param T_Score
1 1 1 tex fine 1
2 1 2 tex coarse 1

Currently, the query returns field headings only as follows:
Site unit M_Score T_Score

What I would like returned is:
Site unit M_Score T_Score
1 1 0 0
1 2 1 0

I appreciate your ideas

Regards
Darren
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Query on two tables with matching null fields 4
IIf statement or DLookup or something else 1
Summary and Details need to combined in a query 0
IIF statement 1
Union Query with division 2
Linked Tables Append Query(ies) 4
Exclusion Query- MS Access 2007 2
Some good Select or While 8

Top