SELECT TOP n -- looking for assistance
in [General]
|
Prev: rere
Next: Entering measurements in a field
From: Steve on 3 Apr 2010 19:29 Clif, Good luck with your project!!! Steve "Clif McIrvin" <clare.moe(a)nevergmail.com.invalid> wrote in message news:u9%23fn%2330KHA.260(a)TK2MSFTNGP05.phx.gbl... > Yeah, you got right back to where I started from. > > Appreciate you taking the time to clean up after yourself -- been there > myself. [My late boss was fond of saying that if you never did anything > wrong, you just weren't working hard enough!] > > Clif > > "Steve" <notmyemail(a)address.com> wrote in message > news:ubu%23Bi10KHA.3412(a)TK2MSFTNGP05.phx.gbl... >> Thinking more about this, this will not work. Please ignore. >> >> Steve >> >> >> "Steve" <notmyemail(a)address.com> wrote in message >> news:eZro3X10KHA.348(a)TK2MSFTNGP02.phx.gbl... >>> Add a calculated field yo your query: >>> WeekAssignedLot:DatePart("ww",[WeekEnding]) >>> Set the sort for this field to Descending. >>> Set the Top property to 2. >>> >>> Steve >>> santus(a)penn.com >>> >>> >>> "Clif McIrvin" <clare.moe(a)nevergmail.com.invalid> wrote in message >>> news:OBnHJm00KHA.2196(a)TK2MSFTNGP05.phx.gbl... >>>> Looking for help with a select query. >>>> >>>> I have a table containing a column: WeekEnding (Date/Time field). >>>> >>>> Each week will have a different number of rows. >>>> >>>> I'm trying to write a query that will return the rows from the most >>>> recent two weeks. I discovered that TOP 1 returns all the rows from the >>>> most recent week, but to my dismay discovered that TOP 2 also returns >>>> the same rows. On reflection, I believe that I understand why, which >>>> leads to my dilemma: how do I return the rows from the last two weeks, >>>> regardless of how many rows that might be? >>>> >>>> Here's what I have right now: >>>> >>>> SELECT TOP 8 [Mix Designs].[Mix ID], AssignedLots.LotNo, >>>> AssignedLots.WeekEnding >>>> FROM [Mix Designs] INNER JOIN (Plants INNER JOIN AssignedLots ON >>>> Plants.PlantID=AssignedLots.PlantID) ON [Mix >>>> Designs].MixID=AssignedLots.MixID >>>> ORDER BY AssignedLots.WeekEnding DESC; >>>> >>>> which returns the last three weeks: >>>> >>>> Mix ID, LotNo, WeekEnding >>>> 6PF, 10-040, 03-Apr-10 >>>> SMH, 10-042, 03-Apr-10 >>>> 7A, 10-039, 03-Apr-10 >>>> 3BF, 10-041, 03-Apr-10 >>>> 6PF, 10-037, 27-Mar-10 >>>> 7A, 10-036, 27-Mar-10 >>>> 3BF, 10-038, 27-Mar-10 >>>> 6PF, 10-035, 20-Mar-10 >>>> 3BF, 10-034, 20-Mar-10 >>>> 1PF, 10-033, 20-Mar-10 >>>> 7A, 10-032, 20-Mar-10 >>>> >>>> I'm going to use TOP 7, on the premise that any given week could have >>>> six rows, but seven rows is not likely. The downside is when there are >>>> only 2 or 3 rows in each week, causing the query to return three (or 4) >>>> weeks instead of two. >>>> >>>> Suggestions? >>>> >>>> Thanks in advance! >>>> >>>> Clif >>>> >>>> >>> >>> >> >> > > >
From: Clif McIrvin on 3 Apr 2010 23:37 Thanks for the additional explanation ... much appreciated. I'll particularly remember that recommendation for Allen Browne's site ... he's got lots of good stuff there. Otherwise, yes, you read between the lines pretty well! <smile> Clif "Hans Up" <hans.updyke(a)nospam.invalid> wrote in message news:u%23%23yXR40KHA.752(a)TK2MSFTNGP04.phx.gbl... > Clif McIrvin wrote: >> Hans, I like your lesson in using a subquery -- that's something I've >> not yet tried to get used to using. The concept is clear, and I >> notice that it is completely independant of today's date, which makes >> it a little more "idiot proof". > > Good. I wasn't clear whether you wanted the last 2 weeks which were > stored in the table or data from this week and the previous week. > They might not be the same. So went with the most recent 2 weeks on > file. > > BTW, you will discard "TOP 8" from your old query before joining in > qryLast2Weeks, right? I didn't think to mention that before. > > Also, look at Allen Browne's site when you're ready to delve further > into subqueries. He was clear discussions with examples. > >> In essence, if I do what you suggest, I'd be running three queries >> when I call this, correct? I suppose that's just a bit of arcane >> trivia that has little practical consequence in terms of actual >> performance that the user would ever notice. > > I don't know what to say about the 3 queries thing, Clif. The > database engine will figure out a query plan and ultimately handle it > as one complex "thing". > > Certainly it will be more challenging for Access than if you weren't > asking it to figure out which weeks to pull whenever it runs the > query. What I mean is it would be easier on Access if you were able to > define a simple WHERE clause: > > WHERE AssignedLots.WeekEnding = #2010/04/03# Or > AssignedLots.WeekEnding = #2010/03/27# > > Guess I would say don't worry about performance implications of this > one unless it's a problem. Sounds like you're already on that same > page. :-)
From: Mrs. Lori Andersen on 5 Apr 2010 17:20 "Clif McIrvin" <clare.moe(a)nevergmail.com.invalid> wrote in message news:OBnHJm00KHA.2196(a)TK2MSFTNGP05.phx.gbl... > Looking for help with a select query. > > I have a table containing a column: WeekEnding (Date/Time field). > > Each week will have a different number of rows. > > I'm trying to write a query that will return the rows from the most recent > two weeks. I discovered that TOP 1 returns all the rows from the most > recent week, but to my dismay discovered that TOP 2 also returns the same > rows. On reflection, I believe that I understand why, which leads to my > dilemma: how do I return the rows from the last two weeks, regardless of > how many rows that might be? > > Here's what I have right now: > > SELECT TOP 8 [Mix Designs].[Mix ID], AssignedLots.LotNo, > AssignedLots.WeekEnding > FROM [Mix Designs] INNER JOIN (Plants INNER JOIN AssignedLots ON > Plants.PlantID=AssignedLots.PlantID) ON [Mix > Designs].MixID=AssignedLots.MixID > ORDER BY AssignedLots.WeekEnding DESC; > > which returns the last three weeks: > > Mix ID, LotNo, WeekEnding > 6PF, 10-040, 03-Apr-10 > SMH, 10-042, 03-Apr-10 > 7A, 10-039, 03-Apr-10 > 3BF, 10-041, 03-Apr-10 > 6PF, 10-037, 27-Mar-10 > 7A, 10-036, 27-Mar-10 > 3BF, 10-038, 27-Mar-10 > 6PF, 10-035, 20-Mar-10 > 3BF, 10-034, 20-Mar-10 > 1PF, 10-033, 20-Mar-10 > 7A, 10-032, 20-Mar-10 > > I'm going to use TOP 7, on the premise that any given week could have six > rows, but seven rows is not likely. The downside is when there are only 2 > or 3 rows in each week, causing the query to return three (or 4) weeks > instead of two. > > Suggestions? > > Thanks in advance! > > Clif > >
From: Mrs. Lori Andersen on 5 Apr 2010 17:24 "Mrs. Lori Andersen" <lori.andersen05(a)yahoo.com> wrote in message news:... > > "Clif McIrvin" <clare.moe(a)nevergmail.com.invalid> wrote in message > news:OBnHJm00KHA.2196(a)TK2MSFTNGP05.phx.gbl... >> Looking for help with a select query. >> >> I have a table containing a column: WeekEnding (Date/Time field). >> >> Each week will have a different number of rows. >> >> I'm trying to write a query that will return the rows from the most >> recent two weeks. I discovered that TOP 1 returns all the rows from the >> most recent week, but to my dismay discovered that TOP 2 also returns the >> same rows. On reflection, I believe that I understand why, which leads >> to my dilemma: how do I return the rows from the last two weeks, >> regardless of how many rows that might be? >> >> Here's what I have right now: >> >> SELECT TOP 8 [Mix Designs].[Mix ID], AssignedLots.LotNo, >> AssignedLots.WeekEnding >> FROM [Mix Designs] INNER JOIN (Plants INNER JOIN AssignedLots ON >> Plants.PlantID=AssignedLots.PlantID) ON [Mix >> Designs].MixID=AssignedLots.MixID >> ORDER BY AssignedLots.WeekEnding DESC; >> >> which returns the last three weeks: >> >> Mix ID, LotNo, WeekEnding >> 6PF, 10-040, 03-Apr-10 >> SMH, 10-042, 03-Apr-10 >> 7A, 10-039, 03-Apr-10 >> 3BF, 10-041, 03-Apr-10 >> 6PF, 10-037, 27-Mar-10 >> 7A, 10-036, 27-Mar-10 >> 3BF, 10-038, 27-Mar-10 >> 6PF, 10-035, 20-Mar-10 >> 3BF, 10-034, 20-Mar-10 >> 1PF, 10-033, 20-Mar-10 >> 7A, 10-032, 20-Mar-10 >> >> I'm going to use TOP 7, on the premise that any given week could have six >> rows, but seven rows is not likely. The downside is when there are only 2 >> or 3 rows in each week, causing the query to return three (or 4) weeks >> instead of two. >> >> Suggestions? >> >> Thanks in advance! >> >> Clif >> >> >
From: KARL DEWEY on 5 Apr 2010 19:07
Try these two queries -- qryMaxWeek -- SELECT Max(AssignedLots.WeekEnding) AS MaxOfWeekEnding FROM AssignedLots; SELECT [Mix Designs].[Mix ID], AssignedLots.LotNo, AssignedLots.WeekEnding FROM [Mix Designs] INNER JOIN AssignedLots ON [Mix Designs].MixID = AssignedLots.MixID, qryMaxWeek WHERE (((AssignedLots.WeekEnding)=[MaxOfWeekEnding])) OR (((AssignedLots.WeekEnding)>DateAdd("d",-14,[MaxOfWeekEnding]))); AssignedLots.WeekEnding -- Build a little, test a little. "Mrs. Lori Andersen" wrote: > > "Mrs. Lori Andersen" <lori.andersen05(a)yahoo.com> wrote in message news:... > > > > "Clif McIrvin" <clare.moe(a)nevergmail.com.invalid> wrote in message > > news:OBnHJm00KHA.2196(a)TK2MSFTNGP05.phx.gbl... > >> Looking for help with a select query. > >> > >> I have a table containing a column: WeekEnding (Date/Time field). > >> > >> Each week will have a different number of rows. > >> > >> I'm trying to write a query that will return the rows from the most > >> recent two weeks. I discovered that TOP 1 returns all the rows from the > >> most recent week, but to my dismay discovered that TOP 2 also returns the > >> same rows. On reflection, I believe that I understand why, which leads > >> to my dilemma: how do I return the rows from the last two weeks, > >> regardless of how many rows that might be? > >> > >> Here's what I have right now: > >> > >> SELECT TOP 8 [Mix Designs].[Mix ID], AssignedLots.LotNo, > >> AssignedLots.WeekEnding > >> FROM [Mix Designs] INNER JOIN (Plants INNER JOIN AssignedLots ON > >> Plants.PlantID=AssignedLots.PlantID) ON [Mix > >> Designs].MixID=AssignedLots.MixID > >> ORDER BY AssignedLots.WeekEnding DESC; > >> > >> which returns the last three weeks: > >> > >> Mix ID, LotNo, WeekEnding > >> 6PF, 10-040, 03-Apr-10 > >> SMH, 10-042, 03-Apr-10 > >> 7A, 10-039, 03-Apr-10 > >> 3BF, 10-041, 03-Apr-10 > >> 6PF, 10-037, 27-Mar-10 > >> 7A, 10-036, 27-Mar-10 > >> 3BF, 10-038, 27-Mar-10 > >> 6PF, 10-035, 20-Mar-10 > >> 3BF, 10-034, 20-Mar-10 > >> 1PF, 10-033, 20-Mar-10 > >> 7A, 10-032, 20-Mar-10 > >> > >> I'm going to use TOP 7, on the premise that any given week could have six > >> rows, but seven rows is not likely. The downside is when there are only 2 > >> or 3 rows in each week, causing the query to return three (or 4) weeks > >> instead of two. > >> > >> Suggestions? > >> > >> Thanks in advance! > >> > >> Clif > >> > >> > > > > . > |