Minimum

[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

 

[BobT]

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])

)
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.

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