Selecting lowest number from Matching Fields...

S

spudd

New Member
Joined
Jan 18, 2011
Messages
18
Hi,

I have a query Table which collates from other tables a set of data.

What I need to do on this table is to select the record with the lowest number where there is matching records.

Below is a sample of the table I am querying.

What I would like to do is pick the record with the lowest number out of MILES where Fields A, B & C Match.

So for the data below would be "87" and Bristol.

The data in A,B & C changes and there are about 1,000,000 total records to pull out from.

Cheers in advance.

Andy

<TABLE dir=ltr border=1 cellSpacing=0 borderColor=#000000 cellPadding=2 width=801><TBODY><TR><TD bgColor=#c0c0c0 height=20 width="15%">MPL Get Alternate Site.WkYr
​
</TD><TD bgColor=#c0c0c0 height=20 width="15%">MPL Get Alternate Site.Product
​
</TD><TD bgColor=#c0c0c0 height=20 width="26%">MPL Get Alternate Site.depot
​
</TD><TD bgColor=#c0c0c0 height=20 width="27%">MPL Get Alternate Site.PlantID
​
</TD><TD bgColor=#c0c0c0 height=20 width="8%">Miles
​
</TD><TD bgColor=#c0c0c0 height=20 width="8%">Demand1

</TD></TR><TR><TD bgColor=#ffffff height=20 width="15%">1.2011
​
</TD><TD bgColor=#ffffff height=20 width="15%">100433

</TD><TD bgColor=#ffffff height=20 width="26%">W969

</TD><TD bgColor=#ffffff height=20 width="27%">Bellshill
​
</TD><TD bgColor=#ffffff height=20 width="8%">556
​
</TD><TD bgColor=#ffffff height=20 width="8%">18531

</TD></TR><TR><TD bgColor=#ffffff height=20 width="15%">1.2011
​
</TD><TD bgColor=#ffffff height=20 width="15%">100433

</TD><TD bgColor=#ffffff height=20 width="26%">W969

</TD><TD bgColor=#ffffff height=20 width="27%">Pennine
​
</TD><TD bgColor=#ffffff height=20 width="8%">265
​
</TD><TD bgColor=#ffffff height=20 width="8%">18531

</TD></TR><TR><TD bgColor=#ffffff height=20 width="15%">1.2011
​
</TD><TD bgColor=#ffffff height=20 width="15%">100433

</TD><TD bgColor=#ffffff height=20 width="26%">W969

</TD><TD bgColor=#ffffff height=20 width="27%">Tuscany Park
​
</TD><TD bgColor=#ffffff height=20 width="8%">253
​
</TD><TD bgColor=#ffffff height=20 width="8%">18531

</TD></TR><TR><TD bgColor=#ffffff height=20 width="15%">1.2011
​
</TD><TD bgColor=#ffffff height=20 width="15%">100433

</TD><TD bgColor=#ffffff height=20 width="26%">W969

</TD><TD bgColor=#ffffff height=20 width="27%">Wednesbury
​
</TD><TD bgColor=#ffffff height=20 width="8%">126
​
</TD><TD bgColor=#ffffff height=20 width="8%">18531

</TD></TR><TR><TD bgColor=#ffffff height=20 width="15%">1.2011
​
</TD><TD bgColor=#ffffff height=20 width="15%">100433

</TD><TD bgColor=#ffffff height=20 width="26%">W969

</TD><TD bgColor=#ffffff height=20 width="27%">Bristol
​
</TD><TD bgColor=#ffffff height=20 width="8%">87
​
</TD><TD bgColor=#ffffff height=20 width="8%">18531

</TD></TR><TR><TD bgColor=#ffffff height=20 width="15%">1.2011
​
</TD><TD bgColor=#ffffff height=20 width="15%">100433

</TD><TD bgColor=#ffffff height=20 width="26%">W969

</TD><TD bgColor=#ffffff height=20 width="27%">Enfield
​
</TD><TD bgColor=#ffffff height=20 width="8%">124
​
</TD><TD bgColor=#ffffff height=20 width="8%">18531

</TD></TR><TR><TD bgColor=#ffffff height=20 width="15%">1.2011
​
</TD><TD bgColor=#ffffff height=20 width="15%">100433

</TD><TD bgColor=#ffffff height=20 width="26%">W969

</TD><TD bgColor=#ffffff height=20 width="27%">Tuscany Park
​
</TD><TD bgColor=#ffffff height=20 width="8%">253
​
</TD><TD bgColor=#ffffff height=20 width="8%">18531

</TD></TR><TR><TD bgColor=#ffffff height=20 width="15%">1.2011
​
</TD><TD bgColor=#ffffff height=20 width="15%">100433

</TD><TD bgColor=#ffffff height=20 width="26%">W969

