Forum Discussion
Mercadogs
Sep 16, 2020Copper Contributor
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?
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?
- WoldmanIron Contributor
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
- MercadogsCopper ContributorThanks, 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- MercadogsCopper ContributorI 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))