Query results change when I add a field

[phannah3]

First Post here... looks like a great forum.

Anyway, I am developing a Tools database for a Cleanroom. It will hopefully keep track of the tools that are used and the maintenance of each tool.
I have created a Tool Details table and a Tools Maintenance table with the data that I need to keep track of each tools and there Maintenance records.
I've created a query to get the Maintenance Record # (ID) the (Tool Name), Downtime End Date (dtEndDate), and (Tool Status).
I want to query the last record for that (Tool Name) and show the current status of the Tool (Tool Status)
I can get the result that i want without the Tool Status:
MaxOfID Tool_Name MaxOfdtEndDate
77 Plasmatherm 790 RIE 2/8/2012
215 Plasmatherm ICP 3/26/2012
270 Oxford HD-PECVD 10/5/2012
282 Stepper 10/9/2012
285 CHA Evaporator - DP 10/10/2012



But as soon as I bring in the (Tool Status) Field I get multiple records of some of the Tools:

MaxOfID Tool_Name MaxOfdtEndDate Tool Status
77 Plasmatherm 790 RIE 2/8/2012 In Service - Up
215 Plasmatherm ICP 3/26/2012 In Service - Up
266 Oxford HD-PECVD 11/18/2009 In Service - Up
267 CHA Evaporator - DP 9/17/2012 In Service - Up
270 Oxford HD-PECVD 10/5/2012 Not in Service - Down
282 Stepper 10/9/2012 Not in Service - Down
284 CHA Evaporator - DP 10/10/2012 In Bake Out
285 CHA Evaporator - DP In Maintenance



Any help would be appreciated.

Thanks!

p.s. i can't seem to get my results to look good on this forum....

 

[Peter Reid]

It looks like you're grouping on the additional field and getting unique results for each Tool_Name/'Tool Status' combination

 

[phannah3]

So How do i get the last record of each Tool which read the Tool Status from that record #?

I can't seem to get this one to work.

 

[Peter Reid]

You want the last status of each Tool? ie 'CHA Evaporator' is 'In Maintenance'?

If so, use the Last function on Tool Status in the same way you've used the Max function on ID

 

[phannah3]

I have gone through pretty much all the iterations of those functions on the Tool Status field, but it does not reflect the actual text in the Tool Status field for that record.
Some how the combination of reading the MaxofID and the MaxofdtEndDate gives me the wrong records for the Tool Status when I add the Tool Status to the query.
Is there a way to use the MaxofID record# to read the Tool Status field of that record?

p.s. the second query in the above post that has the mutiple records for some of the tools is showing the correct Tool Status for that record, but when I use the Max or First or Last function it reads a different record for the Tool Status
Thanks in advance for any help on this one.....