Bills Paid Database

%3CLINGO-SUB%20id%3D%22lingo-sub-1675150%22%20slang%3D%22en-US%22%3EBills%20Paid%20Database%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1675150%22%20slang%3D%22en-US%22%3EHi%20there%3A%3CBR%20%2F%3EI%E2%80%99m%20using%20Access%202016.%3CBR%20%2F%3EI%20started%20database%20for%20bill%20payments.%3CBR%20%2F%3EIn%20that%20database%20i%20have%20a%20first%20table%20called%20Bills%20where%20I%20have%20fields%20concerning%20my%20bills%2C%20including%20a%20BillID%20field%2C%20DueDate%2C%20etc.%20I%20have%20a%20second%20table%20called%20Payments%2C%20which%20includes%20a%20record%20of%20payments.%20For%20example%2C%20it%20has%20fields%20like%20BillID%2C%20DatePaid.%20I%20have%20a%20third%20table%20that%20includes%20a%20schedule%20of%20upcoming%20paydays.%3CBR%20%2F%3EI%20would%20like%20to%20create%20a%20SQL%20query%20that%20will%20return%20all%20my%20unpaid%20bills%20from%20now%20until%20my%20next%20payday%2C%20along%20with%20a%20total%20for%20all%20bills.%3CBR%20%2F%3EWhat%E2%80%99s%20the%20best%20way%20I%20can%20do%20that%3F%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1675150%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3E2016%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EAccess%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1676906%22%20slang%3D%22en-US%22%3ERe%3A%20Bills%20Paid%20Database%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1676906%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F795748%22%20target%3D%22_blank%22%3E%40Mercadogs%3C%2FA%3E%26nbsp%3BA%20combination%20of%20a%20query%20and%20a%20report%20could%20fulfill%20your%20request.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFirst%2C%20this%20query%20selects%20the%20unpaid%20bills%20from%20now%20until%20the%20next%20payday%3A%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%222%22%3ESELECT%20b.BillID%2C%20b.Amount%2C%20b.DueDate%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20size%3D%222%22%3EFROM%20Bills%20AS%20b%20LEFT%20JOIN%20Payments%20AS%20p%20ON%20b.BillID%20%3D%20p.BillID%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20size%3D%222%22%3EWHERE%20(((p.BillID)%20Is%20Null)%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%222%22%3EAND%20((b.DueDate)%26lt%3B%3D(SELECT%20TOP%201%20PayDate%20FROM%20PayDays%20WHERE%20PayDate%20%26gt%3B%3D%20Date()%20ORDER%20BY%20PayDate)))%3B%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20assume%20that%20the%20table%20with%20upcoming%20paydays%20is%20called%20PayDays%20and%20contains%20a%20date%20field%20with%20the%20name%20PayDate%2C%20but%20you%20can%20change%20these%20to%20your%20naming.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESecond%2C%20create%20a%20report%20based%20on%20this%20query%20(you%20could%20name%20it%20something%20like%20qBillsToBePaid).%20When%20you%20open%20the%20query%20in%20view%20mode%20and%20choose%20Report%20from%20the%20Create%20ribbon%2C%20Access%20generates%20a%20report.%20With%20a%20little%20tweaking%20(I%20modified%20the%20header%20and%20added%20a%20Total-label)%20the%20report%20definition%20looks%20like%20this%3A%3C%2FP%3E%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%3CP%3EAnd%20when%20you%20view%20the%20report%20you'll%20get%20the%20bills%20to%20be%20paid%20with%20the%20total%20amount%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Bills2.png%22%20style%3D%22width%3A%20961px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F219037iCDC9116A92C7E2FB%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22Bills2.png%22%20alt%3D%22Bills2.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20this%20helps.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBest%20wishes%2C%3C%2FP%3E%3CP%3ETieme%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1676917%22%20slang%3D%22en-US%22%3ERe%3A%20Bills%20Paid%20Database%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1676917%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F795748%22%20target%3D%22_blank%22%3E%40Mercadogs%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EA%20combination%20of%20a%20query%20and%20a%20report%20could%20fulfill%20your%20request.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFirst%2C%20this%20query%20selects%20the%20unpaid%20bills%20from%20now%20until%20the%20next%20payday%3A%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%222%22%3ESELECT%20b.BillID%2C%20b.Amount%2C%20b.DueDate%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20size%3D%222%22%3EFROM%20Bills%20AS%20b%20LEFT%20JOIN%20Payments%20AS%20p%20ON%20b.BillID%20%3D%20p.BillID%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20size%3D%222%22%3EWHERE%20(((p.BillID)%20Is%20Null)%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%222%22%3EAND%20((b.DueDate)%26lt%3B%3D(SELECT%20TOP%201%20PayDate%20FROM%20PayDays%20WHERE%20PayDate%20%26gt%3B%3D%20Date()%20ORDER%20BY%20PayDate)))%3B%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20assume%20that%20the%20table%20with%20upcoming%20paydays%20is%20called%20PayDays%20and%20contains%20a%20date%20field%20with%20the%20name%20PayDate%2C%20but%20you%20can%20change%20these%20to%20your%20naming.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESecond%2C%20create%20a%20report%20based%20on%20this%20query%20(you%20could%20name%20it%20something%20like%20qBillsToBePaid).%20When%20you%20open%20the%20query%20in%20view%20mode%20and%20choose%20Report%20from%20the%20Create%20ribbon%2C%20Access%20generates%20a%20report.%20With%20a%20little%20tweaking%20(I%20modified%20the%20header%20and%20added%20a%20Total-label)%20the%20report%20definition%20looks%20like%20this%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Bills1.png%22%20style%3D%22width%3A%20966px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F219039i7CEFACD46B6AE929%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22Bills1.png%22%20alt%3D%22Bills1.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnd%20when%20you%20view%20the%20report%20you'll%20get%20the%20bills%20to%20be%20paid%20with%20the%20total%20amount%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Bills2.png%22%20style%3D%22width%3A%20961px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F219040iFBADE77BE76EDC05%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22Bills2.png%22%20alt%3D%22Bills2.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20this%20helps.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBest%20wishes%2C%3C%2FP%3E%3CP%3ETieme%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1698694%22%20slang%3D%22en-US%22%3ERe%3A%20Bills%20Paid%20Database%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1698694%22%20slang%3D%22en-US%22%3EThanks%2C%20Tieme!%3CBR%20%2F%3EI%20tried%20Making%20the%20query%20but%20it%20didn%E2%80%99t%20work.%3CBR%20%2F%3EBut%20I%20messed%20up.%20I%20should%20have%20mentioned%20that%20my%20Bills%20table%20only%20has%20a%20Payment%20Due%20%E2%80%9CDay%E2%80%9D%20(as%20is%20day%20Of%20the%20month%20due)%2C%20not%20a%20Payment%20Due%20Date.%20I%20was%20calculating%20the%20actual%20due%20date%20by%20pulling%20the%20latest%20paymentdate%20from%20payments%20table%20and%20using%20the%20bills.dueday%20In%20a%20Dateadd%20function.%20Therefore%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor
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?
10 Replies
Highlighted

@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:

Bills1.png

 

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

 

Bills2.png

 

Hope this helps.

 

Best wishes,

Tieme

Highlighted
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
Highlighted
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))
Highlighted
Actually:
Dateadd(“m”,1,Dateserial(Year(p.paiddate),Month(p.paiddate), b.dueday))
Highlighted

@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

Highlighted
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.
Highlighted
The last time I paid it was on September 1, 2020.
Highlighted
Also, how do I handle the situation when I add a nee bill that doesn’t have any payment history?
Highlighted
Actually, I realized that the results of this query only includes bills for which there is no previous payment history at all.
Highlighted

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

>