</TD><TD bgColor=#ffffff height=20 width="27%">Newburn
​
</TD><TD bgColor=#ffffff height=20 width="8%">419
​
</TD><TD bgColor=#ffffff height=20 width="8%">18531

</TD></TR><TR><TD bgColor=#ffffff height=20 width="15%">1.2011
​
</TD><TD bgColor=#ffffff height=20 width="15%">100433

</TD><TD bgColor=#ffffff height=20 width="26%">W969

</TD><TD bgColor=#ffffff height=20 width="27%">Eastwood
​
</TD><TD bgColor=#ffffff height=20 width="8%">160
​
</TD><TD bgColor=#ffffff height=20 width="8%">18531

</TD></TR><TR><TD bgColor=#ffffff height=20 width="15%">2.2011
​
</TD><TD bgColor=#ffffff height=20 width="15%">100433

</TD><TD bgColor=#ffffff height=20 width="26%">W969

</TD><TD bgColor=#ffffff height=20 width="27%">Bellshill
​
</TD><TD bgColor=#ffffff height=20 width="8%">556
​
</TD><TD bgColor=#ffffff height=20 width="8%">17494

</TD></TR><TR><TD bgColor=#ffffff height=20 width="15%">2.2011
​
</TD><TD bgColor=#ffffff height=20 width="15%">100433

</TD><TD bgColor=#ffffff height=20 width="26%">W969

</TD><TD bgColor=#ffffff height=20 width="27%">Pennine
​
</TD><TD bgColor=#ffffff height=20 width="8%">265
​
</TD><TD bgColor=#ffffff height=20 width="8%">17494

</TD></TR><TR><TD bgColor=#ffffff height=20 width="15%">2.2011
​
</TD><TD bgColor=#ffffff height=20 width="15%">100433

</TD><TD bgColor=#ffffff height=20 width="26%">W969

</TD><TD bgColor=#ffffff height=20 width="27%">Tuscany Park
​
</TD><TD bgColor=#ffffff height=20 width="8%">253
​
</TD><TD bgColor=#ffffff height=20 width="8%">17494

</TD></TR><TR><TD bgColor=#ffffff height=20 width="15%">2.2011
​
</TD><TD bgColor=#ffffff height=20 width="15%">100433

</TD><TD bgColor=#ffffff height=20 width="26%">W969

</TD><TD bgColor=#ffffff height=20 width="27%">Wednesbury
​
</TD><TD bgColor=#ffffff height=20 width="8%">126
​
</TD><TD bgColor=#ffffff height=20 width="8%">17494

</TD></TR><TR><TD bgColor=#ffffff height=20 width="15%">2.2011
​
</TD><TD bgColor=#ffffff height=20 width="15%">100433

</TD><TD bgColor=#ffffff height=20 width="26%">W969

</TD><TD bgColor=#ffffff height=20 width="27%">Bristol
​
</TD><TD bgColor=#ffffff height=20 width="8%">87
​
</TD><TD bgColor=#ffffff height=20 width="8%">17494

</TD></TR><TR><TD bgColor=#ffffff height=20 width="15%">2.2011
​
</TD><TD bgColor=#ffffff height=20 width="15%">100433

</TD><TD bgColor=#ffffff height=20 width="26%">W969

</TD><TD bgColor=#ffffff height=20 width="27%">Enfield
​
</TD><TD bgColor=#ffffff height=20 width="8%">124
​
</TD><TD bgColor=#ffffff height=20 width="8%">17494

</TD></TR><TR><TD bgColor=#ffffff height=20 width="15%">2.2011
​
</TD><TD bgColor=#ffffff height=20 width="15%">100433

</TD><TD bgColor=#ffffff height=20 width="26%">W969

</TD><TD bgColor=#ffffff height=20 width="27%">Tuscany Park
​
</TD><TD bgColor=#ffffff height=20 width="8%">253
​
</TD><TD bgColor=#ffffff height=20 width="8%">17494

</TD></TR><TR><TD bgColor=#ffffff height=20 width="15%">2.2011
​
</TD><TD bgColor=#ffffff height=20 width="15%">100433

</TD><TD bgColor=#ffffff height=20 width="26%">W969

</TD><TD bgColor=#ffffff height=20 width="27%">Newburn
​
</TD><TD bgColor=#ffffff height=20 width="8%">419
​
</TD><TD bgColor=#ffffff height=20 width="8%">17494

</TD></TR><TR><TD bgColor=#ffffff height=20 width="15%">2.2011
​
</TD><TD bgColor=#ffffff height=20 width="15%">100433

</TD><TD bgColor=#ffffff height=20 width="26%">W969

</TD><TD bgColor=#ffffff height=20 width="27%">Eastwood
​
</TD><TD bgColor=#ffffff height=20 width="8%">160
​
</TD><TD bgColor=#ffffff height=20 width="8%">17494

</TD></TR><TR><TD bgColor=#ffffff height=20 width="15%">3.2011
​
</TD><TD bgColor=#ffffff height=20 width="15%">100433

