Subqueries and Confusion

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

Guest

I have been told that a subquery will solve my problem. But the problem is
that I don't really know how to go about doing this. Ok, let me present my
situation:
I have a query named File Notes Query. This query pulls every note and time
the note was made on a case file for every case file that we have. I want to
be able to creat a query that will pull the latest THREE notes made on EACH
case file. I believe that I could do this by ranking the notes in a
parameter query. But I don't know that much about building expressions or
manipulating SQL statments. I really need help with the syntax of this
situation.
 
I've created a simplified version of the problem I'm having. Suppose you
have the following information in Table1
NoteID NameFile Note Time
1 Brittany asfjdhla 1/20/2005
2 Brittany dasdf 1/21/2005
3 Brittany sdfasdfad 1/22/2005
4 Brittany asfdadsfa 1/23/2005
5 Sarah 32981 1/25/2005
6 Sarah 489879 1/26/2005
7 Sarah 85/564 1/27/2005
8 Sarah adslfjowi 1/28/2005

I want a query that will produce the following results:
NoteID NameFile Note Time
2 Brittany dasdf 1/21/2005
3 Brittany sdfasdfad 1/22/2005
4 Brittany asfdadsfa 1/23/2005
6 Sarah 489879 1/26/2005
7 Sarah 85/564 1/27/2005
8 Sarah adslfjowi 1/28/2005

In other words I want to be able to see the three latest notes on the
namefile of both Brittany and Sarah.

I have been reading a lot of the postings on this site and am tring to use
the information provided by this statement:

Another method would be to use a subquery, something like (air sql):
SELECT *
FROM tblSales
WHERE ProductID IN (SELECT Top 5 ProductID FROM tblSales S WHERE
S.DistrictID = tblSales.DistrictID ORDER BY Profit DESC)
--
Duane Hookom
Microsoft Access MVP

But everytime I run the query I get ALL of the records back. I'm I not
entering the syntax correctly? I'm I not putting it in the right location?
The database that I'm actually working with is a lot more complex. I thought
I would use a practice database before I actually screwed up the real thing.
If you could show the me the syntax on the simple version I'm sure that I
could figure out how to do it on the complex version! Thanks to everyone
that gives this some thought. This is the toughest thing I've had to do in
Access! (so far!)
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The subquery is something like this in the main query's WHERE clause:

SELECT ...
FROM ...
WHERE noteid in (SELECT TOP 3 noteid FROM table_name
WHERE < criteria>
ORDER BY note_date DESC)

The ORDER BY clause in the subquery will put the row w/ the latest
note_date at the top of the result set.

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

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

iQA/AwUBQgkaCIechKqOuFEgEQKwawCePvOz18Ri5YfyHDJSqc7aP1wzUcIAoK3H
EBMQjzC++i6jAg8g9kFi5SZe
=STUY
-----END PGP SIGNATURE-----
 
Thanks for that information! That's going to be really helpful! The only
other question that I have is what sort of criteria would be appropriate for
this? I just don't know what the criteria would be
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The criteria would be whatever you want it to be, or you could just get
rid of the WHERE clause. It all depends on what you want. See the
Access Help article "Enter criteria to retrieve certain records" for
more info.

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

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

iQA/AwUBQgkjOYechKqOuFEgEQJkwgCcDLnHwiGAddv5b1gc/+e3CrGCcfgAoN0B
geZJGCy2t4sNwBp+8eQB4Ag/
=yoh/
-----END PGP SIGNATURE-----
 
THANK YOU, THANK YOU, THANK YOU!
I've finally figured it out! I've been working on this problem for over a
week and it's finally solved! Thank you so much for telling me where to put
that statement.
 
Back
Top