'Sum Of' and 'Start Date-End Date' clashing in query

[Savannah]

Hi,


I have a table with members in it, and within that a sub-table that I can enter multiple shows, their dates and points earned for each show.

I have put together a query on this table that includes a sum of all points by member.

I would like to be able to include a 'Set Start Date and Finish Date' so I can do an annual query to see who has earned the most points in a 12 month period but when I add the date to the query I lose the 'Sum of all points' and each date has it's own entry (so I get the same member multiple times instead of one single total entry).

Is there any way to get a total of all entry's by date range?

Happy to share the database with anyone who might be able to assist.
Cheers.

[ranman256]

Join the 2 tables,t.members, and tSubTable
bring Down fields: memberName,event,EventDate
visible fields are : memberName,Count(event)
do not make EventDate visible.

where: [eventDate]>=TxtStartDate and [eventDate]<=TxtEndDate

[Savannah]

Thank you, I will go and try that now

[Savannah]

Hi, the date is no longer showing however it is still separating the individual events for each participant instead of totalling them which I need them to do. Hopefully the image may assist? Thank you again for any help.

[CJ_London]

try changing the group by in your date column to where

Also date is not a good name for a field - date is a function that means todays date (like the now function which returns todays date and time)

[Savannah]

Whoohoooo! Thank you so MUCH Ajax! It worked.
Love this forum!