</TD><TD bgColor=#ffffff height=20 width="26%">W969

</TD><TD bgColor=#ffffff height=20 width="27%">Bellshill
​
</TD><TD bgColor=#ffffff height=20 width="8%">556
​
</TD><TD bgColor=#ffffff height=20 width="8%">19857

</TD></TR><TR><TD bgColor=#ffffff height=20 width="15%">3.2011
​
</TD><TD bgColor=#ffffff height=20 width="15%">100433

</TD><TD bgColor=#ffffff height=20 width="26%">W969

</TD><TD bgColor=#ffffff height=20 width="27%">Pennine
​
</TD><TD bgColor=#ffffff height=20 width="8%">265
​
</TD><TD bgColor=#ffffff height=20 width="8%">19857

</TD></TR><TR><TD bgColor=#ffffff height=20 width="15%">3.2011
​
</TD><TD bgColor=#ffffff height=20 width="15%">100433

</TD><TD bgColor=#ffffff height=20 width="26%">W969

</TD><TD bgColor=#ffffff height=20 width="27%">Tuscany Park
​
</TD><TD bgColor=#ffffff height=20 width="8%">253
​
</TD><TD bgColor=#ffffff height=20 width="8%">19857

</TD></TR><TR><TD bgColor=#ffffff height=20 width="15%">3.2011
​
</TD><TD bgColor=#ffffff height=20 width="15%">100433

</TD><TD bgColor=#ffffff height=20 width="26%">W969

</TD><TD bgColor=#ffffff height=20 width="27%">Wednesbury
​
</TD><TD bgColor=#ffffff height=20 width="8%">126
​
</TD><TD bgColor=#ffffff height=20 width="8%">19857

</TD></TR><TR><TD bgColor=#ffffff height=20 width="15%">3.2011
​
</TD><TD bgColor=#ffffff height=20 width="15%">100433

</TD><TD bgColor=#ffffff height=20 width="26%">W969

</TD><TD bgColor=#ffffff height=20 width="27%">Bristol
​
</TD><TD bgColor=#ffffff height=20 width="8%">87
​
</TD><TD bgColor=#ffffff height=20 width="8%">19857

</TD></TR><TR><TD bgColor=#ffffff height=20 width="15%">3.2011
​
</TD><TD bgColor=#ffffff height=20 width="15%">100433

</TD><TD bgColor=#ffffff height=20 width="26%">W969

</TD><TD bgColor=#ffffff height=20 width="27%">Enfield
​
</TD><TD bgColor=#ffffff height=20 width="8%">124
​
</TD><TD bgColor=#ffffff height=20 width="8%">19857

</TD></TR><TR><TD bgColor=#ffffff height=20 width="15%">3.2011
​
</TD><TD bgColor=#ffffff height=20 width="15%">100433

</TD><TD bgColor=#ffffff height=20 width="26%">W969

</TD><TD bgColor=#ffffff height=20 width="27%">Tuscany Park
​
</TD><TD bgColor=#ffffff height=20 width="8%">253
​
</TD><TD bgColor=#ffffff height=20 width="8%">19857

</TD></TR><TR><TD bgColor=#ffffff height=20 width="15%">3.2011
​
</TD><TD bgColor=#ffffff height=20 width="15%">100433

</TD><TD bgColor=#ffffff height=20 width="26%">W969

</TD><TD bgColor=#ffffff height=20 width="27%">Newburn
​
</TD><TD bgColor=#ffffff height=20 width="8%">419
​
</TD><TD bgColor=#ffffff height=20 width="8%">19857

</TD></TR><TR><TD bgColor=#ffffff height=20 width="15%">3.2011
​
</TD><TD bgColor=#ffffff height=20 width="15%">100433

</TD><TD bgColor=#ffffff height=20 width="26%">W969

</TD><TD bgColor=#ffffff height=20 width="27%">Eastwood
​
</TD><TD bgColor=#ffffff height=20 width="8%">160
​
</TD><TD bgColor=#ffffff height=20 width="8%">19857

</TD></TR></TBODY></TABLE>​
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Sort by date Sort by votes
Hi

What is A, B and C? It's not clear (at least to me) how you're getting Bristol from that result. If you just want the lowest value for "Miles" then just use something like:

SELECT * FROM YOURTABLE WHERE MILES = (SELECT MIN(MILES) FROM YOURTABLE)

HTH
DK
 
Upvote 0
Hope this helps clarify.

The query is helping me choose the closest site to make a product for a depot in a given week.

So...

To make product 100433 in Wk 01.2011 fopr Depot W969 of the making sites then Bristol is Closer at 87 miles of all the sites.

Data in A,B & C cycles through 104 weeks, then through differing depots then through Products.

I total there are around 1,000,000 differing permutations hence the need to handle in a query.

D & C are added from another query.

