H
hermanko
Hi,
I have a table that intentionally has duplicate [Fund Name] records,
but with different dates. There is also a [Yes/No] checkbox field.
Sample below:
Table: [Duplicate Funds]
[Fund Name] [Date] [Yes/No]
Fund1 April 1, 2006 (checkbox)
Fund1 May 1, 2006 (checkbox)
Fund2 April 1, 2006 (checkbox)
Fund2 May 1, 2006 (checkbox)
etc....
On a form, I have a command button called "selectall" that should check
the records with the EARLIER date within each duplicate....so it would
update the checkboxes to True for Fund1, April1 and Fund2, April2
only....and so on...
I have tested it with my current set up and even with just 100 records
in my table (so 50 records would be checked/updated), it took over 30
seconds to run the query! I don't know why it's so slow.
Below are my SQL that i have. I know it's not the best way to do it so
any suggestions would be appreciated....if i can somehow combine it
into one sql statement:
Select Query: [Select Old Records]
sql:
SELECT T1.[Fund Name], T1.Date, T1.[Yes/No]
FROM [Duplicate Funds] AS T1
GROUP BY T1.[Fund Name], T1.Date, T1.[Yes/No]
HAVING (((T1.Date)=(SELECT Min(T2.[Date]) FROM [Duplicate Funds] AS T2
WHERE T1.[Fund Name] = T2.[Fund Name])));
Update Query: [Select All]
sql:
UPDATE [Duplicate Funds] SET [Duplicate Funds].[Yes/No] = -1
WHERE ((([Duplicate Funds].Date) In (SELECT [Date] FROM [Select Old
Records] WHERE [Date] = [Duplicate Funds].[Date] And [Fund Name] =
[Duplicate Funds].[Fund Name])));
Thanks!!!
Herman
I have a table that intentionally has duplicate [Fund Name] records,
but with different dates. There is also a [Yes/No] checkbox field.
Sample below:
Table: [Duplicate Funds]
[Fund Name] [Date] [Yes/No]
Fund1 April 1, 2006 (checkbox)
Fund1 May 1, 2006 (checkbox)
Fund2 April 1, 2006 (checkbox)
Fund2 May 1, 2006 (checkbox)
etc....
On a form, I have a command button called "selectall" that should check
the records with the EARLIER date within each duplicate....so it would
update the checkboxes to True for Fund1, April1 and Fund2, April2
only....and so on...
I have tested it with my current set up and even with just 100 records
in my table (so 50 records would be checked/updated), it took over 30
seconds to run the query! I don't know why it's so slow.
Below are my SQL that i have. I know it's not the best way to do it so
any suggestions would be appreciated....if i can somehow combine it
into one sql statement:
Select Query: [Select Old Records]
sql:
SELECT T1.[Fund Name], T1.Date, T1.[Yes/No]
FROM [Duplicate Funds] AS T1
GROUP BY T1.[Fund Name], T1.Date, T1.[Yes/No]
HAVING (((T1.Date)=(SELECT Min(T2.[Date]) FROM [Duplicate Funds] AS T2
WHERE T1.[Fund Name] = T2.[Fund Name])));
Update Query: [Select All]
sql:
UPDATE [Duplicate Funds] SET [Duplicate Funds].[Yes/No] = -1
WHERE ((([Duplicate Funds].Date) In (SELECT [Date] FROM [Select Old
Records] WHERE [Date] = [Duplicate Funds].[Date] And [Fund Name] =
[Duplicate Funds].[Fund Name])));
Thanks!!!
Herman