Show a 0 if there is no record

[bassstuf via AccessMonster.com]

I need to find a way to create a query that will show a "0" if there is no
matching record.

The details

I have 3 tables with part number & serial number with different data. One
table consists of shipping information, one with receiving, and the other
consists of returns. I have these tables linked in different queries by a
combination of part number and serial number (expr 1: part number&" "& serial
number). It works great with with receiving & shipping information (bc they
each contain all the parts & serial numbers) but the returns are much less.
So how do I show a "0" if there is not matching record from the returns? I
cannot figure the null function out at all

 

[Stefan Hoffmann]

hi,

I need to find a way to create a query that will show a "0" if there is no
matching record.

The details

I have 3 tables with part number& serial number with different data. One
table consists of shipping information, one with receiving, and the other
consists of returns. I have these tables linked in different queries by a
combination of part number and serial number (expr 1: part number&""& serial
number). It works great with with receiving& shipping information (bc they
each contain all the parts& serial numbers) but the returns are much less.

So you have an INNER JOIN between receiving and shipping and a LEFT JOIN
to returns?

So how do I show a "0" if there is not matching record from the returns? I
cannot figure the null function out at all

You need an extra column for that, e.g.

HasResults: CBool(IIf(IsNull([Returns].[mandatoryField]);False;True))


mfG
--> stefan <--

 

[KARL DEWEY]

Create a union query using all 3 of the tables like this --
SELECT [part number], [serial number]
FROM Shipping
GROUP BY [part number], [serial number]
UNION SELECT [part number], [serial number]
FROM Receiving
GROUP BY [part number], [serial number]
UNION SELECT [part number], [serial number]
FROM Returns
GROUP BY [part number], [serial number];

Do not use UNION ALL as you want the output to be list of unique [part
number] and [serial number] combinations.

Then left join this query in your other query to each table.
Use an IIF statement like this --
Shipping_Activity: IIF([Shipping].[part number] Is Null OR
[Shipping].[serial number] Is Null, 0, Null)
OR
Shipping_Part_Number: IIF([Shipping].[part number] Is Null, 0,
[Shipping].[part number])
with --
Shipping_Serial_Number: IIF([Shipping].[serial number] Is Null, 0,
[Shipping].[serial number])