Criteria

[jamesd]

I have a table with Records by Week Number.

Week 48 with 45 records
Week 49 with 52 records
Week 50 with 12 records

Is there a way to select all records apart from the max week number ( this may only be a part week)

So in the example above the query would only show weeks 48 and 49.

As this table is updated automaticaly I need the query to show all data apart from this week.

 

[Joe4]

You could do this in a two step process (with two queries).

1. Create an aggregate query to find the max week number.

2. Link the first query back into your data set on the week number field, performing an unmatched query so as to return everything where the week number does NOT equal the maximum.

 

[SydneyGeek]

James, here are 2 options.

Based on a dummy table called tblWeeks with fields RecID, WeekNo, Item.

First option uses a subquery in the criteria. This has the advantage of being only one query but can be very slow with large datasets:

SELECT tblWeeks.RecID, tblWeeks.WeekNo, tblWeeks.Item
FROM tblWeeks
WHERE (((tblWeeks.WeekNo) Not In (SELECT MAX(WeekNo) FROM tblWeeks AS T)));

The second option uses 2 queries.

First, find the highest week number --
This is qryMaxWeek

SELECT TOP 1 tblWeeks.WeekNo AS LastWeek
FROM tblWeeks
ORDER BY tblWeeks.WeekNo DESC;

Then build a second query that compares this value to other records in the table --

SELECT tblWeeks.RecID, tblWeeks.WeekNo, tblWeeks.Item
FROM tblWeeks, qryMaxWeek
WHERE (((IIf([WeekNo]<[LastWeek],1,0))=1));

EDIT: And Joe's suggestion is a third possibility. Try his, then my second option, and see which performs best for you.

Denis