So what I need to so is group A,B & C to show all the same values then select the record where E is the lowest figure.

Hope that helps.

Andy
 
Upvote 0
OK, I think I have understood. Can you try this and see if it works. You will need to subsitute the "mytable" bit with the name of your actual table.

SELECT Q1.WkYr, Q1.Product, Q1.Depot, Q1.PlantID, Q1.Miles, Q1.Demand FROM mytable q1, (SELECT WkYr, Product, Depot, Min(Miles) AS MINMILES FROM mytable group by WkYr, Product, Depot) AS Q2 WHERE Q1.WkYr=Q2.WkYr AND Q1.Product=Q2.Product AND Q1.Depot=Q2.Depot AND Q1.Miles=Q2.MINMILES

In my testing based on your sample data it output three records (one for each week) and it chose Bristol in each case. If you have two plants with the same Miles then they'll both get output by the query.

HTH
DK
 
Upvote 0
Hi tided up the fields a bit with the prefix MPL. The table name is MPLSum.

I have rehashed the SQL to what I think is right and that is listed below.

SELECT MPLSum.MPLWk, MPLSum.MPLP, MPLSum.MPLD, MPLSum.MPLPL, MPLSum.Miles, MPLSum.Demand1
FROM MPLSum, (SELECT MPLWk, MPLP, MPLD, Min(Miles) AS MINMILES FROM MPLSUM
Group By MPLWk,MPLP, MPLD) AS Q2

WHERE MPLSUM.MPLWk=Q2.MPLWk AND MPLSum.MPLP=Q2.MPLP AND MPLSum.MPLD=Q2.MPLD AND MPLSum.Miles=Q2.MINMILES

I now get an error "You tried to execute a query that does not include the specified expression "MPLwk" as part of the aggregate function.

I have attached a copy of the top table so you can see what the field headers are named as.

<TABLE dir=ltr border=1 cellSpacing=0 borderColor=#000000 cellPadding=2 width=685><TBODY><TR><TD bgColor=#c0c0c0 height=20 width="30%">MPLP
​
</TD><TD bgColor=#c0c0c0 height=20 width="14%">MPLD
​
</TD><TD bgColor=#c0c0c0 height=20 width="14%">MPLPL
​
</TD><TD bgColor=#c0c0c0 height=20 width="14%">Miles
​
</TD><TD bgColor=#c0c0c0 height=20 width="14%">Demand1
​
</TD><TD bgColor=#c0c0c0 height=20 width="14%">MPLWk
​
</TD></TR><TR><TD bgColor=#ffffff height=20 width="30%">100433

</TD><TD bgColor=#ffffff height=20 width="14%">W969

</TD><TD bgColor=#ffffff height=20 width="14%">Bellshill
​
</TD><TD bgColor=#ffffff height=20 width="14%">556
​
</TD><TD bgColor=#ffffff height=20 width="14%">18531

</TD><TD bgColor=#ffffff height=20 width="14%">1.2011
​
</TD></TR><TR><TD bgColor=#ffffff height=20 width="30%">100433

</TD><TD bgColor=#ffffff height=20 width="14%">W969

</TD><TD bgColor=#ffffff height=20 width="14%">Pennine
​
</TD><TD bgColor=#ffffff height=20 width="14%">265
​
</TD><TD bgColor=#ffffff height=20 width="14%">18531

</TD><TD bgColor=#ffffff height=20 width="14%">1.2011
​
</TD></TR><TR><TD bgColor=#ffffff height=20 width="30%">100433

</TD><TD bgColor=#ffffff height=20 width="14%">W969

</TD><TD bgColor=#ffffff height=20 width="14%">Tuscany Park
​
</TD><TD bgColor=#ffffff height=20 width="14%">253
​
</TD><TD bgColor=#ffffff height=20 width="14%">18531

</TD><TD bgColor=#ffffff height=20 width="14%">1.2011
​
</TD></TR><TR><TD bgColor=#ffffff height=20 width="30%">100433

</TD><TD bgColor=#ffffff height=20 width="14%">W969

</TD><TD bgColor=#ffffff height=20 width="14%">Wednesbury
​
</TD><TD bgColor=#ffffff height=20 width="14%">126
​
</TD><TD bgColor=#ffffff height=20 width="14%">18531

</TD><TD bgColor=#ffffff height=20 width="14%">1.2011
​
</TD></TR><TR><TD bgColor=#ffffff height=20 width="30%">100433

</TD><TD bgColor=#ffffff height=20 width="14%">W969

</TD><TD bgColor=#ffffff height=20 width="14%">Bristol
​
</TD><TD bgColor=#ffffff height=20 width="14%">87
​
</TD><TD bgColor=#ffffff height=20 width="14%">18531

</TD><TD bgColor=#ffffff height=20 width="14%">1.2011
​
</TD></TR><TR><TD bgColor=#ffffff height=20 width="30%">100433

