Selecting Top 3 - Subquery won't work

[Guest]

Ok, let's try this one more time.
The database that I'm working with is for a law firm and it has records of
all of the cases (filename) that they are working on and all of the notes
that are related to that case. What I need is a query/table/report ANYTHING
that will list the last three notes made on each file. I have tried using
(thanks to the help of this discussion borad) subqueries to complete this.
The problem is that it freezes my computer. Well, to be accurate, when I
switch to Task Manager my CPU is running at 100% capacity, and the program
fluctuates between responding and not responding. I'll let the query sit for
hours, and it still never returns the results. Here is the SQL statment for
the query that i've been trying to run:

SELECT *
FROM File Notes Query
WHERE noteid in (select top 3 noteid from [file notes query] S where
S.namefile = [file notes query].[namefile] order by time DESC);

THIS DOESNT WORK!!

Ok, the next step that I took is to adapt the following code statement found
on
http://support.microsoft.com/default.aspx?scid=kb;en-us;210039

Function NthInGroup(GroupID, N)
' Returns the Nth Item in GroupID for use as a Top N per group
' query criteria.
Static LastGroupId, LastNthInGroup
Dim ItemName, GroupIDName, GDC, SearchTable
Dim SQL As String, rs As Recordset, db As DATABASE

If (LastGroupId = GroupID) Then
' Returned saved result if function is called with the
' same GroupID more than once in a row.
NthInGroup = LastNthInGroup
Else
' Set the SQL statement parameters. These are the only items
' that need to be customized in this function.
' Set to Item field name.
ItemName = "OrderDate"
' Set to Group ID field name.
GroupIDName = "CustomerID"
' GroupID Delimiter Character:
' For Text use "'", Date "#", Numeric ""
GDC = "'"
' Set to search table.
SearchTable = "Orders"
' Build a Top N SQL statement dynamically given N and
' GroupID as parameters. Note that the sort is by the
' item in descending order, in order to get the Top N
' largest items.
SQL = "Select Top " & N & " [" & ItemName & "] "
SQL = SQL & "From [" & SearchTable & "] "
SQL = SQL & "Where [" & GroupIDName & "]=" & GDC & GroupID & GDC & " "
SQL = SQL & "Order By [" & ItemName & "] Desc"
' Open up recordset on Top N SQL statement and read the
' last record to get the smallest item in the Top N.
Set db = CurrentDb()
Set rs = db.OpenRecordset(SQL)
If (rs.BOF And rs.EOF) Then
' No matches found, return a null.
LastNthInGroup = Null
LastGroupId = GroupID
NthInGroup = LastNthInGroup
Else
' Return the smallest Top N item in the group.
rs.MoveLast
LastNthInGroup = rs(ItemName)
LastGroupId = GroupID
NthInGroup = LastNthInGroup
End If
End If

End Function


The problem with this is that I haven't written code in about 5 years and I
was never really that good at it. I keep getting error messages when I try
to run this. With the line: Set rs = db.OpenRecordset(SQL) I get an error
message saying that I have open parameters. I have no idea how to fix it.

I know that my skill level isn't up there when it comes to code. So I would
really like to have some solution that would enable me to not have to use
code (wishful thinking I know)

Another option that I thought might work would be to rank that notes. I
could give the oldest note the ranking of 1 and so on and so forth, and then
run a query that pulls the last 3 notes. But I think that I'll have the same
problem as before. If I can't pull notes based on a decending date, how will
I be able to pull it with a number?

Any suggestions on this problem would be very helpful.

 

[Ken Snell [MVP]]

You need square brackets around the query name itself and a few other items:

SELECT *
FROM [File Notes Query]
WHERE noteid in (select top 3 S.noteid from [file notes query] S where
S.namefile = [file notes query].[namefile] order by S.[time] DESC);


Also, do not use Time as the name of a field. It and many other words are
reserved words in ACCESS, and can create serious
confusion for ACCESS and Jet. See these Knowledge Base articles for more
information:

List of reserved words in Access 2002 and Access 2003
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

List of Microsoft Jet 4.0 reserved words
http://support.microsoft.com/?id=321266

Special characters that you must avoid when you work with Access
databases
http://support.microsoft.com/?id=826763


--

Ken Snell
<MS ACCESS MVP>

