Hide Duplicates but show all columns

[Spa]

Hi

Can anyone please help?

I have the following test data (the actual data set is immense):
<TABLE cellSpacing=0 bgColor=#ffffff border=1><CAPTION>Test</CAPTION><THEAD><TR><TH borderColor=#000000 bgColor=#c0c0c0>ID</TH><TH borderColor=#000000 bgColor=#c0c0c0>Date</TH><TH borderColor=#000000 bgColor=#c0c0c0>Order No</TH><TH borderColor=#000000 bgColor=#c0c0c0>Item No</TH><TH borderColor=#000000 bgColor=#c0c0c0>Status</TH><TH borderColor=#000000 bgColor=#c0c0c0>Expected Qty</TH></TR></THEAD><TBODY><TR vAlign=top><TD borderColor=#c0c0c0 align=right>1</TD><TD borderColor=#c0c0c0>01.02.2010</TD><TD borderColor=#c0c0c0 align=right>1</TD><TD borderColor=#c0c0c0 align=right>111</TD><TD borderColor=#c0c0c0 align=right>90</TD><TD borderColor=#c0c0c0>1000</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0 align=right>2</TD><TD borderColor=#c0c0c0>02.02.2010</TD><TD borderColor=#c0c0c0 align=right>1</TD><TD borderColor=#c0c0c0 align=right>111</TD><TD borderColor=#c0c0c0 align=right>90</TD><TD borderColor=#c0c0c0>500</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0 align=right>3</TD><TD borderColor=#c0c0c0>03.02.2010</TD><TD borderColor=#c0c0c0 align=right>2</TD><TD borderColor=#c0c0c0 align=right>111</TD><TD borderColor=#c0c0c0 align=right>90</TD><TD borderColor=#c0c0c0>1000</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0 align=right>4</TD><TD borderColor=#c0c0c0>01.02.2010</TD><TD borderColor=#c0c0c0 align=right>2</TD><TD borderColor=#c0c0c0 align=right>222</TD><TD borderColor=#c0c0c0 align=right>90</TD><TD borderColor=#c0c0c0>1000</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0 align=right>5</TD><TD borderColor=#c0c0c0>02.02.2010</TD><TD borderColor=#c0c0c0 align=right>2</TD><TD borderColor=#c0c0c0 align=right>222</TD><TD borderColor=#c0c0c0 align=right>90</TD><TD borderColor=#c0c0c0>1000</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0 align=right>6</TD><TD borderColor=#c0c0c0>05.02.2010</TD><TD borderColor=#c0c0c0 align=right>2</TD><TD borderColor=#c0c0c0 align=right>222</TD><TD borderColor=#c0c0c0 align=right>20</TD><TD borderColor=#c0c0c0>1000</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0 align=right>7</TD><TD borderColor=#c0c0c0>01.02.2010</TD><TD borderColor=#c0c0c0 align=right>3</TD><TD borderColor=#c0c0c0 align=right>333</TD><TD borderColor=#c0c0c0 align=right>90</TD><TD borderColor=#c0c0c0>1000</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0 align=right>8</TD><TD borderColor=#c0c0c0>02.02.2010</TD><TD borderColor=#c0c0c0 align=right>3</TD><TD borderColor=#c0c0c0 align=right>333</TD><TD borderColor=#c0c0c0 align=right>90</TD><TD borderColor=#c0c0c0>700</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0 align=right>9</TD><TD borderColor=#c0c0c0>08.02.2010</TD><TD borderColor=#c0c0c0 align=right>3</TD><TD borderColor=#c0c0c0 align=right>333</TD><TD borderColor=#c0c0c0 align=right>70</TD><TD borderColor=#c0c0c0>300</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0 align=right>10</TD><TD borderColor=#c0c0c0>01.02.2010</TD><TD borderColor=#c0c0c0 align=right>4</TD><TD borderColor=#c0c0c0 align=right>444</TD><TD borderColor=#c0c0c0 align=right>90</TD><TD borderColor=#c0c0c0>1000</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0 align=right>11</TD><TD borderColor=#c0c0c0>02.02.2010</TD><TD borderColor=#c0c0c0 align=right>4</TD><TD borderColor=#c0c0c0 align=right>444</TD><TD borderColor=#c0c0c0 align=right>90</TD><TD borderColor=#c0c0c0>500</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0 align=right>12</TD><TD borderColor=#c0c0c0>04.02.2010</TD><TD borderColor=#c0c0c0 align=right>4</TD><TD borderColor=#c0c0c0 align=right>444</TD><TD borderColor=#c0c0c0 align=right>80</TD><TD borderColor=#c0c0c0>500</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0 align=right>13</TD><TD borderColor=#c0c0c0>01.02.2010</TD><TD borderColor=#c0c0c0 align=right>5</TD><TD borderColor=#c0c0c0 align=right>111</TD><TD borderColor=#c0c0c0 align=right>10</TD><TD borderColor=#c0c0c0>1000</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0 align=right>14</TD><TD borderColor=#c0c0c0>01.02.2010</TD><TD borderColor=#c0c0c0 align=right>6</TD><TD borderColor=#c0c0c0 align=right>222</TD><TD borderColor=#c0c0c0 align=right>20</TD><TD borderColor=#c0c0c0>1000</TD></TR></TBODY><TFOOT></TFOOT></TABLE>