</TD><TD bgColor=#ffffff height=20 width="14%">W969

</TD><TD bgColor=#ffffff height=20 width="14%">Enfield
​
</TD><TD bgColor=#ffffff height=20 width="14%">124
​
</TD><TD bgColor=#ffffff height=20 width="14%">18531

</TD><TD bgColor=#ffffff height=20 width="14%">1.2011
​
</TD></TR><TR><TD bgColor=#ffffff height=20 width="30%">100433

</TD><TD bgColor=#ffffff height=20 width="14%">W969

</TD><TD bgColor=#ffffff height=20 width="14%">Tuscany Park
​
</TD><TD bgColor=#ffffff height=20 width="14%">253
​
</TD><TD bgColor=#ffffff height=20 width="14%">18531

</TD><TD bgColor=#ffffff height=20 width="14%">1.2011
​
</TD></TR><TR><TD bgColor=#ffffff height=20 width="30%">100433

</TD><TD bgColor=#ffffff height=20 width="14%">W969

</TD><TD bgColor=#ffffff height=20 width="14%">Newburn
​
</TD><TD bgColor=#ffffff height=20 width="14%">419
​
</TD><TD bgColor=#ffffff height=20 width="14%">18531

</TD><TD bgColor=#ffffff height=20 width="14%">1.2011

</TD></TR></TBODY></TABLE>​

Thanks once again.

Andy
 
Upvote 0
OK, that is strange as the field MPLWk is shown in the group by. I also tested the SQL you posted and it worked fine on your sample data. Have you tried doing the same?

Can you confirm that the data you posted is exactly how your table is defined in Access?

DK
 
Upvote 0
Muts admit it's getting me confused as well. When I split the query down the first part of the select query works fine and I get the following table generated which as you would expect is a direct "copy" of the query table being queried.
<TABLE dir=ltr border=1 cellSpacing=0 borderColor=#000000 cellPadding=2 width=826><TBODY><TR><TD bgColor=#c0c0c0 height=20 width="16%">
MPLSum.MPLWk​
​
​
​
​
​
</TD><TD bgColor=#c0c0c0 height=20 width="25%">
MPLSum.MPLP​
​
​
​
​
​
</TD><TD bgColor=#c0c0c0 height=20 width="12%">
MPLSum.MPLD​
​
​
​
​
​
</TD><TD bgColor=#c0c0c0 height=20 width="15%">
MPLSum.MPLPL​
​
​
​
​
​
</TD><TD bgColor=#c0c0c0 height=20 width="15%">
MPLSum.Miles​
​
​
​
​
​
</TD><TD bgColor=#c0c0c0 height=20 width="18%">
MPLSum.Demand1​
​
​
​
​
​
</TD></TR><TR><TD bgColor=#ffffff height=20 width="16%">1.2011
</TD><TD bgColor=#ffffff height=20 width="25%">
100433​
​
​
​
​
</TD><TD bgColor=#ffffff height=20 width="12%">W969
</TD><TD bgColor=#ffffff height=20 width="15%">Bellshill
</TD><TD bgColor=#ffffff height=20 width="15%">
556​
​
​
​
​
</TD><TD bgColor=#ffffff height=20 width="18%">
18531​
​
​
​
​
</TD></TR><TR><TD bgColor=#ffffff height=20 width="16%">1.2011
</TD><TD bgColor=#ffffff height=20 width="25%">
100433​
​
​
​
​
</TD><TD bgColor=#ffffff height=20 width="12%">W969
</TD><TD bgColor=#ffffff height=20 width="15%">Pennine
</TD><TD bgColor=#ffffff height=20 width="15%">
265​
​
​
​
​
</TD><TD bgColor=#ffffff height=20 width="18%">
18531​
​
​
​
​
</TD></TR><TR><TD bgColor=#ffffff height=20 width="16%">1.2011
</TD><TD bgColor=#ffffff height=20 width="25%">
100433​
​
​
​
​
</TD><TD bgColor=#ffffff height=20 width="12%">W969
</TD><TD bgColor=#ffffff height=20 width="15%">Tuscany Park
</TD><TD bgColor=#ffffff height=20 width="15%">
253​
​
​
​
​
</TD><TD bgColor=#ffffff height=20 width="18%">
18531​
​
​
​
​
</TD></TR><TR><TD bgColor=#ffffff height=20 width="16%">1.2011
</TD><TD bgColor=#ffffff height=20 width="25%">
100433​
​
​
​
​
</TD><TD bgColor=#ffffff height=20 width="12%">W969
</TD><TD bgColor=#ffffff height=20 width="15%">Wednesbury
</TD><TD bgColor=#ffffff height=20 width="15%">
126​
​
​
​
​
</TD><TD bgColor=#ffffff height=20 width="18%">
18531​
​
​
​
​
</TD></TR><TR><TD bgColor=#ffffff height=20 width="16%">1.2011
</TD><TD bgColor=#ffffff height=20 width="25%">
100433​
​
​
​
​
</TD><TD bgColor=#ffffff height=20 width="12%">W969
</TD><TD bgColor=#ffffff height=20 width="15%">Bristol
</TD><TD bgColor=#ffffff height=20 width="15%">
87​
​
​
​
​
</TD><TD bgColor=#ffffff height=20 width="18%">
18531​
​
​
​
​
</TD></TR><TR><TD bgColor=#ffffff height=20 width="16%">1.2011
</TD><TD bgColor=#ffffff height=20 width="25%">
100433​
​
​
​
​
</TD><TD bgColor=#ffffff height=20 width="12%">W969
</TD><TD bgColor=#ffffff height=20 width="15%">Enfield
</TD><TD bgColor=#ffffff height=20 width="15%">
124​
​
​
​
​
</TD><TD bgColor=#ffffff height=20 width="18%">
18531​
​
​
​
​
</TD></TR><TR><TD bgColor=#ffffff height=20 width="16%">1.2011
</TD><TD bgColor=#ffffff height=20 width="25%">
100433​
​
​
​
​
</TD><TD bgColor=#ffffff height=20 width="12%">W969
</TD><TD bgColor=#ffffff height=20 width="15%">Tuscany Park
</TD><TD bgColor=#ffffff height=20 width="15%">
253​
​
​
​
​
</TD><TD bgColor=#ffffff height=20 width="18%">
18531​
​
​
​
​
</TD></TR><TR><TD bgColor=#ffffff height=20 width="16%">1.2011
</TD><TD bgColor=#ffffff height=20 width="25%">
100433​
​
​
​
​
</TD><TD bgColor=#ffffff height=20 width="12%">W969
</TD><TD bgColor=#ffffff height=20 width="15%">Newburn
</TD><TD bgColor=#ffffff height=20 width="15%">
419​
​
​
​
​
</TD><TD bgColor=#ffffff height=20 width="18%">
18531​
​
​
​
​
</TD></TR><TR><TD bgColor=#ffffff height=20 width="16%">1.2011
</TD><TD bgColor=#ffffff height=20 width="25%">
100433​
​
​
​
​
</TD><TD bgColor=#ffffff height=20 width="12%">W969
</TD><TD bgColor=#ffffff height=20 width="15%">Eastwood
</TD><TD bgColor=#ffffff height=20 width="15%">
160​
​
​
​
​
</TD><TD bgColor=#ffffff height=20 width="18%">
18531​
​
​
​
​
</TD></TR><TR><TD bgColor=#ffffff height=20 width="16%">2.2011
</TD><TD bgColor=#ffffff height=20 width="25%">
100433​
​
​
​
​
</TD><TD bgColor=#ffffff height=20 width="12%">W969
</TD><TD bgColor=#ffffff height=20 width="15%">Bellshill
</TD><TD bgColor=#ffffff height=20 width="15%">
556​
​
​
​
​
</TD><TD bgColor=#ffffff height=20 width="18%">
17494​
​
​
​
​
</TD></TR><TR><TD bgColor=#ffffff height=20 width="16%">2.2011
</TD><TD bgColor=#ffffff height=20 width="25%">
100433​
​
​
​
​
</TD><TD bgColor=#ffffff height=20 width="12%">W969
</TD><TD bgColor=#ffffff height=20 width="15%">Pennine
</TD><TD bgColor=#ffffff height=20 width="15%">
265​
​
​
​
​
</TD><TD bgColor=#ffffff height=20 width="18%">
17494​
​
​
​
​
</TD></TR><TR><TD bgColor=#ffffff height=20 width="16%">2.2011
</TD><TD bgColor=#ffffff height=20 width="25%">
100433​
​
​
​
​
</TD><TD bgColor=#ffffff height=20 width="12%">W969
</TD><TD bgColor=#ffffff height=20 width="15%">Tuscany Park
</TD><TD bgColor=#ffffff height=20 width="15%">
253​
​
​
​
​
</TD><TD bgColor=#ffffff height=20 width="18%">
17494​
​
​
​
​
</TD></TR></TBODY></TABLE>

I have then run on its own Query 2 and get the following output:

SELECT MPLSUM.MPLWk, MPLSUM.MPLP, MPLSum.MPLD, Min(Miles) AS MINMILES FROM MPLSUM
GROUP BY MPLWk, MPLP, MPLD

