query to sum totals for month using sql

[Ecal]

I am trying to create a query that using the fields: ID, Purchase Date, Name of Institution, Items Purchased, Amount, Type of Receipt, and last four card #
in TblReceipts



here is what I am trying to do:
Run a query to show up on the bottom of frmReciepts that will populate the table above so that every time the user opens up the form it appears the update to the total for that month and updates as the user inputs current receipts the current month as well. so take for instance we have Month: February Number of transactions 7 total for month: 190 and the user inputs a receipt for February
of the amount of $80 then Number of transactions would display 8 and the total for the month would be $270 at the bottom when the user inputs another receipt. I am very new to SQL and I am trying to learn but some help here would be greatly appreciated.

[Dal Jeanis]

I think I have this straight.

You have a form called frmReciepts (I left that ie as you typed it)

This form is used to enter receipts. At the bottom of this form will be a subform that will provide a monthly summary, based upon the same table and the beginning and ending dates of the month.

Every time that someone enters a receipt, you want the subform to refresh the summary data.

So, given that you only wanted two fields, the record source for the subform will contain SQL something like the following:

SELECT COUNT(*), SUM(Amount) 
FROM tblReceipts
WHERE PurchaseDate BETWEEN startofmonth AND endofmonth

startofmonth and endofmonth would be two invisible controls on your main form that contained the calculated first day of the month and the last day of the month (or the current date). Depending on whether you used a query in a subform or the recordsource property in a control, you might need to fix the syntax to look something like " WHERE PurchaseDate BETWEEN " & Me.startofmonth & " AND " & Me.endofmonth

For something that simple, you could probably use two individual simple controls on the main form rather than a subform. On the other hand, if you wanted to have a more complex readout at the bottom that split out the results by user or by type of receipt, then you'd have to add the order by/group by clauses that you wanted, and use a subform. That might look something like this:

SELECT ReceiptType, COUNT(*), SUM(Amount) 
FROM tblReceipts
WHERE PurchaseDate BETWEEN startofmonth AND endofmonth
ORDER BY ReceiptType
GROUP BY ReceiptType

Hopefully that will get you started.