How to select the records corresponding to max "LIMIT" when "FG" and "WW" are same?

[amolin]

How to select the records corresponding to max "LIMIT" when "FG" and "WW" are same?

How to select the records corresponding to max "LIMIT" when "FG" and "WW" are same?

for

FG---------WW--------LIMIT-------QTY---------QTY2
A---------2004/12---------6---------2001---------1000
A---------2004/12---------10---------2001---------2000
A---------2004/07---------6---------3200---------1000
A---------2004/06---------6---------2040---------1000
B---------2004/22---------10---------2121---------4000
B---------2004/22---------22---------2121---------2222
B---------2004/22---------8---------2121---------2000
B---------2004/15---------8---------1221---------2000
B---------2004/15---------10---------1221---------4000
B---------2004/12---------8---------2212---------2000
B---------2004/12---------10---------2212---------4000
C---------2004/27---------9---------2222---------3000
C---------2004/27---------12---------2222---------22222
C---------2004/23---------12---------3332---------22222
C---------2004/23---------9---------3332---------3000
C---------2004/09---------9---------21323---------3000

 

[ColinEssex]

I may be a bit thick here but the field 'FG' is a single letter and the field 'WW' is a series of digits with a slash. . . . . . . how can they ever be the same?

Col

 

[maxmangion]

by FG and WW are same, i think he wanted to mean when values are identical in different records. An example from his list might be his first 2 records

FG WW
A 2004/12
A 2004/12

 

[ColinEssex]

Ok, I see now. Here's one way of doing it. Not the best maybe, but it works.

See Query2 in the enclosed zip for the final result.

Col

 

[amolin]

How to select the records corresponding to max "LIMIT" when "FG" and "WW" are same?

for

FG---------WW--------LIMIT-------QTY---------QTY2
A---------2004/12---------6---------2001---------1000
A---------2004/12---------10---------2001---------2000
A---------2004/07---------6---------3200---------1000
A---------2004/06---------6---------2040---------1000
B---------2004/22---------10---------2121---------4000
B---------2004/22---------22---------2121---------2222
B---------2004/22---------8---------2121---------2000
B---------2004/15---------8---------1221---------2000
B---------2004/15---------10---------1221---------4000
B---------2004/12---------8---------2212---------2000
B---------2004/12---------10---------2212---------4000
C---------2004/27---------9---------2222---------3000
C---------2004/27---------12---------2222---------22222
C---------2004/23---------12---------3332---------22222
C---------2004/23---------9---------3332---------3000
C---------2004/09---------9---------21323---------3000

Sorry, I will detail it as follow: for follow records:

B---------2004/22---------10---------2121---------4000
B---------2004/22---------22---------2121---------2222
B---------2004/22---------8---------2121---------2000
B---------2004/15---------8---------1221---------2000
B---------2004/15---------10---------1221---------4000
B---------2004/12---------8---------2212---------2000
B---------2004/12---------10---------2212---------4000

I will select :
B---------2004/22---------22---------2121---------2222
B---------2004/15---------10---------1221---------4000
B---------2004/12---------10---------2212---------4000

Thank you again!!

 

[maxmangion]

if you download the example, which ColinEssex has posted, you will have the result you're looking for!

 

[amolin]

if you download the example, which ColinEssex has posted, you will have the result you're looking for!

Thank you;

But the result ColinEssex provided is not the exactly what I want, I need to select the entire records which max LIMIT corresponding to . thank you,ColinEssex.

 

[maxmangion]

all you need to do go into the design view of query2 and drag the other fields which you want

 

[amolin]

all you need to do go into the design view of query2 and drag the other fields which you want

Sorry, I couldn't get the correct result as I need by simply drag other fields in to query 2, because there are "total" row in design grid. Do you have any other idea? thank you!

B---------2004/22---------10---------2121---------4000
B---------2004/22---------22---------2121---------2222
B---------2004/22---------8---------2121---------2000
B---------2004/15---------8---------1221---------2000
B---------2004/15---------10---------1221---------4000
B---------2004/12---------8---------2212---------2000
B---------2004/12---------10---------2212---------4000

I will select :
B---------2004/22---------22---------2121---------2222
B---------2004/15---------10---------1221---------4000
B---------2004/12---------10---------2212---------4000

Thank you again!!

 

[maxmangion]

create another query and drag the fields from your query2 and the other field from your table

 

[amolin]

create another query and drag the fields from your query2 and the other field from your table

I See,Thank you very much!!!

 

[ColinEssex]

Thanks Max, I was just going to say a 3rd query is needed because the GroupBy part is screwing it up when you add the other fields.

Col