Calculating data where it exists and give '0' value where it doesn't

[Jen P.]

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)

[Salad]

Jen P. wrote:

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)

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.

[Jen P.]

Salad wrote:

Jen P. wrote:

<snip>

>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.

<snip>

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.

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. :D

-Jen