RE: returning multiple values in a MAX qry

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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

"DawnTreader" wrote:

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.

"KARL DEWEY" wrote:

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.


"DawnTreader" wrote:

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. :)
.

Relevant Pages

  • RE: returning multiple values in a MAX qry
    ... no i dont have 2 entries on the same date. ... and october 25 both show up in the second query when i run it. ... FROM subqryMaxHours INNER JOIN subtblRunningHours ON ... gather the data for the sub form is showing 2 records for one compressorID ...
    (microsoft.public.access.queries)
  • RE: returning multiple values in a MAX qry
    ... The fix is to join CompressorID in the second query. ... no i dont have 2 entries on the same date. ... FROM subqryMaxHours INNER JOIN subtblRunningHours ON ...
    (microsoft.public.access.queries)
  • Re: multi table multi database join?
    ... dont i have some kind of filter? ... sybase runns on one machine mssql on another, ... the data from the aggregate db could be used in a query to ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: What to do with a Null Value that is returned from a Select. ??
    ... but I dont seem to be able catch it properly. ... > SqlConnection myConnection = new SqlConnection ... > //Run Query to compare the Email Address ... > The HTML is simple and just contains a text box. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Employee Emergency
    ... I dont want to be a burden but i have another problem but this time it has ... > listbox/combobox) the order ID to match to an employee, then the query ... > step, i.e. first just the tables and the orderID filter, then the group ...
    (microsoft.public.access.queries)