Access Query for Duplicates between Two Dates

Question

I would like to create a query to count records between two dates:

for example, a table [TabNam] that includes fields [Item Name], [Date], and [Sale Price]

I can find duplicates using the query wizard, but cannot figure how count between dates. Can you help with SQL that will do this count between two dates.

The SQL query without dates is:

Select First(TabNam.[Item Name] AS [Item Name] ,Count(TabNam.[Item Name] AS NumberOfDups

 from [TabNam]

GROUP By TabNam.[Item Name]

HAVING (((Count(TabNam.[Item Name]))>1);

Thank you.

Answer 1

First, rename your date field. Date is a reserved word, and you should never use reserved words for your own purposes. For a comprehensive list of names to avoid (as well as a link to a free utility to check your application for compliance), check what Allen Browne has at http://www.allenbrowne.com/AppIssueBadWord.html

Second, I see no point to the use of First in your query.

To hard-code the dates, you'd use something like:

SELECT TabNam.[Item Name] AS [Item Name], Count(TabNam.[Item Name] AS NumberOfDups

FROM [TabNam]

WHERE MyDateField BETWEEN #2011-02-01# AND #2011-02-02#

GROUP By TabNam.[Item Name]

HAVING Count(TabNam.[Item Name]>1;

If you want to prompt for the dates, use something like

PARAMETERS [Start Date] DateTime, [End Date] DateTime;

SELECT TabNam.[Item Name] AS [Item Name], Count(TabNam.[Item Name] AS NumberOfDups

FROM [TabNam]

WHERE MyDateField BETWEEN [Start Date] AND [End Date]

GROUP By TabNam.[Item Name]

HAVING Count(TabNam.[Item Name]>1;

---

Doug Steele, Microsoft Access MVP

http://www.AccessMVP.com/djsteele (no e-mails, please!)

Co-author, Access Solutions — Tips, Tricks, and Secrets from Microsoft Access MVPs (published by Wiley, ISBN 978-0-470-59168-0)

Answer 2

Yes, this works as you explained above.

BTW, I have bought your book and am enjoying it.

Thanks for your support.