returning multiple values in a MAX qry

[Guest]

i have done a little research in to how to find the max of something that is
related to another table. i have a compressor table that has a running hours
table related to it. i need to find the max running hours

Compressor
compressorID
SerialNumber
etc...

RunningHours
RunninghoursID
Date
HoursAtDate

this allows us to have a history of the running hours. on my forms i want to
show the most current running hours entered, but the query i am setting up to
gather the data for the sub form is showing 2 records for one compressorID

here is the 2 queries i have made.

SELECT subtblRunningHours.CompressorID, Max(subtblRunningHours.Date) AS
MaxOfDate
FROM subtblRunningHours
GROUP BY subtblRunningHours.CompressorID;

this query is used to get the max date. this works fine.


SELECT subtblRunningHours.Date, subtblRunningHours.HoursAtDate,
subtblRunningHours.CompressorID
FROM subqryMaxHours INNER JOIN subtblRunningHours ON
subqryMaxHours.MaxOfDate = subtblRunningHours.Date
GROUP BY subtblRunningHours.Date, subtblRunningHours.HoursAtDate,
subtblRunningHours.CompressorID
ORDER BY subtblRunningHours.Date DESC;

this uses the other query to only pick the dates that match a maxdate to get
the hours.

if anyone can help please do.

 

[Guest]

I did not get the same problem with my sample data. I used your SQL and then
changed it as below.

SELECT subtblRunningHours.CompressorID, subtblRunningHours.Date,
subtblRunningHours.HoursAtDate
FROM subqryMaxHours INNER JOIN subtblRunningHours ON
subqryMaxHours.MaxOfDate = subtblRunningHours.Date
ORDER BY subtblRunningHours.CompressorID;

Do you have two meter readings on the same date?

Post your data that is giving duplicates.

 

[Guest]

Hello

thanks for helping out. no i dont have 2 entries on the same date. here is a
link to a picture of the data:

http://img463.imageshack.us/my.php?image=runninghoursproblemls4.jpg

this is looking through the tables that affect the data. the september 19
and october 25 both show up in the second query when i run it. i have no idea
why. i even renumbered the running hours table by creating a new autonumber
for the primary key.

i dont know what could be the problem.

 

[Guest]

Sorry, I do not go to unknown web sites. I meant for you to post a sample
here.

 

[Guest]

The fix is to join CompressorID in the second query.

 

[Guest]

Hello

sorry about the site, i have limited time to post over at my end, this is a
work project and i cant spend all my time typing out a diagram. i have found
imageshack to be very friendly, i hope you didnt have problems with it.

i have a query with the compressorID in it and thats is when the duplicate
shows. i will post more in a bit.

 

[Guest]

Try this --
SELECT subtblRunningHours.CompressorID, subtblRunningHours.Date,
subtblRunningHours.HoursAtDate
FROM subqryMaxHours INNER JOIN subtblRunningHours ON
(subqryMaxHours.CompressorID = subtblRunningHours.CompressorID) AND
(subqryMaxHours.MaxOfDate = subtblRunningHours.Date)
ORDER BY subtblRunningHours.CompressorID;

 

[Guest]

Hello

Thanks. i am going to have to study that some to figure out what its doing
differently. thanks. it works great!