| re: SQL query question
John Winterbottom wrote:[color=blue]
> "Trey" <treydog90spam@hotmail.com> wrote in message
> news:SvlRc.2685$Qa4.1832@twister.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.
| 
