SELECT TOP n -- looking for assistance

C

Clif McIrvin

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
 
T

Tom van Stiphout

On Sat, 3 Apr 2010 11:20:09 -0500, "Clif McIrvin"

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
 
S

Steve

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
(e-mail address removed)
 
S

Steve

Thinking more about this, this will not work. Please ignore.

Steve


Steve said:
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
(e-mail address removed)


Clif McIrvin said:
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
 
S

Steve

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 said:
On Sat, 3 Apr 2010 11:20:09 -0500, "Clif McIrvin"

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
 
H

Hans Up

Clif said:
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
 
C

Clif McIrvin

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 said:
On Sat, 3 Apr 2010 11:20:09 -0500, "Clif McIrvin"

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
 
C

Clif McIrvin

Hans Up said:
Clif said:
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
 
C

Clif McIrvin

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 said:
Thinking more about this, this will not work. Please ignore.

Steve


Steve said:
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
(e-mail address removed)


Clif McIrvin said:
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
 
H

Hans Up

Clif said:
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. :)
 
S

Steve

Clif,

Good luck with your project!!!

Steve


Clif McIrvin said:
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 said:
Thinking more about this, this will not work. Please ignore.

Steve


Steve said:
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
(e-mail address removed)


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
 
C

Clif McIrvin

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
 
M

Mrs. Lori Andersen

Clif McIrvin said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Fiscal year 2
Interpreting Site Reports 4

Top