Select top three and not responding error

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

The problem I have is very frustrating, because I KNOW that it should work
the way that I'm doing it...it's just that the database is too big!

Ok, here's the situation. I work for a law office that has a database with
all of thier cases and all of the notes made on these cases. They would like
to run a query (and later a report) that lists the LAST THREE NOTES made on
files that are still open. To give you a better understanding there are
8,763 cases and some 100,000 notes on all the files. When I drop that number
down to only open files I have 132 cases, but 4,646 notes.


I have been trying to use the following SQL statement to accomplish this:
SELECT *
FROM b
WHERE (((b.NoteID) In (select top 3 noteid from b s where s.namefile =
b.namefile order by timenote desc)));

b is the name of the query that I'm using to pull the information from.

It will work if I limit the number of notes to under 20, (I limited it to
the files that start with "b") but if I have any more notes than that it
crashes. The IT people in the office have put the database only on my
machine so that I'll stop crashing the network when I try to test this query,
but I still crash my machine everytime.

I assume that I need to divide this query into smaller queries, but I'm not
sure how to go about it.

Any suggestions would be very appreciated!
 
I can offer this workaround:

1. Add a field to the Notes Table. Call it whatever. How about Ordinal?
2. Run 3 update queries to set the field Ordinal, of the appropriate note
records, as either the 1st, 2nd, or 3rd. (You can exclude any cases that
have not had a note added to the case since the last time you performed this
action, too. Thus reducing the time it takes to execute.)

By doing these steps, you now know exactly which records to extract, as
they'll either have a 1, 2, or 3 in the Ordinal field. This eliminates the
need for the Subquery, which should eliminate the crashes and performance
issues.

Perform step 2 on a nightly basis, so that it happens after everyone is done
with their changes for that day.
 
Brittany-

Do you have an index defined on both the namefile and timenote fields? That
should improve performance significantly - and perhaps stop it from
crashing. Actually, Access should handle this quite easily. You might be
working in a corrupted database. If it still crashes, try creating a new
database and importing the tables from the old one.

--
John Viescas, author
"Building Microsoft Access Applications"
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
Ok, I have added the "ordinal" field. I had to actually create a new table
using a MakeTable Query because of the properties of the database.

As to the next part...I really don't know how to create the update query
(I've never needed to before) Can you (or anyone else) give a little
information on how what sort of criteria I would use to sort this
information? How do I make it put a 1 in the latest note, and a two before
that , and so on. I also don't know how to exclude the cases that haven't
been updated recently in that query. I guess I don't know as much about
queries as I thought.

On the bright side, once I figure that part out, I know how to do the last
step. Thanks so much for your patience.

Brittany
 
Back
Top