Query Problems

[brc]

Trying to create a Rent Roll Report displaying rent paid each month on each property grouped by Owner. All payments are inputed into a table called Payment then I have a query which pulls all the records that are rent and I use that in the Rent Roll Query. Problem I am having that it displays all the payments to that property as separate records. I need the rent payments to one property during selected month to display in one record. If you need more info let me konw.
Thanks

[pbaldy]

With your query in design view, click on the Totals icon that looks like a Greek "E". Change the amount field from Group By to Sum and see what that does for you.

[brc]

Thanks for the reply! unfortunately that didn't work either. Still displays the records the same.

[pbaldy]

Likely you have too many fields included with Group By. What is the SQL of that query?

[pbaldy]

Or you have the date field included. It should just say Where.

[brc]

This Report is turning out to be quite complex and really confusing me. Here is the saved Query that I have the report using.

SELECT DISTINCT [Property Extended].[File As], [Property Extended].AddressMid, PropertyType.PropertyType, Company.CompanyName, [Active Leases].[Tenant Name], [Property Extended].ManagementFee, [Active Leases].EndDate, [Payments Rent].Amount AS [Rent Amt], [Active Leases].MonthlyLeaseAmount, [Rent Amt]-[MonthlyLeaseAmount] AS Balance, [Payments Rent].DatePaid
FROM [Payments Rent] RIGHT JOIN ([Active Leases] RIGHT JOIN (([Property Extended] INNER JOIN PropertyType ON [Property Extended].Property.PropertyTypeID = PropertyType.PropertyTypeID) LEFT JOIN Company ON [Property Extended].CompanyID = Company.CompanyID) ON [Active Leases].PropertyID = [Property Extended].PropertyID) ON [Payments Rent].LeaseID = [Active Leases].LeaseID;

[pbaldy]

Well, it doesn't look like you tried the Totals icon. The query would include a GROUP BY clause, and Sum() on the appropriate field(s). Another option you can use is to group by property on the report, include a textbox with =Sum(FieldName) in the group footer, and hide the detail section. It would be less efficient, but should yield the desired look.