The problem is that I have duplicate "item nos" per "order no". I would like to show only the "recent dated" rows where the duplicates occur and hide the duplicates. The returned data should look like this:

<TABLE cellSpacing=0 bgColor=#ffffff border=1><CAPTION>Test</CAPTION><THEAD><TR><TH borderColor=#000000 bgColor=#c0c0c0>ID</TH><TH borderColor=#000000 bgColor=#c0c0c0>Date</TH><TH borderColor=#000000 bgColor=#c0c0c0>Order No</TH><TH borderColor=#000000 bgColor=#c0c0c0>Item No</TH><TH borderColor=#000000 bgColor=#c0c0c0>Status</TH><TH borderColor=#000000 bgColor=#c0c0c0>Expected Qty</TH></TR></THEAD><TBODY><TR vAlign=top><TD borderColor=#c0c0c0 align=right>2</TD><TD borderColor=#c0c0c0>02.02.2010</TD><TD borderColor=#c0c0c0 align=right>1</TD><TD borderColor=#c0c0c0 align=right>111</TD><TD borderColor=#c0c0c0 align=right>90</TD><TD borderColor=#c0c0c0>500</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0 align=right>3</TD><TD borderColor=#c0c0c0>03.02.2010</TD><TD borderColor=#c0c0c0 align=right>2</TD><TD borderColor=#c0c0c0 align=right>111</TD><TD borderColor=#c0c0c0 align=right>90</TD><TD borderColor=#c0c0c0>1000</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0 align=right>6</TD><TD borderColor=#c0c0c0>05.02.2010</TD><TD borderColor=#c0c0c0 align=right>2</TD><TD borderColor=#c0c0c0 align=right>222</TD><TD borderColor=#c0c0c0 align=right>20</TD><TD borderColor=#c0c0c0>1000</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0 align=right>7</TD><TD borderColor=#c0c0c0>01.02.2010</TD><TD borderColor=#c0c0c0 align=right>3</TD><TD borderColor=#c0c0c0 align=right>333</TD><TD borderColor=#c0c0c0 align=right>90</TD><TD borderColor=#c0c0c0>1000</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0 align=right>8</TD><TD borderColor=#c0c0c0>02.02.2010</TD><TD borderColor=#c0c0c0 align=right>3</TD><TD borderColor=#c0c0c0 align=right>333</TD><TD borderColor=#c0c0c0 align=right>90</TD><TD borderColor=#c0c0c0>700</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0 align=right>9</TD><TD borderColor=#c0c0c0>08.02.2010</TD><TD borderColor=#c0c0c0 align=right>3</TD><TD borderColor=#c0c0c0 align=right>333</TD><TD borderColor=#c0c0c0 align=right>70</TD><TD borderColor=#c0c0c0>300</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0 align=right>12</TD><TD borderColor=#c0c0c0>04.02.2010</TD><TD borderColor=#c0c0c0 align=right>4</TD><TD borderColor=#c0c0c0 align=right>444</TD><TD borderColor=#c0c0c0 align=right>80</TD><TD borderColor=#c0c0c0>500</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0 align=right>13</TD><TD borderColor=#c0c0c0>01.02.2010</TD><TD borderColor=#c0c0c0 align=right>5</TD><TD borderColor=#c0c0c0 align=right>111</TD><TD borderColor=#c0c0c0 align=right>10</TD><TD borderColor=#c0c0c0>1000</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0 align=right>14</TD><TD borderColor=#c0c0c0>01.02.2010</TD><TD borderColor=#c0c0c0 align=right>6</TD><TD borderColor=#c0c0c0 align=right>222</TD><TD borderColor=#c0c0c0 align=right>20</TD><TD borderColor=#c0c0c0>1000</TD></TR></TBODY><TFOOT></TFOOT></TABLE>

Many Thanks

 

[SydneyGeek]

I couldn't get exactly what you need -- you will have to play with the appropriate columns to match -- but here is the approach;

NOTE -- ASSUMES THAT TABLE IS CALLED tblMain. You will need to adjust the name to suit.

1. Build a query with all fields of the table (all fields, not the *)
2. in the Criteria row for ID, put this (adjusted so you use the correct table name:

In (SELECT MAX(ID) FROM tblMain AS T WHERE T.[Order No]=tblMain.[Order No] AND T.[item no]=tblMain.[Item no] AND T.Status=tblmain.Status)

This assumes that you need to match up Order No, Item no and Status to get what you want.

Denis

 

[Spa]

Hi Denis

I have tried it and it works!

Thank you very much for taking the time to help me with this, your instruction were really clear. It really is appreciated.

Thanks again

Peter.

 

[SydneyGeek]

You're welcome

Denis