Prev: rere
Next: Entering measurements in a field
From: Clif McIrvin on 3 Apr 2010 12:20
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: Tom van Stiphout on 3 Apr 2010 12:53
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: Steve on 3 Apr 2010 13:49
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: Steve on 3 Apr 2010 14:07
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: Steve on 3 Apr 2010 14:10
The solution the OP wants is the "most recent two weeks". If that means the
current week and the previous week then shouldn't the criteria be:
>=DateAdd("ww",-1,Date())

Steve


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


 |  Next  |  Last
Pages: 1 2 3 4
Prev: rere
Next: Entering measurements in a field