SQL help - update query to select one duplicate each

[hermanko]

Hi,

I need help with the SQL for an update query. The underlying query that
i want to update has duplicate records (shown below), by Fund Name
field, but all other data is different, including the Date field.
Included is a Yes/No checkbox field. I need to run an update query that
will select the "older" record by Date for each duplicate.

Sample query to be updated:

ID Fund Name Date Remove?
1 Fund16 3/20/2006 (checkbox)
2 Fund16 4/20/2006 (checkbox)
3 Fund21 3/20/2006 (checkbox)
4 Fund21 4/20/2006 (checkbox)
5 Fund35 3/20/2006 (checkbox)
6 Fund35 4/20/2006 (checkbox)
.....

I want to have the Update Query select only records 1,3 and 5 by
updating the "Remove?" field to TRUE.

The SQL that i have SO FAR (shown below) is only set up to set ALL
records to TRUE. I dont really know the syntax well enough to modify it
to my needs. Any assistance would be greatly appreciated!!!

UPDATE [Duplicate Fund Name (same Proxy)] SET [Duplicate Fund Name
(same Proxy)].[Yes/No] = True
WHERE ((([Duplicate Fund Name (same Proxy)].[Yes/No])=False));

thanks!
Herman

 

[Guest]

SELECT [Fund Name], [Date]
FROM Table1 AS T1
GROUP BY T1.[Fund Name], T1.[Date]
HAVING T1.[Date] = (SELECT Max(T2.[Date]) FROM Table1 AS T2
WHERE T1.[Fund Name] = T2.[Fund Name])

 

[Guest]

Actually, I shouldnt have posted my earlier query just yet. But, create a
query like the one I posted (Query1). Then create another query which is the
update query, as follows.

UPDATE Table1 SET [Remove] = -1
WHERE [Date] In(SELECT [Date] FROM Query1 WHERE [Date] = Table1.[Date] And
[Fund Name] = Table1.[Fund Name])

SELECT [Fund Name], [Date]
FROM Table1 AS T1
GROUP BY T1.[Fund Name], T1.[Date]
HAVING T1.[Date] = (SELECT Max(T2.[Date]) FROM Table1 AS T2
WHERE T1.[Fund Name] = T2.[Fund Name])

Hi,

I need help with the SQL for an update query. The underlying query that
i want to update has duplicate records (shown below), by Fund Name
field, but all other data is different, including the Date field.
Included is a Yes/No checkbox field. I need to run an update query that
will select the "older" record by Date for each duplicate.

Sample query to be updated:

ID Fund Name Date Remove?
1 Fund16 3/20/2006 (checkbox)
2 Fund16 4/20/2006 (checkbox)
3 Fund21 3/20/2006 (checkbox)
4 Fund21 4/20/2006 (checkbox)
5 Fund35 3/20/2006 (checkbox)
6 Fund35 4/20/2006 (checkbox)
.....

I want to have the Update Query select only records 1,3 and 5 by
updating the "Remove?" field to TRUE.

The SQL that i have SO FAR (shown below) is only set up to set ALL
records to TRUE. I dont really know the syntax well enough to modify it
to my needs. Any assistance would be greatly appreciated!!!

UPDATE [Duplicate Fund Name (same Proxy)] SET [Duplicate Fund Name
(same Proxy)].[Yes/No] = True
WHERE ((([Duplicate Fund Name (same Proxy)].[Yes/No])=False));

thanks!
Herman

 

[hermanko]

Hi JonWayn.

Thanks for the SQL. Now i have a query to extract the older of the two
duplicates. But I still need to update all of those selected in this
query to TRUE in the checkbox. An update query doesn't not work anymore
because the query is a totals query and it's "not updateable".

How do i work around this?

Herman

 

[hermanko]

I didn't see your 2nd reply... thanks a bunch! It seems to be working
fine now

 

[Guest]

I am not quite sure I understand what you need to do now. But, if you want to
do the inverse of what you asked earlier, which is; update [Remove] to TRUE
where the Date for each duplicate pair is older, then all you had to do in
the first place was to update [Remove] to TRUE for all records.

If you needed, instead, to update [Remove] to FALSE for older dupes, then,
update all records to false, first, and then run the two queries I suggested
earlier - that would update the newer dates to TRUE, leaving the older
(anything else) as FLASE.

If you were asking something else, please rephrase.

 

[hermanko]

Actually, you answered my question and my form seems to be working well
in that respect now. Thanks! I just have to work through my other
issues now