Ok, let's try this one more time.
The database that I'm working with is for a law firm and it has records of
all of the cases (filename) that they are working on and all of the notes
that are related to that case. What I need is a query/table/report
ANYTHING
that will list the last three notes made on each file. I have tried using
(thanks to the help of this discussion borad) subqueries to complete this.
The problem is that it freezes my computer. Well, to be accurate, when I
switch to Task Manager my CPU is running at 100% capacity, and the program
fluctuates between responding and not responding. I'll let the query sit
for
hours, and it still never returns the results. Here is the SQL statment
for
the query that i've been trying to run:

SELECT *
FROM File Notes Query
WHERE noteid in (select top 3 noteid from [file notes query] S where
S.namefile = [file notes query].[namefile] order by time DESC);

THIS DOESNT WORK!!

Ok, the next step that I took is to adapt the following code statement
found
on
http://support.microsoft.com/default.aspx?scid=kb;en-us;210039

Function NthInGroup(GroupID, N)
' Returns the Nth Item in GroupID for use as a Top N per group
' query criteria.
Static LastGroupId, LastNthInGroup
Dim ItemName, GroupIDName, GDC, SearchTable
Dim SQL As String, rs As Recordset, db As DATABASE

If (LastGroupId = GroupID) Then
' Returned saved result if function is called with the
' same GroupID more than once in a row.
NthInGroup = LastNthInGroup
Else
' Set the SQL statement parameters. These are the only items
' that need to be customized in this function.
' Set to Item field name.
ItemName = "OrderDate"
' Set to Group ID field name.
GroupIDName = "CustomerID"
' GroupID Delimiter Character:
' For Text use "'", Date "#", Numeric ""
GDC = "'"
' Set to search table.
SearchTable = "Orders"
' Build a Top N SQL statement dynamically given N and
' GroupID as parameters. Note that the sort is by the
' item in descending order, in order to get the Top N
' largest items.
SQL = "Select Top " & N & " [" & ItemName & "] "
SQL = SQL & "From [" & SearchTable & "] "
SQL = SQL & "Where [" & GroupIDName & "]=" & GDC & GroupID & GDC & " "
SQL = SQL & "Order By [" & ItemName & "] Desc"
' Open up recordset on Top N SQL statement and read the
' last record to get the smallest item in the Top N.
Set db = CurrentDb()
Set rs = db.OpenRecordset(SQL)
If (rs.BOF And rs.EOF) Then
' No matches found, return a null.
LastNthInGroup = Null
LastGroupId = GroupID
NthInGroup = LastNthInGroup
Else
' Return the smallest Top N item in the group.
rs.MoveLast
LastNthInGroup = rs(ItemName)
LastGroupId = GroupID
NthInGroup = LastNthInGroup
End If
End If

End Function


The problem with this is that I haven't written code in about 5 years and
I
was never really that good at it. I keep getting error messages when I
try
to run this. With the line: Set rs = db.OpenRecordset(SQL) I get an
error
message saying that I have open parameters. I have no idea how to fix it.

I know that my skill level isn't up there when it comes to code. So I
would
really like to have some solution that would enable me to not have to use
code (wishful thinking I know)

Another option that I thought might work would be to rank that notes. I
could give the oldest note the ranking of 1 and so on and so forth, and
then
run a query that pulls the last 3 notes. But I think that I'll have the
same
problem as before. If I can't pull notes based on a decending date, how
will
I be able to pull it with a number?

Any suggestions on this problem would be very helpful.

 

[Guest]

Actually, I copied the wrong statement. I was using TIMENOTE instead of
time. And when I tired it with all the brackets I still got the same problem.
SELECT *
FROM [File Notes Query]
WHERE noteid in (select top 3 S.noteid from [file notes query] S where
S.namefile = [file notes query].[namefile] order by S.[timenote] DESC);

You need square brackets around the query name itself and a few other items:

SELECT *
FROM [File Notes Query]
WHERE noteid in (select top 3 S.noteid from [file notes query] S where
S.namefile = [file notes query].[namefile] order by S.[time] DESC);


Also, do not use Time as the name of a field. It and many other words are
reserved words in ACCESS, and can create serious
confusion for ACCESS and Jet. See these Knowledge Base articles for more
information:

List of reserved words in Access 2002 and Access 2003
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

List of Microsoft Jet 4.0 reserved words
http://support.microsoft.com/?id=321266

Special characters that you must avoid when you work with Access
databases
http://support.microsoft.com/?id=826763


--

Ken Snell
<MS ACCESS MVP>

Ok, let's try this one more time.
The database that I'm working with is for a law firm and it has records of
all of the cases (filename) that they are working on and all of the notes
that are related to that case. What I need is a query/table/report
ANYTHING
that will list the last three notes made on each file. I have tried using
(thanks to the help of this discussion borad) subqueries to complete this.
The problem is that it freezes my computer. Well, to be accurate, when I
switch to Task Manager my CPU is running at 100% capacity, and the program
fluctuates between responding and not responding. I'll let the query sit
for
hours, and it still never returns the results. Here is the SQL statment
for
the query that i've been trying to run:

SELECT *
FROM File Notes Query
WHERE noteid in (select top 3 noteid from [file notes query] S where
S.namefile = [file notes query].[namefile] order by time DESC);

THIS DOESNT WORK!!

Ok, the next step that I took is to adapt the following code statement
found
on
http://support.microsoft.com/default.aspx?scid=kb;en-us;210039

Function NthInGroup(GroupID, N)
' Returns the Nth Item in GroupID for use as a Top N per group
' query criteria.
Static LastGroupId, LastNthInGroup
Dim ItemName, GroupIDName, GDC, SearchTable
Dim SQL As String, rs As Recordset, db As DATABASE

If (LastGroupId = GroupID) Then
' Returned saved result if function is called with the
' same GroupID more than once in a row.
NthInGroup = LastNthInGroup
Else
' Set the SQL statement parameters. These are the only items
' that need to be customized in this function.
' Set to Item field name.
ItemName = "OrderDate"
' Set to Group ID field name.
GroupIDName = "CustomerID"
' GroupID Delimiter Character:
' For Text use "'", Date "#", Numeric ""
GDC = "'"
' Set to search table.
SearchTable = "Orders"
' Build a Top N SQL statement dynamically given N and
' GroupID as parameters. Note that the sort is by the
' item in descending order, in order to get the Top N
' largest items.
SQL = "Select Top " & N & " [" & ItemName & "] "
SQL = SQL & "From [" & SearchTable & "] "
SQL = SQL & "Where [" & GroupIDName & "]=" & GDC & GroupID & GDC & " "
SQL = SQL & "Order By [" & ItemName & "] Desc"
' Open up recordset on Top N SQL statement and read the
' last record to get the smallest item in the Top N.
Set db = CurrentDb()
Set rs = db.OpenRecordset(SQL)
If (rs.BOF And rs.EOF) Then
' No matches found, return a null.
LastNthInGroup = Null
LastGroupId = GroupID
NthInGroup = LastNthInGroup
Else
' Return the smallest Top N item in the group.
rs.MoveLast
LastNthInGroup = rs(ItemName)
LastGroupId = GroupID
NthInGroup = LastNthInGroup
End If
End If

End Function


The problem with this is that I haven't written code in about 5 years and
I
was never really that good at it. I keep getting error messages when I
try
to run this. With the line: Set rs = db.OpenRecordset(SQL) I get an
error
message saying that I have open parameters. I have no idea how to fix it.

I know that my skill level isn't up there when it comes to code. So I
would
really like to have some solution that would enable me to not have to use
code (wishful thinking I know)

Another option that I thought might work would be to rank that notes. I
could give the oldest note the ranking of 1 and so on and so forth, and
then
run a query that pulls the last 3 notes. But I think that I'll have the
same
problem as before. If I can't pull notes based on a decending date, how
will
I be able to pull it with a number?

Any suggestions on this problem would be very helpful.

 

[Ken Snell [MVP]]

Let's define what you mean by "doesn't work". Do you get the wrong data? do
you get no data? What data type is timenote field? is noteid the primary key
of a table? What is the SQL statement of file notes query?

Let's also try this very slight modification:

SELECT T.*
FROM [File Notes Query] AS T
WHERE T.noteid in (select top 3 S.noteid from [file notes query] S where
S.namefile = T.[namefile] order by S.[timenote] DESC);

--

Ken Snell
<MS ACCESS MVP>

Actually, I copied the wrong statement. I was using TIMENOTE instead of
time. And when I tired it with all the brackets I still got the same
problem.
SELECT *
FROM [File Notes Query]
WHERE noteid in (select top 3 S.noteid from [file notes query] S where
S.namefile = [file notes query].[namefile] order by S.[timenote] DESC);

You need square brackets around the query name itself and a few other
items:

SELECT *
FROM [File Notes Query]
WHERE noteid in (select top 3 S.noteid from [file notes query] S where
S.namefile = [file notes query].[namefile] order by S.[time] DESC);


Also, do not use Time as the name of a field. It and many other words are
reserved words in ACCESS, and can create serious
confusion for ACCESS and Jet. See these Knowledge Base articles for more
information:

List of reserved words in Access 2002 and Access 2003
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

List of Microsoft Jet 4.0 reserved words
http://support.microsoft.com/?id=321266

Special characters that you must avoid when you work with Access
databases
http://support.microsoft.com/?id=826763


--

Ken Snell
<MS ACCESS MVP>

Ok, let's try this one more time.
The database that I'm working with is for a law firm and it has records
of
all of the cases (filename) that they are working on and all of the
notes
that are related to that case. What I need is a query/table/report
ANYTHING
that will list the last three notes made on each file. I have tried
using
(thanks to the help of this discussion borad) subqueries to complete
this.
The problem is that it freezes my computer. Well, to be accurate, when
I
switch to Task Manager my CPU is running at 100% capacity, and the
program
fluctuates between responding and not responding. I'll let the query
sit
for
hours, and it still never returns the results. Here is the SQL
statment
for
the query that i've been trying to run:

SELECT *
FROM File Notes Query
WHERE noteid in (select top 3 noteid from [file notes query] S where
S.namefile = [file notes query].[namefile] order by time DESC);

THIS DOESNT WORK!!

Ok, the next step that I took is to adapt the following code statement
found
on
http://support.microsoft.com/default.aspx?scid=kb;en-us;210039

Function NthInGroup(GroupID, N)
' Returns the Nth Item in GroupID for use as a Top N per group
' query criteria.
Static LastGroupId, LastNthInGroup
Dim ItemName, GroupIDName, GDC, SearchTable
Dim SQL As String, rs As Recordset, db As DATABASE

If (LastGroupId = GroupID) Then
' Returned saved result if function is called with the
' same GroupID more than once in a row.
NthInGroup = LastNthInGroup
Else
' Set the SQL statement parameters. These are the only items
' that need to be customized in this function.
' Set to Item field name.
ItemName = "OrderDate"
' Set to Group ID field name.
GroupIDName = "CustomerID"
' GroupID Delimiter Character:
' For Text use "'", Date "#", Numeric ""
GDC = "'"
' Set to search table.
SearchTable = "Orders"
' Build a Top N SQL statement dynamically given N and
' GroupID as parameters. Note that the sort is by the
' item in descending order, in order to get the Top N
' largest items.
SQL = "Select Top " & N & " [" & ItemName & "] "
SQL = SQL & "From [" & SearchTable & "] "
SQL = SQL & "Where [" & GroupIDName & "]=" & GDC & GroupID & GDC & " "
SQL = SQL & "Order By [" & ItemName & "] Desc"
' Open up recordset on Top N SQL statement and read the
' last record to get the smallest item in the Top N.
Set db = CurrentDb()
Set rs = db.OpenRecordset(SQL)
If (rs.BOF And rs.EOF) Then
' No matches found, return a null.
LastNthInGroup = Null
LastGroupId = GroupID
NthInGroup = LastNthInGroup
Else
' Return the smallest Top N item in the group.
rs.MoveLast
LastNthInGroup = rs(ItemName)
LastGroupId = GroupID
NthInGroup = LastNthInGroup
End If
End If

End Function


The problem with this is that I haven't written code in about 5 years
and
I
was never really that good at it. I keep getting error messages when I
try
to run this. With the line: Set rs = db.OpenRecordset(SQL) I get an
error
message saying that I have open parameters. I have no idea how to fix
it.

I know that my skill level isn't up there when it comes to code. So I
would
really like to have some solution that would enable me to not have to
use
code (wishful thinking I know)

Another option that I thought might work would be to rank that notes.
I
could give the oldest note the ranking of 1 and so on and so forth, and
then
run a query that pulls the last 3 notes. But I think that I'll have
the
same
problem as before. If I can't pull notes based on a decending date,
how
will
I be able to pull it with a number?

Any suggestions on this problem would be very helpful.

 

[Guest]

For some reason my database is too big. I have over 100,00 notes and
hundreds of cases. Whenever I run the query my CPU jumps to 100% Usage and I
fluctuate between Responding and Not Responding. This afternoon I created a
dummy database, called practice. I wrote a maketable query that transfered
all the data from file notes query to a table called AllNotes in the new
database. I then ran the query and got the top three notes that I need. I
then created another make table query in the practice database and created a
new table in my original database, called Three notes. I then used this
table to generate a report. I used this report as a subreport in the report
calle File Facts. So I basically solved the problem, but I have issues.
This is way to complicated. I'm not going to be the one who is running this
report. Nancy, the person running the report, is very skitish around Access.
She doesnt want to have to run the queries to make the report run. I know
that I can't leave it like this. I would love to be able to run the simple
subquery in the orginal database but I can't seem to make it work. I have a
meeting with one of the IT people next week to see if there is something that
he can do.

Let's define what you mean by "doesn't work". Do you get the wrong data? do
you get no data? What data type is timenote field? is noteid the primary key
of a table? What is the SQL statement of file notes query?

Let's also try this very slight modification:

SELECT T.*
FROM [File Notes Query] AS T
WHERE T.noteid in (select top 3 S.noteid from [file notes query] S where
S.namefile = T.[namefile] order by S.[timenote] DESC);

--

Ken Snell
<MS ACCESS MVP>

Actually, I copied the wrong statement. I was using TIMENOTE instead of
time. And when I tired it with all the brackets I still got the same
problem.
SELECT *
FROM [File Notes Query]
WHERE noteid in (select top 3 S.noteid from [file notes query] S where
S.namefile = [file notes query].[namefile] order by S.[timenote] DESC);

You need square brackets around the query name itself and a few other
items:

SELECT *
FROM [File Notes Query]
WHERE noteid in (select top 3 S.noteid from [file notes query] S where
S.namefile = [file notes query].[namefile] order by S.[time] DESC);


Also, do not use Time as the name of a field. It and many other words are
reserved words in ACCESS, and can create serious
confusion for ACCESS and Jet. See these Knowledge Base articles for more
information:

List of reserved words in Access 2002 and Access 2003
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

List of Microsoft Jet 4.0 reserved words
http://support.microsoft.com/?id=321266

Special characters that you must avoid when you work with Access
databases
http://support.microsoft.com/?id=826763


--

Ken Snell
<MS ACCESS MVP>



Ok, let's try this one more time.
The database that I'm working with is for a law firm and it has records
of
all of the cases (filename) that they are working on and all of the
notes
that are related to that case. What I need is a query/table/report
ANYTHING
that will list the last three notes made on each file. I have tried
using
(thanks to the help of this discussion borad) subqueries to complete
this.
The problem is that it freezes my computer. Well, to be accurate, when
I
switch to Task Manager my CPU is running at 100% capacity, and the
program
fluctuates between responding and not responding. I'll let the query
sit
for
hours, and it still never returns the results. Here is the SQL
statment
for
the query that i've been trying to run:

SELECT *
FROM File Notes Query
WHERE noteid in (select top 3 noteid from [file notes query] S where
S.namefile = [file notes query].[namefile] order by time DESC);

THIS DOESNT WORK!!

Ok, the next step that I took is to adapt the following code statement
found
on
http://support.microsoft.com/default.aspx?scid=kb;en-us;210039

Function NthInGroup(GroupID, N)
' Returns the Nth Item in GroupID for use as a Top N per group
' query criteria.
Static LastGroupId, LastNthInGroup
Dim ItemName, GroupIDName, GDC, SearchTable
Dim SQL As String, rs As Recordset, db As DATABASE

If (LastGroupId = GroupID) Then
' Returned saved result if function is called with the
' same GroupID more than once in a row.
NthInGroup = LastNthInGroup
Else
' Set the SQL statement parameters. These are the only items
' that need to be customized in this function.
' Set to Item field name.
ItemName = "OrderDate"
' Set to Group ID field name.
GroupIDName = "CustomerID"
' GroupID Delimiter Character:
' For Text use "'", Date "#", Numeric ""
GDC = "'"
' Set to search table.
SearchTable = "Orders"
' Build a Top N SQL statement dynamically given N and
' GroupID as parameters. Note that the sort is by the
' item in descending order, in order to get the Top N
' largest items.
SQL = "Select Top " & N & " [" & ItemName & "] "
SQL = SQL & "From [" & SearchTable & "] "
SQL = SQL & "Where [" & GroupIDName & "]=" & GDC & GroupID & GDC & " "
SQL = SQL & "Order By [" & ItemName & "] Desc"
' Open up recordset on Top N SQL statement and read the
' last record to get the smallest item in the Top N.
Set db = CurrentDb()
Set rs = db.OpenRecordset(SQL)
If (rs.BOF And rs.EOF) Then
' No matches found, return a null.
LastNthInGroup = Null
LastGroupId = GroupID
NthInGroup = LastNthInGroup
Else
' Return the smallest Top N item in the group.
rs.MoveLast
LastNthInGroup = rs(ItemName)
LastGroupId = GroupID
NthInGroup = LastNthInGroup
End If
End If

End Function


The problem with this is that I haven't written code in about 5 years
and
I
was never really that good at it. I keep getting error messages when I
try
to run this. With the line: Set rs = db.OpenRecordset(SQL) I get an
error
message saying that I have open parameters. I have no idea how to fix
it.

I know that my skill level isn't up there when it comes to code. So I
would
really like to have some solution that would enable me to not have to
use
code (wishful thinking I know)

Another option that I thought might work would be to rank that notes.
I
could give the oldest note the ranking of 1 and so on and so forth, and
then
run a query that pulls the last 3 notes. But I think that I'll have
the
same
problem as before. If I can't pull notes based on a decending date,
how
will
I be able to pull it with a number?

Any suggestions on this problem would be very helpful.

 

[Ken Snell [MVP]]

Sometimes, when you have a lot of records and there are not good indices on
the records, using a subquery can take a lot of resources and time. Two ways
around that are

(1) to save the subquery as its own query, and then use that query as a
source table in the main query;

(2) to use VBA code to create a temporary table into which the
subquery's data are put (with indices on the appropriate fields in that
temporary table), and then use the table as a source table in the main
query, and then delete the temporary table. (Alternatively, one can have the
table there permanently, delete all records from it, run an append query
that gets the subquery's results and writes the data into the table, run the
main query, and then delete the records from the table.)

It's possible to automate (2) with some programming.


--

Ken Snell
<MS ACCESS MVP>

For some reason my database is too big. I have over 100,00 notes and
hundreds of cases. Whenever I run the query my CPU jumps to 100% Usage
and I
fluctuate between Responding and Not Responding. This afternoon I created
a
dummy database, called practice. I wrote a maketable query that
transfered
all the data from file notes query to a table called AllNotes in the new
database. I then ran the query and got the top three notes that I need.
I
then created another make table query in the practice database and created
a
new table in my original database, called Three notes. I then used this
table to generate a report. I used this report as a subreport in the
report
calle File Facts. So I basically solved the problem, but I have issues.
This is way to complicated. I'm not going to be the one who is running
this
report. Nancy, the person running the report, is very skitish around
Access.
She doesnt want to have to run the queries to make the report run. I
know
that I can't leave it like this. I would love to be able to run the
simple
subquery in the orginal database but I can't seem to make it work. I have
a
meeting with one of the IT people next week to see if there is something
that
he can do.

Let's define what you mean by "doesn't work". Do you get the wrong data?
do
you get no data? What data type is timenote field? is noteid the primary
key
of a table? What is the SQL statement of file notes query?

Let's also try this very slight modification:

SELECT T.*
FROM [File Notes Query] AS T
WHERE T.noteid in (select top 3 S.noteid from [file notes query] S where
S.namefile = T.[namefile] order by S.[timenote] DESC);

--

Ken Snell
<MS ACCESS MVP>

Actually, I copied the wrong statement. I was using TIMENOTE instead
of
time. And when I tired it with all the brackets I still got the same
problem.
SELECT *
FROM [File Notes Query]
WHERE noteid in (select top 3 S.noteid from [file notes query] S where
S.namefile = [file notes query].[namefile] order by S.[timenote] DESC);

:

You need square brackets around the query name itself and a few other
items:

SELECT *
FROM [File Notes Query]
WHERE noteid in (select top 3 S.noteid from [file notes query] S where
S.namefile = [file notes query].[namefile] order by S.[time] DESC);


Also, do not use Time as the name of a field. It and many other words
are
reserved words in ACCESS, and can create serious
confusion for ACCESS and Jet. See these Knowledge Base articles for
more
information:

List of reserved words in Access 2002 and Access 2003
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

List of Microsoft Jet 4.0 reserved words
http://support.microsoft.com/?id=321266

Special characters that you must avoid when you work with Access
databases
http://support.microsoft.com/?id=826763


--

Ken Snell
<MS ACCESS MVP>



Ok, let's try this one more time.
The database that I'm working with is for a law firm and it has
records
of
all of the cases (filename) that they are working on and all of the
notes
that are related to that case. What I need is a query/table/report
ANYTHING
that will list the last three notes made on each file. I have tried
using
(thanks to the help of this discussion borad) subqueries to complete
this.
The problem is that it freezes my computer. Well, to be accurate,
when
I
switch to Task Manager my CPU is running at 100% capacity, and the
program
fluctuates between responding and not responding. I'll let the
query
sit
for
hours, and it still never returns the results. Here is the SQL
statment
for
the query that i've been trying to run:

SELECT *
FROM File Notes Query
WHERE noteid in (select top 3 noteid from [file notes query] S where
S.namefile = [file notes query].[namefile] order by time DESC);

THIS DOESNT WORK!!

Ok, the next step that I took is to adapt the following code
statement
found
on
http://support.microsoft.com/default.aspx?scid=kb;en-us;210039

Function NthInGroup(GroupID, N)
' Returns the Nth Item in GroupID for use as a Top N per group
' query criteria.
Static LastGroupId, LastNthInGroup
Dim ItemName, GroupIDName, GDC, SearchTable
Dim SQL As String, rs As Recordset, db As DATABASE

If (LastGroupId = GroupID) Then
' Returned saved result if function is called with the
' same GroupID more than once in a row.
NthInGroup = LastNthInGroup
Else
' Set the SQL statement parameters. These are the only items
' that need to be customized in this function.
' Set to Item field name.
ItemName = "OrderDate"
' Set to Group ID field name.
GroupIDName = "CustomerID"
' GroupID Delimiter Character:
' For Text use "'", Date "#", Numeric ""
GDC = "'"
' Set to search table.
SearchTable = "Orders"
' Build a Top N SQL statement dynamically given N and
' GroupID as parameters. Note that the sort is by the
' item in descending order, in order to get the Top N
' largest items.
SQL = "Select Top " & N & " [" & ItemName & "] "
SQL = SQL & "From [" & SearchTable & "] "
SQL = SQL & "Where [" & GroupIDName & "]=" & GDC & GroupID & GDC &
" "
SQL = SQL & "Order By [" & ItemName & "] Desc"
' Open up recordset on Top N SQL statement and read the
' last record to get the smallest item in the Top N.
Set db = CurrentDb()
Set rs = db.OpenRecordset(SQL)
If (rs.BOF And rs.EOF) Then
' No matches found, return a null.
LastNthInGroup = Null
LastGroupId = GroupID
NthInGroup = LastNthInGroup
Else
' Return the smallest Top N item in the group.
rs.MoveLast
LastNthInGroup = rs(ItemName)
LastGroupId = GroupID
NthInGroup = LastNthInGroup
End If
End If

End Function


The problem with this is that I haven't written code in about 5
years
and
I
was never really that good at it. I keep getting error messages
when I
try
to run this. With the line: Set rs = db.OpenRecordset(SQL) I get
an
error
message saying that I have open parameters. I have no idea how to
fix
it.

I know that my skill level isn't up there when it comes to code. So
I
would
really like to have some solution that would enable me to not have
to
use
code (wishful thinking I know)

Another option that I thought might work would be to rank that
notes.
I
could give the oldest note the ranking of 1 and so on and so forth,
and
then
run a query that pulls the last 3 notes. But I think that I'll have
the
same
problem as before. If I can't pull notes based on a decending date,
how
will
I be able to pull it with a number?

Any suggestions on this problem would be very helpful.