ITKeyword,专注技术干货聚合推荐

注册 | 登录

ms access - SQL: Display associated value based on Min and Max values

itPublisher 分享于

2020腾讯云10周年活动,优惠非常大!(领取2860元代金券),
地址https://cloud.tencent.com/act/cps/redirect?redirect=1040

2020阿里云最低价产品入口,含代金券(新老用户有优惠),
地址https://www.aliyun.com/minisite/goods

I have tblDump which has ~52,000 records. It is a list of IDs with corresponding dosing times. The relevant variables are SubjectNumber, RECDATE, RECTIME, FoodType. Each patient doses twice a day, so there is a Min(RECTIME) and Max(RECTIME) per 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 Min(RecTIME) and Foodtype = 3 for Max(RECTIME), but I can't get the linguistics correct.

Resolved:

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.

qryMin:

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;

qryMax:

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;

qryCombo:

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;
sql ms-access max min having
|
  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 qryMin and qryMax with those statements, although there are additional shortcuts available). Also, RECDATE and RECTIME should 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.


相关阅读排行


相关内容推荐

最新文章

×

×

请激活账号

为了能正常使用评论、编辑功能及以后陆续为用户提供的其他产品,请激活账号。

您的注册邮箱: 修改

重新发送激活邮件 进入我的邮箱

如果您没有收到激活邮件,请注意检查垃圾箱。