Filter or Query Question

[a]

Thank You and I hope You Enjoy answer this Difficult Question
I have form (Continuous) and I’m using filter to filter data :
The date is like this:
Dates No HName ID
1/1/2004 1 N.oneH 1
2/1/2004 1 N.oneH 1
3/1/2004 1 N.oneH 1
4/1/2004 1 N.oneH 1
1/1/2004 1 K.To.O 2
2/1/2004 1 K.To.O 2
3/1/2004 1 K.To.O 2
1/1/2004 1 N.oneH 3
2/1/2004 1 N.oneH 3
3/1/2004 1 N.oneH 3
4/1/2004 1 N.oneH 3
Now the Filter: between 1/1/2004 and 3/1/2004 and Hname N.OneH
The Result Will be
Dates No HName ID
1/1/2004 1 N.oneH 1
2/1/2004 1 N.oneH 1
3/1/2004 1 N.oneH 1
1/1/2004 1 N.oneH 3
2/1/2004 1 N.oneH 3
3/1/2004 1 N.oneH 3
Here Is the Question:
Don’t display last record of every group of ID With Biggest Dates? Look the
to the result!!
The (Filter) or query Result Will be like This
Dates No HName ID
1/1/2004 1 N.oneH 1
2/1/2004 1 N.oneH 1
1/1/2004 1 N.oneH 3
2/1/2004 1 N.oneH 3
If You Could give the answer with Filter and query

 

[Michel Walsh]

Hi,



SELECT a.dates, a.no, a.hname, a.id

FROM myTable As a INNER JOIN myTable as b
ON a.no=b.no AND a.HName=b.HName AND a.id=b.id

GROUP BY a.dates, a.no, a.HName, a.id

HAVING a.dates <> MAX(b.dates)



Hoping it may help,
Vanderghast, Access MVP

 

[a]

Thank you very much mr mvp Michel Walsh
You have answer many question for me thank you...
I would like to give you another question:
Dates No HName ID AutoNumber
1/1/2004 1 N.oneH 1 1
2/1/2004 1 N.oneH 1 2
3/1/2004 1 N.oneH 1 3
4/1/2004 1 N.oneH 1 4
5/1/2004 1 N.oneH 1 5
you see I have add autonumber field:
The Question
Dates Between #1/1/2004# and #5/1/2004#
Will return the Following if I apply first answer:
Dates No HName ID AutoNumber
1/1/2004 1 N.oneH 1 1
2/1/2004 1 N.oneH 1 2
3/1/2004 1 N.oneH 1 3
4/1/2004 1 N.oneH 1 4
5/1/2004 1 N.oneH 1 5
I want the query return only:
Dates No HName ID AutoNumber
1/1/2004 1 N.oneH 1 1
2/1/2004 1 N.oneH 1 2
3/1/2004 1 N.oneH 1 3
4/1/2004 1 N.oneH 1 4
Can you give me the answer with and without AutoNumber Field
Thank you Very Much For Your Help

 

[Michel Walsh]

Hi,


same variation, this time the HAVING clause is on the Autonumber field. I am
not sure if the field dates has to be included or not in the JOIN. That
depends on the possibility to have more than one Autonumber for a given
value of dates, or not. If there is, keep it in the JOIN, if not, leave it
out.


SELECT a.dates, a.no, a.hname, a.id, a.autonumber

FROM myTable As a INNER JOIN myTable as b
ON a.no=b.no AND a.HName=b.HName AND a.id=b.id AND a.dates=b.dates

GROUP BY a.dates, a.no, a.HName, a.id, a.autonumber

HAVING a.autonumber <> MAX(b.autonumber)

or

SELECT a.dates, a.no, a.hname, a.id, a.autonumber

FROM myTable As a INNER JOIN myTable as b
ON a.no=b.no AND a.HName=b.HName AND a.id=b.id

GROUP BY a.dates, a.no, a.HName, a.id, a.autonumber

HAVING a.autonumber <> MAX(b.autonumber)



Without autonumber field, that looks like the previous message.


Hoping it may help,
Vanderghast, Access MVP