Hi. I'm currently trying to create a query which works out from the following information what the outstanding balance for each invoice in a table.
I can then easily bring up a list of unpaid invoices for each client which can go on the bottom of any new invoices as a reminder. I'm looking to do this rather than simply use the total balance outstanding (£1000 in the above example) because:
a) different amounts would have different due dates, some of the outstanding balance will be overdue, some won't.
b) I'd rather be able to outline to each client exactly what they haven't paid than give them a total. I just feel they're more likely to respond positively if the reminder says exactly which invoices are overdue and when they should have been paid by.
I had this idea on reading about the domain aggregate functions that they could be used to achieve this, but now I'm not sure I'm understanding them correctly. It could be I'm barking up the wrong tree.
I'll refer to this example, as it's much simpler than my own example
http://www.techonthenet.com/access/functions/domain/dsum.php
In it, they use the example function:
to sum the [UnitPrice] field from the [Order Details] table for all records where the [OrderID] field is 10248
Simple enough, except this would return the exact same figure for all rows in the query in which it is used, which is why I think I'm barking up the wrong tree.
Say you were using this function in a query where different rows referred to different OrderIDs and you wanted the function in each row to sum the unit prices in Order Details where the Order ID matched that specific row.
Instead of "OrderID = 10248", there might be a column in your query called [OrderIDtosum] and you'd think you could have the expression read: "OrderID = [OrderIDtosum]" or something similar. Except it appears you can't. You have to simply use a value such as 10248 that is fixed and doesn't vary from row to row.
If this is the case then I don't think domain aggregate is the function that will work for me, but I wanted to check.
- The total amount that was invoiced
- The client the invoice is for
- The total that client has ever been invoiced
- The total that client has ever paid
- The due date on the invoice
I can then easily bring up a list of unpaid invoices for each client which can go on the bottom of any new invoices as a reminder. I'm looking to do this rather than simply use the total balance outstanding (£1000 in the above example) because:
a) different amounts would have different due dates, some of the outstanding balance will be overdue, some won't.
b) I'd rather be able to outline to each client exactly what they haven't paid than give them a total. I just feel they're more likely to respond positively if the reminder says exactly which invoices are overdue and when they should have been paid by.
I had this idea on reading about the domain aggregate functions that they could be used to achieve this, but now I'm not sure I'm understanding them correctly. It could be I'm barking up the wrong tree.
I'll refer to this example, as it's much simpler than my own example
http://www.techonthenet.com/access/functions/domain/dsum.php
In it, they use the example function:
Code:
DSum("UnitPrice", "Order Details", "OrderID = 10248")Simple enough, except this would return the exact same figure for all rows in the query in which it is used, which is why I think I'm barking up the wrong tree.
Say you were using this function in a query where different rows referred to different OrderIDs and you wanted the function in each row to sum the unit prices in Order Details where the Order ID matched that specific row.
Instead of "OrderID = 10248", there might be a column in your query called [OrderIDtosum] and you'd think you could have the expression read: "OrderID = [OrderIDtosum]" or something similar. Except it appears you can't. You have to simply use a value such as 10248 that is fixed and doesn't vary from row to row.
If this is the case then I don't think domain aggregate is the function that will work for me, but I wanted to check.
Last edited:
