Bills Paid Database

[Mercadogs]

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?

[#2]

@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

[#3]

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

[#4]

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

[#5]

Actually:
Dateadd(“m”,1,Dateserial(Year(p.paiddate),Month(p.paiddate), b.dueday))

[#6]

@Mercadogs 

Hopefully, this query will do want you want:

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 (SELECT DateAdd("m", 1, Dateserial( Year(MAX(DatePaid)), Month(MAX(DatePaid)), b.DueDay)) FROM Payments)<=(SELECT TOP 1 PayDate FROM PayDays WHERE PayDate >= Date() ORDER BY PayDate);

Good luck,

Tieme

[#7]

Thanks! That certainly works better than anything I’ve been able to come up with on my own.
However, for some reason it is not returning all expected records.
So far, I only notice my mortgage Bill is missing from the results. It is due on the 1st of each month. And my next payday is on September 3rd. So, the mortgage bill should be showing up in the results, but doesn’t.

[#8]

The last time I paid it was on September 1, 2020.

[#9]

Also, how do I handle the situation when I add a nee bill that doesn’t have any payment history?

[#10]

Actually, I realized that the results of this query only includes bills for which there is no previous payment history at all.

[#11]

@Tieme Woldman 

This is my code exactly:

<

SELECT b.Description, b.BillerID, b.Amount, b.DueDay
FROM Bills AS b LEFT JOIN Payments AS p ON b.BillerID = p.BillerID
WHERE ((((SELECT  DateAdd("m",1,DateSerial( Year(MAX(PaidDate)), Month(MAX(PaidDate)), b.DueDay)) FROM Payments GROUP BY p.paiddate))<=(SELECT TOP 1 IncomeDate FROM IncomeSchedule WHERE IncomeDate >= Date() ORDER BY IncomeDate))) AND (p.BillerID Is Null);

>

[#12]

Can somebody answer my question.
I’d like to fix this query. I have been using Mint.com to stay on top of my bill payments.
However, I realize that sometimes, a bill that’s due will not come up in the bills due list, which is an unfortunate thing and makes me lose trust in the that application.

[#13]

@Mercadogs 

Okay, sometimes it helps to see actual data, along with samples of what you want to see as the output. That way we can possibly see details that might be missed in a summary of the sort we can put into a description. 

You can attach a sample accdb with sanitized data for that purpose.

[#14]

@George Hepworth 

Hi George:

(I tried to upload the database, but I get the following error: "The attachment's billssanatized20201029.accdb content type (application/msaccess) does not match its file extension and has been removed.").

I tried to respond earlier, but I was not feeling well, and my response did not go through correctly.

The query I'm trying to correct is called "QueryInProcess". With this query, I'd like to analyze at "Bills", "Payments", and "IncomeSchedule" tables and return all Bills that are due and unpaid (or overdue) between current date and the next payday (see "IncomeDate" field in the "IncomeSchedule" table).

If I can figure this out, I'll get a better idea of how much discretionary income I have left until next pay check.

A sample output (not actual data) would look like this:

<<Description>>	<<BillerID>>	<<Amount>>	<<Due Day>>
Card 1	1	$30	31
Phone	2	$50	1

[#15]

@Mercadogs 

Try compressing the accdb into a ZIP file for upload.

[#16]

@George Hepworth 

Here it is. Please let me know if there are any issues with opening it.