Query not showing desired results

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a query that is based on a 2 tables. The first is the primary, the
second is based of off the first with linked field.
I am trying to get the query to show all records with sum of product (first
table) and sum of rejects (second table) even if there are no rejects.

Currently I get:
Model Plant SumOfTotal Produced SumOfRejects
BC-TYP-12 41 26 2
BC-TYP-24 41 122 7
BC-TYP-30 41 140 2
BC-TYP-6 41 24 8

When I should get
Model Plant SumOfTotal Produced
Model Plant SumOfTotal Produced
BC-TYP-12 41 26 2
BC-TYP-16 41 95 0
BC-TYP-20 41 300 0
BC-TYP-24 41 122 7
BC-TYP-30 41 140 2
BC-TYP-36 41 2 0
BC-TYP-6 41 24 8
BC-TYP-9 41 123 0

How can I force the ones that do not have a linked record to show the value
and set it as zero? What do I need to type or enter in the query
requirements or calculation fields?
 
James said:
I have a query that is based on a 2 tables. The first is the primary, the
second is based of off the first with linked field.
I am trying to get the query to show all records with sum of product (first
table) and sum of rejects (second table) even if there are no rejects.

Currently I get:
Model Plant SumOfTotal Produced SumOfRejects
BC-TYP-12 41 26 2
BC-TYP-24 41 122 7
BC-TYP-30 41 140 2
BC-TYP-6 41 24 8

When I should get
Model Plant SumOfTotal Produced
Model Plant SumOfTotal Produced
BC-TYP-12 41 26 2
BC-TYP-16 41 95 0
BC-TYP-20 41 300 0
BC-TYP-24 41 122 7
BC-TYP-30 41 140 2
BC-TYP-36 41 2 0
BC-TYP-6 41 24 8
BC-TYP-9 41 123 0

How can I force the ones that do not have a linked record to show the value
and set it as zero? What do I need to type or enter in the query
requirements or calculation fields?
Click to expand...


Use an outer join between the two tables. E.g.

SELECT firsttbl.Model, first.Plant,
Sum(first.Produced) AS SumOfProduced,
Sum(second.Rejects) AS SumOfRejects
FROM first LEFT JOIN second
ON first.ID = second.ID

I.e. just change the INNER JOIN to LEFT JOIN.
 
It works and thanks! Took me a while to figure out the function.

Only other thing is how do I get the blanks to say the number "0" when there
is nothing.
 
If you're displaying the query directly in sheet view, use
the Nz function.

SELECT firsttbl.Model, first.Plant,
Nz(Sum(first.Produced), 0) AS SumOfProduced,
. . .
But, if you're using the query to feed a form or report,
it's a little better to leave the query alone and use the
text box's Format property:

0;;;"0"
 
Actually needed:

SELECT DISTINCTROW firsttbl.Model, first.Plant, Sum(first.[Produced]) AS
[SumOfProduced], Nz(Sum([second].Rejects),0) AS [Sum Of Rejects]

You pointed me in the right direction though with your first answer. It is
a query only. But trying it the first way showed me something else I was not
aware of. Thanks. It will help in the future.

Marshall Barton said:
If you're displaying the query directly in sheet view, use
the Nz function.

SELECT firsttbl.Model, first.Plant,
Nz(Sum(first.Produced), 0) AS SumOfProduced,
. . .
But, if you're using the query to feed a form or report,
it's a little better to leave the query alone and use the
text box's Format property:

0;;;"0"
--
Marsh
MVP [MS Access]


James said:
It works and thanks! Took me a while to figure out the function.
Only other thing is how do I get the blanks to say the number "0" when there
is nothing.
Click to expand...
Click to expand...
 
Back
Top