Max Query - Microsoft Access Forum - Computer Learning Zone


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Max Query

Mark McKechnie

2 years ago

Hi guys,

I'm having trouble with a query and this is the second time I've come up against the same type of problem and I'm stumped if I can work out a solution. I have below but I'm not confident in the solution or convinced its the best.

Basically I have a works ticket table (Jobs) which contains all of our works orders. Each record has various data including the machine centre it's assigned to, and a if its due for production it is assigned a priority for the specific machine. I want to be able to make a query to group by the machine id and the highest priority - this is straightforward like i have done in the query on the left. However I want to be able to show the works id too and can't seem to fathom how to get without it just returning all records since the ids are unique. The only way I've been able to hobble something together is to make a second query with the two fields linked. I can't help think there must be a better solution and I'm hoping it's one of those how did I not see that moments.

Ps. Please forgive the field names - spaces and spelling errors... I wish I could fix this but it's one of my earliest tables on what is now a sprawling db and it would take too much effort to rectify.

Many thanks for any suggestions.

	Mark McKechnie OP @Reply 2 years ago

	John Davy @Reply 2 years ago
If making a second query works, there is nothing wrong with that. John

	Kim Wong @Reply 2 years ago
Richard has a good tutorial about using a second query. What you are trying to do sounds like something I was trying to figure out. Use of second query worked beautifully for me. Here's the link https://www.youtube.com/watch?v=n42BwlhgBQA

Kevin Yip @Reply

2 years ago

Hi Mark, if by "better," you mean instead of using two queries, you want to use just one, there is a way to do that. In the query screen for MachineTopJobQ, add the Jobs table to query designer *again*. You will have TWO Jobs table on the screen, with the second one called Jobs_1. Create links to it as you normally do. This is how you can use the Jobs table twice without having to use two queries.

Mark McKechnie OP @Reply

2 years ago

Thanks Kevin, I'll give that a try. I can't remember the situation was the first time I can up against a similar problem but for some reason I had it in my head that the links from the first lookup query could lead to potentially retreaving the wrong record in the second query. An example in the scenario might be if a record doesn't have a job priority removed correctly and we end up with two jobs for the same machine with priority 1. I came across this page below and was able to tidy it into one query, albeit with another built inside which I didn't realise you could do as my sql programming isn't too strong...

https://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View