<TABLE dir=ltr border=1 cellSpacing=0 borderColor=#000000 cellPadding=2 width=555><TBODY><TR><TD bgColor=#c0c0c0 height=20 width="24%">
MPLSUM.MPLWk​
​
​
​
​
​
</TD><TD bgColor=#c0c0c0 height=20 width="37%">
MPLSUM.MPLP​
​
​
​
​
​
</TD><TD bgColor=#c0c0c0 height=20 width="22%">
MPLSum.MPLD​
​
​
​
​
​
</TD><TD bgColor=#c0c0c0 height=20 width="17%">
MINMILES​
​
​
​
​
​
</TD></TR><TR><TD bgColor=#ffffff height=20 width="24%">1.2011
</TD><TD bgColor=#ffffff height=20 width="37%">
100433​
​
​
​
​
</TD><TD bgColor=#ffffff height=20 width="22%">W962
</TD><TD bgColor=#ffffff height=20 width="17%">
671​
​
​
​
​
</TD></TR><TR><TD bgColor=#ffffff height=20 width="24%">1.2011
</TD><TD bgColor=#ffffff height=20 width="37%">
100433​
​
​
​
​
</TD><TD bgColor=#ffffff height=20 width="22%">W963
</TD><TD bgColor=#ffffff height=20 width="17%">
444​
​
​
​
​
</TD></TR><TR><TD bgColor=#ffffff height=20 width="24%">1.2011
</TD><TD bgColor=#ffffff height=20 width="37%">
100433​
​
​
​
​
</TD><TD bgColor=#ffffff height=20 width="22%">W954
</TD><TD bgColor=#ffffff height=20 width="17%">
295​
​
​
​
​
</TD></TR><TR><TD bgColor=#ffffff height=20 width="24%">1.2011
</TD><TD bgColor=#ffffff height=20 width="37%">
100433​
​
​
​
​
</TD><TD bgColor=#ffffff height=20 width="22%">W16
</TD><TD bgColor=#ffffff height=20 width="17%">
311​
​
​
​
​
</TD></TR><TR><TD bgColor=#ffffff height=20 width="24%">1.2011
</TD><TD bgColor=#ffffff height=20 width="37%">
100433​
​
​
​
​
</TD><TD bgColor=#ffffff height=20 width="22%">W1
</TD><TD bgColor=#ffffff height=20 width="17%">
146​
​
​
​
​
</TD></TR><TR><TD bgColor=#ffffff height=20 width="24%">1.2011
</TD><TD bgColor=#ffffff height=20 width="37%">
100433​
​
​
​
​
</TD><TD bgColor=#ffffff height=20 width="22%">W905
</TD><TD bgColor=#ffffff height=20 width="17%">
124​
​
​
​
​
</TD></TR><TR><TD bgColor=#ffffff height=20 width="24%">1.2011
</TD><TD bgColor=#ffffff height=20 width="37%">
100433​
​
​
​
​
</TD><TD bgColor=#ffffff height=20 width="22%">W3
</TD><TD bgColor=#ffffff height=20 width="17%">
173​
​
​
​
​
</TD></TR><TR><TD bgColor=#ffffff height=20 width="24%">1.2011
</TD><TD bgColor=#ffffff height=20 width="37%">
100433​
​
​
​
​
</TD><TD bgColor=#ffffff height=20 width="22%">W922
</TD><TD bgColor=#ffffff height=20 width="17%">
190​
​
​
​
​
</TD></TR><TR><TD bgColor=#ffffff height=20 width="24%">1.2011
</TD><TD bgColor=#ffffff height=20 width="37%">
100433​
​
​
​
​
</TD><TD bgColor=#ffffff height=20 width="22%">W2
</TD><TD bgColor=#ffffff height=20 width="17%">
149​
​
​
​
​
</TD></TR><TR><TD bgColor=#ffffff height=20 width="24%">1.2011
</TD><TD bgColor=#ffffff height=20 width="37%">
100433​
​
​
​
​
</TD><TD bgColor=#ffffff height=20 width="22%">W15
</TD><TD bgColor=#ffffff height=20 width="17%">
214​
​
​
​
​
</TD></TR><TR><TD bgColor=#ffffff height=20 width="24%">1.2011
</TD><TD bgColor=#ffffff height=20 width="37%">
100433​
​
​
​
​
</TD><TD bgColor=#ffffff height=20 width="22%">W932
</TD><TD bgColor=#ffffff height=20 width="17%">
216​
​
​
​
​
</TD></TR><TR><TD bgColor=#ffffff height=20 width="24%">1.2011
</TD><TD bgColor=#ffffff height=20 width="37%">
100433​
​
​
​
​
</TD><TD bgColor=#ffffff height=20 width="22%">W13
</TD><TD bgColor=#ffffff height=20 width="17%">
196​
​
​
​
​
</TD></TR><TR><TD bgColor=#ffffff height=20 width="24%">1.2011
</TD><TD bgColor=#ffffff height=20 width="37%">
100433​
​
​
​
​
</TD><TD bgColor=#ffffff height=20 width="22%">W972
</TD><TD bgColor=#ffffff height=20 width="17%">
112​
​
​
​
​
</TD></TR><TR><TD bgColor=#ffffff height=20 width="24%">1.2011
</TD><TD bgColor=#ffffff height=20 width="37%">
100433​
​
​
​
​
</TD><TD bgColor=#ffffff height=20 width="22%">W18
</TD><TD bgColor=#ffffff height=20 width="17%">
91​
​
​
​
​
</TD></TR><TR><TD bgColor=#ffffff height=20 width="24%">1.2011
</TD><TD bgColor=#ffffff height=20 width="37%">
100433​
​
​
​
​
</TD><TD bgColor=#ffffff height=20 width="22%">W927
</TD><TD bgColor=#ffffff height=20 width="17%">
239​
​
​
​
​
</TD></TR><TR><TD bgColor=#ffffff height=20 width="24%">1.2011
</TD><TD bgColor=#ffffff height=20 width="37%">
100433​
​
​
​
​
</TD><TD bgColor=#ffffff height=20 width="22%">W926
</TD><TD bgColor=#ffffff height=20 width="17%">
287​
​
​
​
​
</TD></TR><TR><TD bgColor=#ffffff height=20 width="24%">1.2011
</TD><TD bgColor=#ffffff height=20 width="37%">
100433​
​
​
​
​
</TD><TD bgColor=#ffffff height=20 width="22%">W8
</TD><TD bgColor=#ffffff height=20 width="17%">
103​
​
​
​
​
</TD></TR><TR><TD bgColor=#ffffff height=20 width="24%">1.2011
</TD><TD bgColor=#ffffff height=20 width="37%">
100433​
​
​
​
​
</TD><TD bgColor=#ffffff height=20 width="22%">W966
</TD><TD bgColor=#ffffff height=20 width="17%">
103​
​
​
​
​
</TD></TR><TR><TD bgColor=#ffffff height=20 width="24%">1.2011
</TD><TD bgColor=#ffffff height=20 width="37%">
100433​
​
​
​
​
</TD><TD bgColor=#ffffff height=20 width="22%">W976
</TD><TD bgColor=#ffffff height=20 width="17%">
224​
​
​
​
​
</TD></TR><TR><TD bgColor=#ffffff height=20 width="24%">1.2011
</TD><TD bgColor=#ffffff height=20 width="37%">
100433​
​
​
​
​
</TD><TD bgColor=#ffffff height=20 width="22%">W14
</TD><TD bgColor=#ffffff height=20 width="17%">
201​
​
​
​
​
</TD></TR><TR><TD bgColor=#ffffff height=20 width="24%">1.2011
</TD><TD bgColor=#ffffff height=20 width="37%">
100433​
​
​
​
​
</TD><TD bgColor=#ffffff height=20 width="22%">W977
</TD><TD bgColor=#ffffff height=20 width="17%">
228​
​
​
​
​
</TD></TR><TR><TD bgColor=#ffffff height=20 width="24%">1.2011
</TD><TD bgColor=#ffffff height=20 width="37%">
100433​
​
​
​
​
</TD><TD bgColor=#ffffff height=20 width="22%">W978
</TD><TD bgColor=#ffffff height=20 width="17%">
294​
​
​
​
​
</TD></TR><TR><TD bgColor=#ffffff height=20 width="24%">1.2011
</TD><TD bgColor=#ffffff height=20 width="37%">
100433​
​
​
​
​
</TD><TD bgColor=#ffffff height=20 width="22%">W7
</TD><TD bgColor=#ffffff height=20 width="17%">
440​
​
​
​
​
</TD></TR></TBODY></TABLE>

