Show only one occurance of data in a field

[Ybbor]

I have the following table:
Blast No Hole Number Hole Depth Tape BCM/M ORE m ORE bcm
1.00 200 7.90 9.75 7.90 77.03
1.00 200 8.20 9.75 8.20 79.95
1.00 201 8.00 9.75 0.00 0.00
1.00 201 8.10 9.75 0.00 0.00
1.00 202 7.80 9.75 7.80 76.05
1.00 203 7.20 9.75 7.20 70.20
1.00 204 7.90 9.75 7.90 77.03
1.00 205 7.70 9.75 7.70 75.08
1.00 206 2.80 9.75 2.80 27.30
1.00 207 7.40 9.75 7.40 72.15
1.00 208 7.80 9.75 7.80 76.05

And i want to show only the last occurance of the Hole Number field. e.g:

Blast No Hole Number Hole Depth Tape BCM/M ORE m ORE bcm

1.00 200 8.20 9.75 8.20 79.95

1.00 201 8.10 9.75 0.00 0.00
1.00 202 7.80 9.75 7.80 76.05
1.00 203 7.20 9.75 7.20 70.20
1.00 204 7.90 9.75 7.90 77.03
1.00 205 7.70 9.75 7.70 75.08
1.00 206 2.80 9.75 2.80 27.30
1.00 207 7.40 9.75 7.40 72.15
1.00 208 7.80 9.75 7.80 76.05

However I cannot figure out if the LAST function dose this. When i have applied it hole 202 & one of the 200's dissapeared. However 202 should not, but 201 should??

 

[john471]

Refer to Pat Hartman's entry in this thread. It may shed some light on why you are getting the results you are getting, and what you need to do to get the results you want

 

[mresann]

Although I think your question is probably more complicated that you wrote about, from the information you gave me you wanted the record with the MAXIMUM Hole Number value. The query is easy, if you are just looking for the top record. Substitute table and field names as appropriate.

SELECT TOP 1 [Table1].[*] From Table1 ORDER BY [Table1].[Hole Number] DESC;

Does this work?

 

[john471]

I understood your request to mean that you want:

For each Blast Number and Hole Number combination, retrieve the data that corresponds to the deepest (maximum) depth.

Is that what you are trying to get ?

There are probably those who could tell you how to do that in one step, but I would break it into two steps.

1) For each Blast Number and Hole Number combination, retrieve the deepest (maximum) depth.
2) Retrieve the corresponding data

SQL for step 1) - I named this as "qryMaxDepthByBlastAndHole" - which is referenced in step 2

SELECT tblYourTableName.[Blast No], tblYourTableName.[Hole Number], Max(tblYourTableName.[Hole Depth]) AS [MaxOfHole Depth]
FROM tblYourTableName
GROUP BY tblYourTableName.[Blast No], tblYourTableName.[Hole Number];

SQL for step 2) - I named this as "qryDataForMaxDepthByBlastAndHole" - you can call it whatever you like

SELECT tblYourTableName.*
FROM tblYourTableName INNER JOIN qryMaxDepthByBlastAndHole ON (tblYourTableName.[Hole Depth] = qryMaxDepthByBlastAndHole.[MaxOfHole Depth]) AND (tblYourTableName.[Hole Number] = qryMaxDepthByBlastAndHole.[Hole Number]) AND (tblYourTableName.[Blast No] = qryMaxDepthByBlastAndHole.[Blast No]);

Don't know wether your needs were closer to my interpretation or mresann's, but now you have options....



Result (including my assumptions about column (field) names) :-

'Blast No'	'Hole Number'	'Hole Depth'	'Tape BCM/M'	'ORE m'	'ORE bcm'
1.00		200		8.2		9.75		8.2	79.95
1.00		201		8.1		9.75		0	0
1.00		202		7.8		9.75		7.8	76.05
1.00		203		7.2		9.75		7.2	70.2
1.00		204		7.9		9.75		7.9	77.03
1.00		205		7.7		9.75		7.7	75.08
1.00		206		2.8		9.75		2.8	27.3
1.00		207		7.4		9.75		7.4	72.15
1.00		208		7.8		9.75		7.8	76.05

HTH

Regards

John

 

[john471]

TIP : Be sure you are storing the data you want to "max" as a number type (probably Single Precision Floating Point will do, if the example you gave is representative of your data), and not as text, otherwise you could get undesireable results (depending on the data) !

See illustration attached

 

[Ybbor]

Spot on with the help guys. I was after the LAST function. Thank you!!

 

[mresann]

I agree. In fact, I would recommend not using First or Last if possible and using SQL aggregate functions to obtain the desired record.

 

[mresann]

OOPS, I meant Jet, not SQL... sorry.