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]

Re: SQL query question

Forgot to mention, This is in Access 2000.

Trey wrote:[color=blue]
> 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![/color]

[John Winterbottom]

Re: SQL query question

"Trey" <treydog90spam@ hotmail.com> wrote in message
news:SvlRc.2685 $Qa4.1832@twist er.socal.rr.com ...[color=blue]
> How do I tell an update query to only search the records that are older[/color]
then[color=blue]
> the current record?
>
> I need to figure out how to write a query that will take a record, look[/color]
to[color=blue]
> 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[/color]
would[color=blue]
> be very much appreciated!
>
>[/color]


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@ihug.co.nz]

Re: SQL query question

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

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

Trey" <treydog90spam@ hotmail.com> wrote in message
news:SvlRc.2685 $Qa4.1832@twist er.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]

Re: SQL query question

John Winterbottom wrote:[color=blue]
> "Trey" <treydog90spam@ hotmail.com> wrote in message
> news:SvlRc.2685 $Qa4.1832@twist er.socal.rr.com ...[color=green]
>> 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!
>>
>>[/color]
>
>
> 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.[/color]


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.