How do I quantify the instances of a value over a date range?

[Sonny Jim]

I have a query where I am trying to count the number of claims issued against a specific insurance company. I have no problem until I impose a date range, then I end up with a listing of the number of claims against a company per date entered. Is there a way to define a date range and still get a total of the number of claims per insurance company over the entire date range defined instead of getting a day-to-day tally?

 

[Kiwiman]

Howzit

It sounds like you are grouping by date. In the show part of your query, uncheck the date field, and in the total section, select WHERE on the date field.

Your SQL view will look something like

SELECT tblInvoices.CustomerId, Count(tblInvoices.InvoiceId) AS CountOfInvoiceId
FROM tblInvoices
WHERE (((tblInvoices.InvoiceDate) Between #1/1/2008# And #12/31/2008#))
GROUP BY tblInvoices.CustomerId;

 

[redneckgeek]

DRAT... somebody beat me to it.

 

[Sonny Jim]

Ha, ha, ha!!! You guys are good!!! Thanks a million!!!