How can I select only one record per "person" per day with an inner join in a MS Access query?
I need to extract data from two tables: Neptune_FN_Analysis
and Neptune_prem
There will be 3 fields called readings_miu_id
(matched to person name or element #) ReadDate
, ReadTime
(all of which are in Neptune_FN_Analysis
). Some readings_miu_id
have a few ReadTime
for a few days, but I only want to pull out the "last time" for readings_miu_id
a day.
I need everyone readings_miu_id
that has a record date for the selected range, but only the last ReadTime
one entered for each record I pull.
My solution so far based on one table:
SELECT readings_miu_id, Reading, ReadDate, ReadTime, MIUwindow, SN, Noise, RSSI, OriginCol, ColID, Ownage
FROM analyzed AS A
WHERE ReadDate Between #4/21/2009# and #4/29/2009#
AND ReadTime=
(SELECT TOP 1 analyzed.ReadTime FROM analyzed
where analyzed.readings_miu_id = A.readings_miu_id
AND analyzed.ReadDate = A.ReadDate
ORDER BY analyzed.ReadTime DESC);
When I try to adapt this solution, I cannot do FROM [tableName] as A, INNER JOIN
because it gives me an error. The original code that my predecessor made (which I am trying to adapt / fix) looks like this:
SELECT readings_miu_id, Reading, ReadDate,Format([MIUtime],'hh:mm:ss') AS
ReadTime, MIUwindow, SN, Noise, RSSI, ColRSSI, MIURSSI, Firmware, CFGDate, FreqCorr,
Active, MeterType, OriginCol, ColID, Ownage, SiteID, PremID, Neptune_prem.prem_group1,
Neptune_prem.prem_group2, ReadID
INTO analyzed
FROM Neptune_FN_Analysis INNER JOIN
Neptune_prem ON Neptune_FN_Analysis.PremID = Neptune_prem.premice_id
WHERE SiteID = 36801 and ReadDate BETWEEN #04/21/09# AND #04/27/09#
and OriginCol = 'US 29' and ColID = 1 and ColID <> 0 and Active = 'Y'
a source to share
I don't quite understand what you are trying to do, but if you join a subquery that receives the MAX of the date, it can eliminate all records where the date was not the maximum
SELECT readings_miu_id, Reading, ReadDate, ReadTime, MIUwindow, SN,
Noise, RSSI, OriginCol, ColID, Ownage
FROM analyzed
INNER JOIN
(SELECT [whatever the id common to all like records is] as likeID, MAX(analyzed.ReadTime) as latestDate
FROM analyzed
GROUP BY likeID) AS maxDate ON analyzed.likeID=maxDate.likeID AND analyzed.latestDate = maxDate.latestDate
WHERE ReadDate Between #4/21/2009# and #4/29/2009#
change if necessary
a source to share
I would try something like this:
SELECT a.readings_miu_id, a.Reading, a.ReadDate, a.ReadTime, a.MIUwindow, a.SN, a.Noise, a.RSSI, a.OriginCol, a.ColID, a.Ownage
FROM analyzed AS A INNER JOIN
(SELECT max(ReadTime) as MaxReadTime,readings_miu_id FROM analyzed
WHERE ReadDate Between #4/21/2009# and #4/29/2009#
GROUP BY readings_miu_id) as B
on a.readings_miu_id = b.readings_miu_id and a.MaxReadTime = b.ReadTime
a source to share
SELECT
<your columns>
FROM
Neptune_FN_Analysis A1
INNER JOIN Neptune_prem ON
P.premice_id = A1.PremID
LEFT OUTER JOIN Neptune_FN_Analysis A2 ON
A2.readings_miu_id = A1.readings_miu_id AND
A2.ReadDate = A1.ReadDate AND
A2.ReadTime > A1.ReadTime
WHERE
A2.readings_miu_id IS NULL AND
<add any additional criteria here>
a source to share
I'm not sure what you mean by specifying " INNER JOIN
" this time. Other answers use a subquery, so here's an example using two INNER JOIN
and no subquery. Instead of fooling around with your scheme :) I use Northwind to return customers and their last order date:
SELECT C1.CustomerID, C1.CompanyName,
O1.OrderID, O1.OrderDate
FROM (Customers AS C1
INNER JOIN Orders AS O1
ON C1.CustomerID = O1.CustomerID)
INNER JOIN Orders AS O2
ON C1.CustomerID = O2.CustomerID
GROUP
BY C1.CustomerID, C1.CompanyName,
O1.OrderID, O1.OrderDate
HAVING O1.OrderDate = MAX(O2.OrderDate);
a source to share