SELECT TblCharges.Chargeamt, TblCharges.Chargedate, TblPayments.Creditamt, TblPayments.Recdate
FROM TblPayments, TblCharges;


Trying to get totals (Chargeamt) and (Creditamt) by month (and ultimately by year)
The Chargeamt and Recdate date formats are day/month/year

I'm missing something I'm sure is simple.

I don't think that you can do this in 1 query as you need to Group by Month(Chargedate) and sum(Chargeamt) then ditto for credit, yo could then join these 2 queries in a 3rd on month to get what you require.

Brian

I need to apologize profusely. Copied a portion of the wrong query.

This is the query I am using -

SELECT Sum(TblPayments.Creditamt) AS SumOfCreditamt, Format([Recdate],"mmmm") AS AMonth, DatePart("yyyy",[Recdate]) AS AYear, DatePart("m",[Recdate]) AS Expr1
FROM TblPayments
GROUP BY Format([Recdate],"mmmm"), DatePart("yyyy",[Recdate]), DatePart("m",[Recdate])
ORDER BY DatePart("m",[Recdate]);

I really need to show the difference of charges and payments by month which would mean I would have to do this from two different tables but they are only linked by the parent table.

If you do the same for the charges then the 3rd query can join queries 1 and 2 on ayear and amonth and do the calculation.

Brian

Thanks
Assuming I can figure how to join the two queries.
I have one similar that I can maybe pattern it from

Its just the same as joining tables. Select both queries for the 3rd in design view and then drag the joining fields to each other. hope you follow that!

Brian

Here's what I have:

QryIncome
SELECT Sum(TblPayments.Creditamt) AS SumOfCreditamt, Format([Recdate],"mmmm") AS AMonth, DatePart("yyyy",[Recdate]) AS AYear, DatePart("m",[Recdate])
FROM TblPayments
GROUP BY Format([Recdate],"mmmm"), DatePart("yyyy",[Recdate]), DatePart("m",[Recdate])
ORDER BY DatePart("m",[Recdate]);

and

QryMonthDebit
SELECT Sum(TblCharges.Chargeamt) AS SumOfChargeamt, Format([Chargedate],"mmmm") AS AMonth, DatePart("yyyy",[Chargedate]) AS AYear, DatePart("m",[Chargedate]) AS Expr1
FROM TblCharges
GROUP BY Format([Chargedate],"mmmm"), DatePart("yyyy",[Chargedate]), DatePart("m",[Chargedate])
ORDER BY DatePart("m",[Chargedate]);

They both seem to work separately

So you can now put then together

select QryIncome.ayear, QryIncome.amonth, QryIncome.SumOfCreditamt, QryMonthDebit.SumOfChargeamt, SumOfCreditamt-SumOfChargeamt as diff
from QryMonthDebit innerjoin QryIncome on QryIncome.ayear = QryMonthDebit.ayear and QryIncome.amonth= QryMonthDebit.amonth
order by QryIncome.ayear

The above is air code so untested but a pointer to what you need to get the difference, actually in at least one of the queries you might like to carry DatePart("m",[Chargedate]) as a field to order in correct order.
the common fields are interchangeable in the select, eg amonth

Brian