Query grouping problem

[jag]

Hi

i have a drill hole geology table with information as follows

holeid depthfrom depth to plot1

hwrc1 0 1 cuf
hwrc1 1 2 cuf
hwrc1 2 4 cup
hwrc1 4 6 cuf

i would like to composite the geology to get the following

hwrc1 1 2 cuf
hwrc1 2 4 cup
hwrc1 4 6 cuf

i have a query where i have grouped by 'holeid' and 'plot1' and min of
'depth from' and max of 'depth to'

this query retrieves the following

hwrc 1 6 cuf
hwrc 2 4 cup
how can i retrieve the correct data without overlapping depth intervals?

thanks

dee


-------------------------------------------------------------------------
FIGHT BACK AGAINST SPAM!
Download Spam Inspector, the Award Winning Anti-Spam Filter
http://mail.giantcompany.com

[Salad]

jag wrote:

Hi

i have a drill hole geology table with information as follows

holeid depthfrom depth to plot1

hwrc1 0 1 cuf
hwrc1 1 2 cuf
hwrc1 2 4 cup
hwrc1 4 6 cuf

i would like to composite the geology to get the following

hwrc1 1 2 cuf
hwrc1 2 4 cup
hwrc1 4 6 cuf
From your example I would exclude all records with 0 in the DepthFrom
field. That will work.

If this is a sample and my solution will not work, you are SOL.

IOW, you need to redefine your logic and add another field. As you add
records into a batch, run a sub/function that sets groups/breakpoints.
Maybe a field called Sequence. I see no other way based on the info you
have in this question.

i have a query where i have grouped by 'holeid' and 'plot1' and min of
'depth from' and max of 'depth to'

this query retrieves the following

hwrc 1 6 cuf
hwrc 2 4 cup
how can i retrieve the correct data without overlapping depth intervals?

thanks

dee

[John Winterbottom]

"jag" <md**@benet.net.au> wrote in message
news:40********@quokka.wn.com.au...

Hi

i have a drill hole geology table with information as follows

holeid depthfrom depth to plot1

hwrc1 0 1 cuf
hwrc1 1 2 cuf
hwrc1 2 4 cup
hwrc1 4 6 cuf

i would like to composite the geology to get the following

hwrc1 1 2 cuf
hwrc1 2 4 cup
hwrc1 4 6 cuf

i have a query where i have grouped by 'holeid' and 'plot1' and min of
'depth from' and max of 'depth to'

this query retrieves the following

hwrc 1 6 cuf
hwrc 2 4 cup
how can i retrieve the correct data without overlapping depth intervals?

if the core intervals are contiguous, (i.e. "depth from" for the scceeding
interval always equals "depth to" for the preceeding interval) you can do:

select holeid, min(depthfrom) as depFrom,
max(depthto) as depTo, prevPlot AS plot
from
(
select b1.holeid, b1.depthfrom, b1.depthto, b1.plot1,
nz (
(
select b2.plot1
from boreholes as b2
where b2.holeid = b1.holeid
and b2.depthto = b1.depthfrom
),b1.Plot1
) as prevPlot
from boreholes as b1
) as A
group by holeid, plot1, prevplot
order by holeid, min(depthfrom)

If the intervals are not contiguous then you need to make them so with e.g.
a separate table of hole depths in metres. Alternatively, get your boss to
buy a good core-logging program :-)

[PC Datasheet]

Dee,

I create applications that integrate a Palm Pilot with Access. The Palm Pilot
is programmed for data entry and after data is entered in the Palm Pilot, the
Palm Pilot is synchronized to an Access database and the data entered in the
Palm Pilot is transferred to the Access database. You could take your Palm Pilot
to the field, enter your field data and after returning to your office, transfer
the data to your database. Email me at my email address below if you are
interested.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com
"jag" <md**@benet.net.au> wrote in message news:40********@quokka.wn.com.au...

Hi

i have a drill hole geology table with information as follows

holeid depthfrom depth to plot1

hwrc1 0 1 cuf
hwrc1 1 2 cuf
hwrc1 2 4 cup
hwrc1 4 6 cuf

i would like to composite the geology to get the following

hwrc1 1 2 cuf
hwrc1 2 4 cup
hwrc1 4 6 cuf

i have a query where i have grouped by 'holeid' and 'plot1' and min of
'depth from' and max of 'depth to'

this query retrieves the following

hwrc 1 6 cuf
hwrc 2 4 cup
how can i retrieve the correct data without overlapping depth intervals?

thanks

dee


-------------------------------------------------------------------------
FIGHT BACK AGAINST SPAM!
Download Spam Inspector, the Award Winning Anti-Spam Filter
http://mail.giantcompany.com

[John Winterbottom]

"PC Datasheet" <no****@nospam.spam> wrote in message
news:wr*****************@newsread2.news.atl.earthl ink.net...

Dee,

I create applications that integrate a Palm Pilot with Access. The Palm Pilot is programmed for data entry and after data is entered in the Palm Pilot, the Palm Pilot is synchronized to an Access database and the data entered in the Palm Pilot is transferred to the Access database. You could take your Palm Pilot to the field, enter your field data and after returning to your office, transfer the data to your database. Email me at my email address below if you are
interested.

Way to go Steve.

Not only do you *not* answer the guys question, (because, of course, you
can't), you also use the opportunity to send an unsolicited advertisement
for a service he didn't request.
--
p.s. Note to dee. I would avoid this guy like the plague. His knowlege of
Access is about on a par with his knowlege of diamond drilling.

[jag]

Yes John the intervals are contiguous.
Thankyou , I will try this out
Dee

--
-------------------------------------------------------------------------
FIGHT BACK AGAINST SPAM!
Download Spam Inspector, the Award Winning Anti-Spam Filter
http://mail.giantcompany.com
"John Winterbottom" <as******@hotmail.com> wrote in message
news:2j*************@uni-berlin.de...

"jag" <md**@benet.net.au> wrote in message
news:40********@quokka.wn.com.au...

Hi

i have a drill hole geology table with information as follows

holeid depthfrom depth to plot1

hwrc1 0 1 cuf
hwrc1 1 2 cuf
hwrc1 2 4 cup
hwrc1 4 6 cuf

i would like to composite the geology to get the following

hwrc1 1 2 cuf
hwrc1 2 4 cup
hwrc1 4 6 cuf

i have a query where i have grouped by 'holeid' and 'plot1' and min of
'depth from' and max of 'depth to'

this query retrieves the following

hwrc 1 6 cuf
hwrc 2 4 cup
how can i retrieve the correct data without overlapping depth intervals?
if the core intervals are contiguous, (i.e. "depth from" for the scceeding
interval always equals "depth to" for the preceeding interval) you can do:

select holeid, min(depthfrom) as depFrom,
max(depthto) as depTo, prevPlot AS plot
from
(
select b1.holeid, b1.depthfrom, b1.depthto, b1.plot1,
nz (
(
select b2.plot1
from boreholes as b2
where b2.holeid = b1.holeid
and b2.depthto = b1.depthfrom
),b1.Plot1
) as prevPlot
from boreholes as b1
) as A
group by holeid, plot1, prevplot
order by holeid, min(depthfrom)

If the intervals are not contiguous then you need to make them so with

e.g. a separate table of hole depths in metres. Alternatively, get your boss to
buy a good core-logging program :-)