• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 644
  • Last Modified:

Access query - "Top 1" record anomaly (?)

I just had a query question answered nicely, but it now created a different issue.
The query now outputs a "TOP 1" set of fields that are correct, and match a given Part_ID.

However, I want to be able to give these fields for EACH part on a project - So I was doing a LEFT JOIN between the parts table and the query table (which outputs a TOP 1  result).

Instead of giving the different answer for each part, it only output a single answer, for 1 record.

any ideas of how to fix?
Thanks, experts!
  • 3
  • 2
1 Solution
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
Check out the following MSKB article:

ACC: How to Create a Top N Values per Group Query

TOP 1 in the SQL statement will only yield you the single answer, as the command implies...  perhaps you could create several SQL statements, one for each part of your project and then use the UNION command to chain them together, each separate part having a 'TOP 1' in it?

For example:

SELECT TOP 1 * FROM tblDeals WHERE strSedol='0004659'
SELECT TOP 1 * FROM tblDeals WHERE strSedol='0009137'

Open in new window

Edit: Just seen JDettman's answer - go with his advice :o)
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
I just looked back and didn't understand the question correctly.

<<The query now outputs a "TOP 1" set of fields that are correct, and match a given Part_ID.>>

  Does it do it only for a single part, or is it doing a TOP 1 for each part within a group.

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

terpsichoreAuthor Commented:
It is outputting only the result for a single part.
This is the current Query, which is working OK for a single part:

SELECT TOP 1 PP.Part_ID, R.ExpDate, M.Description
FROM Project_Parts AS PP INNER JOIN (Project_Parts_Revenue AS R INNER JOIN PICK_PartMilestoneType AS M ON R.Milestone_ID = M.ID) ON PP.Part_ID = R.Part_ID
WHERE (((R.Complete)=False) AND ((PP.Canceled)=False))
ORDER BY PP.Part_ID, R.ExpDate, R.RevenueID;

I need to invoke this, as noted, across a range of Part_ID's...
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
You need to do this a bit differently as your TOP1 query is looking at all parts.  It's not filtering on Project at all.

1. What you want is the last part on a project where R.Complete=False AND PP.Canceled)=False right?

  So first, create a new query.    Add in Project_Parts and Project_Parts_Revenue tables.

 Now make it a GROUP BY (hit the "E" on the toolbar).  

 Pull down the project, part, r.Complete, PP.Canceled, R.ExpDate, and R.RevenueID

  Under the project column set the Total line to "GROUP BY".   Under ExpDate, sort ascending and put LAST on the total line.  Put LAST on the Part ID Column as well.

  Under the complete and Canceled colums, change the total Line to a WHERE condition.  You'll have to uncheck the output box for these.

  Now execute this.  You should have one row per project with the first part where canceled and complete columns are false.

 Save this.

 Now use this in your other query just as you were trying to do with the TOP1.

terpsichoreAuthor Commented:
many sincere thanks.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now