SELECT TOP n -- looking for assistance

[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

 

[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

 

[Steve]

Add a calculated field yo your query:
WeekAssignedLotatePart("ww",[WeekEnding])
Set the sort for this field to Descending.
Set the Top property to 2.

Steve
(e-mail address removed)

 

[Steve]

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

Steve

Add a calculated field yo your query:
WeekAssignedLotatePart("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

 

[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

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

 

[Hans Up]

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

 

[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

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

 

[Clif McIrvin]

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

 

[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

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

Steve

Add a calculated field yo your query:
WeekAssignedLotatePart("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

 

[Hans Up]

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.

 

[Steve]

Clif,

Good luck with your project!!!

Steve

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

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

Steve

Add a calculated field yo your query:
WeekAssignedLotatePart("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

 

[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

 

[Mrs. Lori Andersen]

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