query records by month

[Guest]

My main query, [Journal], has a date dd/mm/yy for each record. I would like
to query those people that pay rent and have one record for each person
rather than all the payments they make. For instance, their rent may be 650,
but they may make several payments and have several records each month and i
want to see their total amount. So how do i sort by month and just have one
record for each month for each person? thanks a lot.

 

[Brian Camire]

You might try a query whose SQL looks something like this:

SELECT
[Journal].[Person ID],
Year([Journal].[Payment Date]) AS [Year],
Month([Journal].[Payment Date]) AS [Month],
Sum([Journal].[Payment Amount]) AS [Total Payment]
FROM
[Journal]
GROUP BY
[Journal].[Person ID],
Year([Journal].[Payment Date]),
Month([Journal].[Payment Date])

If a person makes no payments in a given month, there will be no
corresponding record in the result. Post back if that's not what you want.

If it's possible for one person to rent more than one "thing" (whatever
you're renting), you might want to group by "thing" instead of by person
(for example, if you are trying to find out for which "things" rent is in
arrears).

 

[PC Datasheet]

Open your query in design view. Remove your date field and enter this
expression in its place:
MonthOfRent:Month(NameOfYourDateField)
Be sure this field comes before the renters' names field.
Click on the Sigma (looks like E) button the toolbar at the top of the
screen. Change Group By under Rent to Sum.