Hi,

I'm working on a database where I need to calculate some data that fits
certain sets of parameters, but I'd like it to give me more information
than I know how to get.

As an example, let's say I'm querying a table with the following fields:

pmt_no pmt_amt staff_id

pmt_no is unique, but isn't being used in these calculations.

I've done a query that counts the number of payments as long as the
payment is more than 50:

SELECT tbl_accounts.staff_id, Count(tbl_accounts.pmt_amt) AS no_of_payments
FROM tbl_accounts
WHERE (((tbl_accounts.pmt_amt)>=50))
GROUP BY tbl_accounts.staff_id;

This works fine, as far as it's meant to: it gives me a list of all the
people who've had any payments >=50.

However, what I'd really like is maybe to link this with another table
that contains *all* staff_ids and, if they haven't had a payment of over
50, I'd like my query to give me a '0' next to that staff id.

Is this possible or am I hoping for too much?

(Please do let me know if anything is unclear - I tried to think of as
simple an example as possible; my real data is very slightly more
complex, but the principle is the same!)

Thank you for reading this far.

-Jen (if you care to reply by email, remove the spork)

Jen P. wrote:
In the query builder drop both tables into it. Most likely there will
be a link line between StaffID in both tables. Dbl-Click on the line
and select "All recs from EmpTable and Matching recs from TblAccounts"

Drag the StaffID from tblAccounts. If it's null, then it'd be 0.

Salad wrote:
Hurrah! Cheers for that. I ran into an 'Ambiguous outer join'
error, but I was able to fix that with a quick Google and help from:

<http://rogersaccessblog.blogspot.com/2008/09/ambiguous-outer-joins.html>

Many thanks for your quick and extremely helpful reply.

-Jen