So it looks like the GROUP BY section is working fine, it appears to be when they join the 2 select queries together.

Andy
 
Upvote 0
I am guessing that you're posting only a sample of your actual data. Try these things and see what happens:

1. Using a test database or table remove all data except what you posted on this forum and check that the query works on that - bear in mind that I have set up a table with what you have posted and the query worked.

2. If the above works, then try and solve the problem using 3 queries i.e. separate the queries as you did in the last post and then create a third query that joins the two other queries - you will need to join them on MPLWk, MPLP, MPLD And Miles.

3. Can you confirm the EXACT specification of your MPLSum table i.e. the name and data type of every field within that table so that I can ensure I'm looking at the same thing?

Cheers
DK
 
Upvote 0
You must log in or register to reply here.

Similar threads

Z
  • Question Question
Formula to match multiple things so I can get a result to tell me what I am off on.
Replies
1
Views
381
zone709
Z
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
2K
CrashOD
C
Z
  • Solved
Formula help. Match name return numbers that not in Column H
2 3
Replies
28
Views
1K
Fluff
Fluff
G
  • Question Question
The invoice number from the list
Replies
5
Views
370
Peter_SSs
Peter_SSs
rsd007
  • Solved
Going from Bottom to top how to check if a cell contains a number, and if it does, then subtract that cell from the next number in the column
2
Replies
13
Views
644
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,607
Messages
6,179,871
Members
452,948
Latest member
UsmanAli786

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top