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

 

[Pat Hartman]

I think you can do this in one query as long as you sort by the relevant firlds to position the record you want as the last record of a set.

Select Blast, No, Last([Hole Number]) as LastHoleNum, Last([Hole Depth]) As LastHoleDepth, Last(Tape) as LastTape Last([BCM/M]) As LastBCM, Last([ORE m]) as LastOREm, Last([ORE bcm]) As LastOREbcm
From YourTable
Group By Blast, No
Order By Blast, No, [Hole Number], [Hole Depth], [Tape], [BCM/M], [ORE m], [ORE bcm];

BTW - embedded spaces and special characters in names are poor practice.

 

[Ybbor]

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

 

[Pat Hartman]

Good. Just remember that Last() and First() ONLY work properly if you have ordered the recordset into a sequence that places the desired record first or last.

 

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

 

[Pat Hartman]

First() and Last() are SQL aggregate functions. The problem with using Min() and Max() is that they require nested queries or subselects which most people find hard to understand. I don't normally use First() and Last() because there is no equivalent in SQL Server and other RDBMS'. They are strictly Jet. So, given that most of my applications are non-Jet, I always use nested queries or subselects with Min() or Max().

 

[mresann]

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