Problems with making the correct query

  • Thread starter Thread starter Anton
  • Start date Start date
A

Anton

Can some one help me?

I've a table with 3 fields

datefrom article price
1-10-2003 123 98
1-11-2003 123 96
1-12-2003 123 93
1-10-2003 111 77
1-11-2003 111 77
1-09-2003 111 77

I want to make a query that gives me the records before a specific
date (1-1-2004) and only the most actual one.

The result should be
1-12-2003 123 93
1-11-2003 111 77

Thanks in advance

Anton
 
You might try a query whose SQL looks something like this:

SELECT
[Your Table].*
FROM
[Your Table]
WHERE
[Your Table].[datefrom] =
(SELECT
Max([Self].[datefrom])
FROM
[Your Table] AS [Self]
WHERE
[Self].[article] = [Your Table].[article]
AND
[Self].[datefrom] < #1/1/2004#);

Instead of hard coding the "cutoff" date, you might consider using a
parameter query whose SQL looks something like this:

PARAMETERS [Cutoff Date] DateTime;
SELECT
[Your Table].*
FROM
[Your Table]
WHERE
[Your Table].[datefrom] =
(SELECT
Max([Self].[datefrom])
FROM
[Your Table] AS [Self]
WHERE
[Self].[article] = [Your Table].[article]
AND
[Self].[datefrom] < [Cutoff Date]);

You might want to use <= instead of < in the last condition in the WHERE
clause, depending on your needs.

For some alternative approaches, you might check out:

http://www.mvps.org/access/queries/qry0020.htm
 
Back
Top