Sep 16 2020 12:43 PM
Sep 16 2020 12:43 PM
Sep 17 2020 12:17 AM
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.
Sep 22 2020 06:18 AM
Sep 22 2020 06:22 AM
Sep 23 2020 05:40 AM
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);
Sep 23 2020 01:21 PM
Sep 23 2020 02:15 PM
Sep 23 2020 03:33 PM
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);
Oct 26 2020 06:26 AM
Oct 26 2020 06:20 PM
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.
Oct 29 2020 02:02 PM
(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: