From: Hans Up on
Clif McIrvin wrote:
> 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 rws 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

Hi Clif,

Not sure how well I understand your problem. I wonder if it might help
to create a separate query which only returns the last 2 unique
WeekEnding values.

Create qryLast2Weeks:

SELECT TOP 2 l.WeekEnding
FROM (
SELECT DISTINCT WeekEnding
FROM AssignedLots
) AS l
ORDER BY l.WeekEnding DESC;

Then INNER JOIN qryLast2Weeks (ON AssignedLots.WeekEnding =
qryLast2Weeks.WeekEnding) to your existing query.

Regards,
Hans
From: Clif McIrvin on
Thanks, Tom. Looks like that should work ... as long as the data in the
table is kept current, which it *should* be. <smile>.

I'd wondered about a WHERE clause, but wasn't quite clear on how to
proceed.

Thanks!

Clif

"Tom van Stiphout" <tom7744.no.spam(a)cox.net> wrote in message
news:0dser5pgkpi5uqo01oh4loipjfbaj3dhsb(a)4ax.com...
> On Sat, 3 Apr 2010 11:20:09 -0500, "Clif McIrvin"
> <clare.moe(a)nevergmail.com.invalid> wrote:
>
> If I understand you correctly, you need a WHERE clause, not a TOP
> clause. Design your query, and in the WeekEnding column enter on the
> Criteria row:
>>=DateAdd("ww",-2,Date())
> This expression will count back two weeks from today.
>
> -Tom.
> Microsoft Access MVP
>
>
>
>>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
"Hans Up" <hans.updyke(a)nospam.invalid> wrote in message
news:upJry%2320KHA.4420(a)TK2MSFTNGP02.phx.gbl...
> Clif McIrvin wrote:
>> 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 rws 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
>
> Hi Clif,
>
> Not sure how well I understand your problem. I wonder if it might
> help to create a separate query which only returns the last 2 unique
> WeekEnding values.
>
> Create qryLast2Weeks:
>
> SELECT TOP 2 l.WeekEnding
> FROM (
> SELECT DISTINCT WeekEnding
> FROM AssignedLots
> ) AS l
> ORDER BY l.WeekEnding DESC;
>
> Then INNER JOIN qryLast2Weeks (ON AssignedLots.WeekEnding =
> qryLast2Weeks.WeekEnding) to your existing query.
>
> Regards,
> Hans


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".

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.

Clif


From: Clif McIrvin on
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: Hans Up on
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. :-)
First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4
Prev: rere
Next: Entering measurements in a field