Forum Discussion

Mercadogs's avatar
Mercadogs
Copper Contributor
Sep 16, 2020

Bills Paid Database

Hi there:
I’m using Access 2016.
I started database for bill payments.
In that database i have a first table called Bills where I have fields concerning my bills, including a BillID field, DueDate, etc. I have a second table called Payments, which includes a record of payments. For example, it has fields like BillID, DatePaid. I have a third table that includes a schedule of upcoming paydays.
I would like to create a SQL query that will return all my unpaid bills from now until my next payday, along with a total for all bills.
What’s the best way I can do that?
  • Woldman's avatar
    Woldman
    Iron Contributor

    Mercadogs 

    A combination of a query and a report could fulfill your request.

     

    First, this query selects the unpaid bills from now until the next payday:

    SELECT b.BillID, b.Amount, b.DueDate
    FROM Bills AS b LEFT JOIN Payments AS p ON b.BillID = p.BillID
    WHERE (((p.BillID) Is Null)

    AND ((b.DueDate)<=(SELECT TOP 1 PayDate FROM PayDays WHERE PayDate >= Date() ORDER BY PayDate)));

     

    I assume that the table with upcoming paydays is called PayDays and contains a date field with the name PayDate, but you can change these to your naming.

     

    Second, create a report based on this query (you could name it something like qBillsToBePaid). When you open the query in view mode and choose Report from the Create ribbon, Access generates a report. With a little tweaking (I modified the header and added a Total-label) the report definition looks like this:

     

    And when you view the report you'll get the bills to be paid with the total amount:

     

     

    Hope this helps.

     

    Best wishes,

    Tieme

    • Mercadogs's avatar
      Mercadogs
      Copper Contributor
      Thanks, Tieme!
      I tried Making the query but it didn’t work.
      But I messed up. I should have mentioned that my Bills table only has a Payment Due “Day” (as is day Of the month due), not a Payment Due Date. I was calculating the actual due date by pulling the latest paymentdate from payments table and using the bills.dueday In a Dateadd function. Therefore
      • Mercadogs's avatar
        Mercadogs
        Copper Contributor
        I meant “Therefore I’d have to figure out how to modify your query to also use the DateAdd function.
        My dateadd funtion looks like:
        Dateadd(“m”,1,Dateserial(Year(b.paiddate),Month(b.paiddate), b.dueday))

Resources