Minimum

  • Thread starter Thread starter Martin
  • Start date Start date
M

Martin

Hello,

I have a query that works for me except I need to show just the first record
at each change in a record in a field.

Here is the SQL:

SELECT Import.Postcode, [FN Branches].Brand, [FN Branches].[Branch Name],
[FN Branches].Sortcode,
([Part1]-[Part2])*([Part3]-[Part4])+([Part1]-[Part2])*([Part3]-[Part4]) AS
DistanceCalc, Sqr([DistanceCalc])/1000 AS [Distance(km)],
Sqr([DistanceCalc])/1000*0.6214 AS [Distance(miles)]
FROM Import, [FN Branches]
WHERE ((([FN Branches].Brand)="NatWest" Or ([FN Branches].Brand)="RBS"))
ORDER BY Import.Postcode, ([Import]![X]-[FN Branches]![X])*([Import]![X]-[FN
Branches]![X])+([Import]![Y]-[FN Branches]![Y])*([Import]![Y]-[FN
Branches]![Y]);

There is no link in the tables which is why I cant just use the min
function. I am calculating distances using a value in the Import table and
the FN Branches table.

Is there anyway to find the minimum value of the DistanceCalc field.

I appreciate this maybe quite unique but any help or advice would be greatly
appreciated.

Martin
 
Make an inline query out of it. That is, you have the query now that
calculates on the fly your DistanceCalc, now query that query to get the min:

SELECT Import.Postcode, MIN(DISTANCECALC)
from
(
SELECT Import.Postcode, [FN Branches].Brand, [FN Branches].[Branch Name],
[FN Branches].Sortcode,
([Part1]-[Part2])*([Part3]-[Part4])+([Part1]-[Part2])*([Part3]-[Part4]) AS
DistanceCalc, Sqr([DistanceCalc])/1000 AS [Distance(km)],
Sqr([DistanceCalc])/1000*0.6214 AS [Distance(miles)]
FROM Import, [FN Branches]
WHERE ((([FN Branches].Brand)="NatWest" Or ([FN Branches].Brand)="RBS"))
ORDER BY Import.Postcode, ([Import]![X]-[FN Branches]![X])*([Import]![X]-[FN
Branches]![X])+([Import]![Y]-[FN Branches]![Y])*([Import]![Y]-[FN
Branches]![Y])
Click to expand...
)
GROUP BY Import.Postcode

Or group by whatever you want. You could also save your original query and
create a whole new query based on the first.



Martin said:
Hello,

I have a query that works for me except I need to show just the first record
at each change in a record in a field.

Here is the SQL:

SELECT Import.Postcode, [FN Branches].Brand, [FN Branches].[Branch Name],
[FN Branches].Sortcode,
([Part1]-[Part2])*([Part3]-[Part4])+([Part1]-[Part2])*([Part3]-[Part4]) AS
DistanceCalc, Sqr([DistanceCalc])/1000 AS [Distance(km)],
Sqr([DistanceCalc])/1000*0.6214 AS [Distance(miles)]
FROM Import, [FN Branches]
WHERE ((([FN Branches].Brand)="NatWest" Or ([FN Branches].Brand)="RBS"))
ORDER BY Import.Postcode, ([Import]![X]-[FN Branches]![X])*([Import]![X]-[FN
Branches]![X])+([Import]![Y]-[FN Branches]![Y])*([Import]![Y]-[FN
Branches]![Y]);

There is no link in the tables which is why I cant just use the min
function. I am calculating distances using a value in the Import table and
the FN Branches table.

Is there anyway to find the minimum value of the DistanceCalc field.

I appreciate this maybe quite unique but any help or advice would be greatly
appreciated.

Martin
Click to expand...
 
Back
Top