[AccessD] Find nearest query troubles

Hi Chester,

Below are two structures for the same logic. The logic is first, get the
appropriate Date Time from the temperature table (that is, the maximum
Date Time less than the pressure table entry); then, use that Date Time
to find the appropriate temperature in the temperature table.

First structure, all in a single query using a sub query. This might
cause you problems as Access saves/compacts/etc. If so, use the second
method with two queries.

SELECT PropTempDateTime.Date, [tbl temperature].[Date Time], [tbl
temperature].temp
FROM [tbl temperature]
INNER JOIN (SELECT  dbo_S_CO2_System_Pressures.Date, max([tbl
temperature].[Date Time]) as Date_Time
                     FROM dbo_S_CO2_System_Pressures
                     INNER JOIN [tbl temperature] ON
dbo_S_CO2_System_Pressures.Date >= [tbl temperature].[Date Time]
                     GROUP BY dbo_S_CO2_System_Pressures.Date)
PropTempDateTime
ON [tbl temperature].[Date Time] = PropTempDateTime.Date_Time

Second structure, write a separate query for the sub query above, call
it qryProperTempDateTime.

SELECT dbo_S_CO2_System_Pressures.Date, max([tbl temperature].[Date
Time]) AS Date_Time
FROM dbo_S_CO2_System_Pressures INNER JOIN [tbl temperature] ON
dbo_S_CO2_System_Pressures.Date>=[tbl temperature].[Date Time]
GROUP BY dbo_S_CO2_System_Pressures.Date;

Then, use qryProperTempDateTime in your final query:

SELECT qryProperTempDateTime.Date, [tbl temperature].[Date Time], [tbl
temperature].temp
FROM [tbl temperature] INNER JOIN qryProperTempDateTime ON [tbl
temperature].[Date Time] = qryProperTempDateTime.Date_Time;

Hope this helps,
Fred

On 6/22/2010 3:07 PM, Kaup, Chester wrote:
> I have one table that has a date time column and one field of data (PSI), I have a second table that has a date time column and one field of data (temperature). The date time fields in the two tables to not match. I need to find the nearest date time in the second table that is not greater then the time date in the first table for each record and extract the temperature field from the second table. I have attempted to write a query but I get the same temperature for each date time in table one. Here is what I have. I think I have something turned around here. Thanks for looking.
>
> SELECT dbo_S_CO2_System_Pressures.Date, dbo_S_CO2_System_Pressures.PSI, (Select Min([Temp]) from [tbl Temperature]
> where [tbl temperature].[Date Time]< dbo_S_CO2_System_Pressures.Date) 
> AS Test
> FROM dbo_S_CO2_System_Pressures
> WHERE (((dbo_S_CO2_System_Pressures.Date)>#6/1/2010#))
> ORDER BY dbo_S_CO2_System_Pressures.Date;
>
>
>
> Chester Kaup
> Engineering Technician
> Kinder Morgan CO2 Company, LLP
> Office (432) 688-3797
> FAX (432) 688-3799
>
>  
> No trees were killed in the sending of this message. However a large number of electrons were terribly inconvenienced.
>
>
>