SQL help - queries very slow

[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

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Try this:

UPDATE [Duplicate Funds]
SET [yes/no] = True
WHERE [Date] In (SELECT Min(DF.[Date]) FROM [Duplicate Funds] As DF
WHERE DF.[Fund Name] = [Duplicate Funds].[Fund Name])

For speed, be sure the columns [Fund Name] and [Date] are indexed.

BTW, it would be better to rename the [Date] and [Yes/No] columns to
something more descriptive, 'cuz Date is a keyword (it is a data type) &
could be confused for VBA function Date() and [Yes/No] is a data type.
Use names that describe the contents of the column rather than the data
type of the columns. E.g.:

Date: sales_date, received_date, transaction_date
Yes/No: credit_rating_received, proposal_sent, account_closed,

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRFfv9YechKqOuFEgEQIwOQCgwAsqk4kw/uYw7h2NgHrgZUnOw2wAn2AK
01Hpv6NHoipkwna7MiKuI/7d
=GIDm
-----END PGP SIGNATURE-----