Help with duplicate query

[Guest]

I hope I can explain this so it makes sense....

I made a duplicate query with the wizard which is fine BUT I'd like to have
it give me only the records that duplicate since a certain date. My fields
are:

Name
Date
Amount

I'd like the query to tell me only the names, dates and amounts that occured
AFTER 9/30/05.

Any help out there?

 

[John Spencer]

Could you post the SQL of your query?

Open in Design View; Select VIEW: SQL from the menu. Copy and paste.

You probably have something that should look roughly like the following
(minus the WHERE CLAUSE

SELECT Name, Date, Amount
FROM YourTable
WHERE Name in
(SELECT Name
FROM YourTable
WHERE Date > #9/30/05#
GROUP BY Name
Having Count(Name) > 1)
AND Date < #9/30/05#

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Jeff Boyce]

Jessica

Open the query you already have in design view. Find the field that holds
your date value (by the way, if your fieldname is actually "Date", you and
Access will both get confused -- Access treats the word "Date" as a reserved
word. Consider changing it to SomethingDate or DateSomething, anything but
"Date").

In the row that holds selection criteria , under your field that holds the
date value, add ">9/30/05" (without the quotes).

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Eureka Jeff! It works!

It's always something simple like that...but like I said...they never tell
you this kind of real world type stuff in the Dummies books! And really...I
try so hard and read those books till I'm about blind. It's always about that
stupid auto dealership!

Gracias Sir!

 

[Jeff Boyce]

De nada...

Regards

Jeff Boyce
Microsoft Office/Access MVP