Field Subtotalling by Date

[domingo2615]

Hi All,



I am looking to add a field in my table that subtotals the "AmountPaid" from a linked table for which the record has a date in the "DatePaid" field within a certain month (say January).

There are multiple records from each month linking to this table of my suppliers.

Please let me know what else I need to provide to make this picture clearer.

Thanks

[rpeare]

You don't want to do subtotals on a table, it's really a lot of effort to maintain them and they are subject to a lot of errors with being out of date.

What I would suggest is that you link your primary table to your secondary table and sum the activity based on your service date and supplier.

[domingo2615]

So, would that sum be a calculated field in my supplier table? If so, do you know how I might go about writing that?

[rpeare]

Run this example

Create a table called Tbl_Customers
Create 2 fields
CustomerID (autonumber)
CustomerName (text)

Put in two customer names

Create a table called Tbl_Bills
create 4 fields
BillID (autonumber)
BillAmt (currency)
BillDate (date/time)
CustomerID (number)

Add some sample data in the billamt and billdate field (span months and dollar amounts) For the customerID always put in the value of your first customer (just to see what this code do not enter anything for customer 2)

Run this query

SELECT Tbl_Customers.CustomerID, Tbl_Customers.CustomerName, Sum(Tbl_Bills.BillAmt) AS [Total Bills], IIf(IsNull([billdate]),Null,DatePart("yyyy",[billdate]) & ", " & Mid(Format([billdate],"medium date"),4,3)) AS MonthLabel
FROM Tbl_Customers LEFT JOIN Tbl_Bills ON Tbl_Customers.CustomerID = Tbl_Bills.CustomerID
GROUP BY Tbl_Customers.CustomerID, Tbl_Customers.CustomerName, IIf(IsNull([billdate]),Null,DatePart("yyyy",[billdate]) & ", " & Mid(Format([billdate],"medium date"),4,3));

It will total everything by month for your customer and leave anything blank for customers who have no activity or data.

From this point you can apply filters however you want.

The key is that you're building a GROUPING query (the GROUP BY clause of the SQL statement) if you look at the design view of the query you'll notice there's a new line called TOTAL. Anything that you'll notice everything says GROUP BY except the BILLAMT field.

Anything that says GROUP BY you will get a separate line for each individual value so if your first customer has services in three different months you will get three different lines because the TOTALS line for the MONTHLABEL field says GROUP BY.

[domingo2615]

Thanks, this is exactly what I've been trying to do.

My last question, and excuse my ignorance here, but what would be the easiest way to get this information into a form or report?

I just want to show this in more easily digestible fashion

[domingo2615]

Update to my last question...

Instead of putting it into a form (which I figured out for myself) I was wondering if there were any way to make a field in the table which was one of these calculations. Say a field "Jan","Feb"..."Dec". Twelve in total which showed the sum for that month for the vendor.

Thanks again!

[rpeare]

That was really my point in showing you this, that storing the information is not necessary and is bad database practice. If you put a monthly total on a table for each record for that month you have to do a *lot* of maintenance as your monthly total changes as time goes on.

Let's take an example where you get one charge per day for every day of a 30 day month. Also assume (for the sake of ease) that each day the charge is 10$.

On the first day you have one record with a charge of 10$ and a subtotal of 10$ no problem.

On the second day you would have to put in the new record, perform a lookup for the total for the month so far, add that to today's total and put that total no only on the current record being added, but the previous record as well.

On the third day you're updating 3 records, the 4th, 4 records and so on. You really, really, really do not want to construct a database this way.

The type of operation you're doing is specifically what QUERIES are designed to do. If you have a query showing you exactly what you want build a report based on that QUERY not on a table with a value you have to update every time you add or change a record.