SQL query question

[Trey]

How do I tell an update query to only search the records that are older then
the current record?

I need to figure out how to write a query that will take a record, look to
see if any record with an older date and time has the same address, then
marks a yes/no box to YES if it does not find a duplicate.

I know this can be done, I just don't know how.

I know it will take a subquery of some kind. I am just lost. Any help would
be very much appreciated!

[Trey]

Forgot to mention, This is in Access 2000.

Trey wrote:

How do I tell an update query to only search the records that are
older then the current record?

I need to figure out how to write a query that will take a record,
look to see if any record with an older date and time has the same
address, then marks a yes/no box to YES if it does not find a
duplicate.

I know this can be done, I just don't know how.

I know it will take a subquery of some kind. I am just lost. Any help
would be very much appreciated!

[John Winterbottom]

"Trey" <tr***********@hotmail.com> wrote in message
news:Sv*****************@twister.socal.rr.com...

How do I tell an update query to only search the records that are older then the current record?

I need to figure out how to write a query that will take a record, look to see if any record with an older date and time has the same address, then
marks a yes/no box to YES if it does not find a duplicate.

I know this can be done, I just don't know how.

I know it will take a subquery of some kind. I am just lost. Any help would be very much appreciated!

Normally it would take a subquery - unfortunately that doesn't work in Jet.
You need to use domain aggregate functions or "queries-feeding-queries".
Give us some more info and someone will be able to write the sql for you.

[iuliegriffiths]

try in "Record Created" fleld run the query like "[date <*]" and Is Not
Null
try

if NO ........ then
txtFledName = yes in VBA

Trey" <tr***********@hotmail.com> wrote in message
news:Sv*****************@twister.socal.rr.com...
How do I tell an update query to only search the records that are older then
the current record?

I need to figure out how to write a query that will take a record, look
to
see if any record with an older date and time has the same address, then
marks a yes/no box to YES if it does not find a duplicate.

I know this can be done, I just don't know how.

I know it will take a subquery of some kind. I am just lost. Any help would
be very much appreciated!

[Trey]

John Winterbottom wrote:

"Trey" <tr***********@hotmail.com> wrote in message
news:Sv*****************@twister.socal.rr.com...

How do I tell an update query to only search the records that are
older then the current record?

I need to figure out how to write a query that will take a record,
look to see if any record with an older date and time has the same
address, then marks a yes/no box to YES if it does not find a
duplicate.

I know this can be done, I just don't know how.

I know it will take a subquery of some kind. I am just lost. Any
help would be very much appreciated!

Normally it would take a subquery - unfortunately that doesn't work
in Jet. You need to use domain aggregate functions or
"queries-feeding-queries". Give us some more info and someone will be
able to write the sql for you.

I have a table named "main". then I have a query named "12month", this query
selects only the records in the table that are 12 months old and newer. From
this point on, that is the only data that I will be working with for this
operation.

SQL for 12month:
SELECT DateDiff('m',[date_called],Date()) AS expr1, [main].[date_called],
[main].[call_count], [main].[Address], [main].[Zip], [main].[Billable],
[main].[Bill_sent], *
FROM main
WHERE (((DateDiff('m',[date_called],Date()))<=12))
ORDER BY [main].[date_called], [main].[Address];
---------------
This part works great. But now comes the update query that I am having
trouble with.
I have some ugly bastardized SQL that I have glued together. This update
query is able to find any records that do not have duplicate addresses, and
mark the warning = yes.

UPDATE 12month SET 12month.Warning = Yes
WHERE [12month].address = (SELECT [12month].Address
FROM 12month WHERE ((([12month].Address) In (SELECT [Address] FROM [12month]
As Tmp GROUP BY [Address] HAVING Count(*)<=1 )))
ORDER BY [12month].Address);
-------------
However, it searches all the records, not just the records that are older
then the current record. So instead of looking at a record, seeing if an
OLDER duplicate address exists, and marking the warning = yes accordingly,
it searches all the records. And if any duplicates exist (including new
duplicates) then it wont mark it.

I am afraid I did not explain this correctly, but this is the best I can do
with my limited SQL knowledge.

Thanks in advance for your help.