Displaying No Value in a query

R

ron.overton

Ok, long story short. I have made a tracking database in order to make
my boss' job easier. The only problem is that in the query/report, it
won't show any fields with a count of 0. Anyway I can make this
item/function be displayed?

Thank you.
 
M

[MVP] S.Clark

Create an Outer Join between the two tables.

For example, Find all customers that have not placed an order.
Add both tables to the query
Double-Click on the join line.
Change the Join type to display ALL records from Customers.
Add the Order.CustomerID to the grid, with the criteria of Isnull
Add fields from the customer table.

Run the query. You'll probably not get any results until you add a new
customer.
 
M

MGFoster

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Here's one way. Say you have a Products table and a Sales & you want to
find out the sales for all products in a month. I'd make a query like
this:

SELECT Product, Sum(SalesAmount) As Sales
FROM (
SELECT DISTINCT ProductID As Product, 0 As SalesAmount
FROM Products

UNION ALL

SELECT ProductID, SalesAmount
FROM Sales
WHERE SalesDate BETWEEN #1/1/2006# AND #1/31/2006#
) As A
GROUP BY Product

The SELECT on the Products table would get all products. The SELECT on
the Sales table would get all sales in Jan 2006. The top SELECT rolls
up all the Products and sales amounts into one line. Any products that
didn't have any sales in Jan 2006 will have a zero value in SalesAmount.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ+KAmIechKqOuFEgEQJFZwCgndoXmMGxUaqNDWkvQfcsYhWB9n8An2xG
iKrCcMSTRPn5Y/YTeQVJrQYe
=Htdf
-----END PGP SIGNATURE-----
 
Top