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

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Jen P.
    #1

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

    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.st aff_id, Count(tbl_accou nts.pmt_amt) AS no_of_payments
    FROM tbl_accounts
    WHERE (((tbl_accounts .pmt_amt)>=50))
    GROUP BY tbl_accounts.st aff_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)
    Tags: None
    • Salad
      #2
      Re: Calculating data where it exists and give '0' value where itdoesn't

      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.st aff_id, Count(tbl_accou nts.pmt_amt) AS no_of_payments
      FROM tbl_accounts
      WHERE (((tbl_accounts .pmt_amt)>=50))
      GROUP BY tbl_accounts.st aff_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.

        Comment

        • Jen P.
          #3
          Re: Calculating data where it exists and give '0' value where itdoesn't

          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.st aff_id, Count(tbl_accou nts.pmt_amt) AS
          >no_of_paymen ts
          >FROM tbl_accounts
          >WHERE (((tbl_accounts .pmt_amt)>=50))
          >GROUP BY tbl_accounts.st aff_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://rogersaccessblo g.blogspot.com/2008/09/ambiguous-outer-joins.html>

          Many thanks for your quick and extremely helpful reply. :D

          -Jen

            Comment

            Previous template Next
            Working...