ms access - SQL: Display associated value based on Min and Max values
I have tblDump which has ~52,000 records. It is a list of IDs with corresponding dosing times. The relevant variables are
FoodType. Each patient doses twice a day, so there is a
RECDATE. To display this, I have this so far:
SELECT tblDump.SubjectNumber, tblDump.RECDATE, Min(RECTIME) As MornDose, Max(RECTIME) As EveDose FROM tblDump GROUP BY tblDump.SubjectNumber, tblDump.RECDATE ORDER BY tblDump.SubjectNumber, tblDump.RECDATE Asc;
For the variable
FoodType, there are three possibilities: 1, 2 or 3. I need to print the Subjects whose
Min(RECTIME) is associated with a 1 and
Max(RECTIME) is associated with a 3, per day.
I was attempting to make the
HAVING statement such that the
Foodtype = 1 for
Foodtype = 3 for
Max(RECTIME), but I can't get the linguistics correct.
Okay, I'm sure there is a more efficient way to do this but this works. Hopefully this will help someone or someone can post a more efficient method. I used 3 queries, one for the Min Dose time, one for the Max dose time, and one to join them together.
SELECT a.SubjectNumber, a.RECDATE, First(a.colm AS MornDose), First(m.FoodType) FROM (SELECT SubjectNumber, RECDATE, MIN(RECTIME) as colm FROM tblDump GROUP BY SubjectNumber, RECDATE) AS a INNER JOIN tblDump AS m ON (a.colm=m.RECTIME) AND (a.RECDATE = m.RECDATE) AND (a.SubjectNumber = m.SubjectNumber) GROUP BY a.SubjectNumber, a.RECDATE ORDER BY a.SubjectNumber, a.RECDATE;
SELECT a.SubjectNumber, a.RECDATE, First(a.colm) AS MornDose, First(m.FoodType) FROM (SELECT SubjectNumber, RECDATE, MAX(RECTIME) AS colm FROM tblDump GROUP BY SubjectNumber, RECDATE) AS a INNER JOIN tblDump AS m ON (a.SubjectNumber = m.SubjectNumber) AND (a.RECDATE = m.RECDATE) AND (a.colm = m.RECTIME) GROUP BY a.SubjectNumber, a.RECDATE ORDER BY a.SubjectNumber, a.RECDATE;
sql ms-access max min having
SELECT qryMin.*, qryMax.* FROM qryMin INNER JOIN qryMax ON qryMin.SubjectNumber = qryMax.SubjectNumber WHERE qryMin.RECDATE = qryMax.RECDATE AND qryMin.FoodType = 1 AND qryMax.FoodType = 3;
this question edited Jul 3 '13 at 15:48 asked Jun 25 '13 at 16:32 user2520927 8 2 1 It's perfectly permissable to answer your own question, you know. And yes, this can be done in one query (if nothing else, by replacing those references to
qryMaxwith those statements, although there are additional shortcuts available). Also,
RECTIMEshould really be combined in one timestamp type, which is what you're dealing with. Although, this does make some of the analysis easier (given you're aggregating by day).... – Clockwork-Muse Jun 25 '13 at 17:28 Also, what RDBMS (and major version) do you have - there may be a better way to solve this. – Clockwork-Muse Jun 25 '13 at 17:44 @Clockwork-Muse I'm actually using access 2010. replacing the references to the queries with the statements, access is requesting that I use the "EXISTS" command which I am not familiar with – user2520927 Jun 25 '13 at 18:10
Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.
- 1java.sql.SQLException: Access denied for user 'root'@'localhost' (using password: YES)的一种解决方法
- 4解决：java.sql.SQLException: Access denied for user ''@'localhost' (using password: YES)
- 5VFP将图片或其他文件保存到SQL Server的Text类型字段或者Access的备注字段中，并可以还原或显示。