Top three notes

[Guest]

I'm having problems sorting some information. The database I'm working with
lists every single note associated with a file name. There are many
different file names. All this information is listed in one query. What I
am wanting is to be able to see the last three notes for EACH file name.
Each note is stamped with a time and date, and these two fields are combined
to create a "time" field. I have tried using the "SELECT TOP 3" function in
the SQL statement but this only generates the last three notes every made,
not the last three notes for each file name. The only way that I can view
the last three notes for a specific file name is if I limit the filename
field to only that specific file name. I don't want to have to create a
query for every single file because there are hundred. PLEASE HELP ME!!

 

[James Hahn]

You need a parameterized query where the filename is the parameter. Then you
call the query and pass it the filename (the parameter), and the query
returns the three notes for that filename.

 

[Guest]

I'm still not sure that I understand. I'm not sure how to create a
parameterized query. I know how to create a parameter query where I fill in
the parameter, but I didn't realize that you can create a query that selects
the parameters for you. And to be honest, I don't know how to accomplish
that task or how to pass that information on to another query. Sorry to be
such a bother, I'm still sort of new at working with accesss.

 

[James Hahn]

If you create a parameterized query and then execute it on its own, it will
ask you for the parameter value (the name of the file, in this case). You
would use this procedure to test that you can return the top three notes for
a filename. I presume this is what you mean when you say you know how to
create a parameter query where I fill in the parameter. In this case the
parameter value that you enter at the prompt is the criteria that controls
the records that get selected.

The query you need for the report will look exactly the same, except that
the source of data for the criteria value won't be entered in the dialogue,
but will be the filename being processed within the current record. So your
SELECT TOP 3 function will operate not on the notes table, but on the subset
of that table that includes only those notes records that meet the selection
criteria based on the filename parameter.
--