Filter or Query Question

  • Thread starter Thread starter a
  • Start date Start date
A

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
 
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
 
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
 
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
 
Back
Top