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]

Re: Query grouping problem

jag wrote:[color=blue]
> 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[/color]

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.
[color=blue]
>
> 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[/color]

[John Winterbottom]

Re: Query grouping problem

"jag" <mdee@benet.net .au> wrote in message
news:40d82705$1 @quokka.wn.com. au...[color=blue]
> 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?
>[/color]


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]

Re: Query grouping problem

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
resource@pcdata sheet.com

https://www.pcdatasheet.com

"jag" <mdee@benet.net .au> wrote in message news:40d82705$1 @quokka.wn.com. au...[color=blue]
> 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
>
>
>[/color]

[John Winterbottom]

Re: Query grouping problem

"PC Datasheet" <nospam@nospam. spam> wrote in message
news:wr_Bc.2255 0$Y3.8510@newsr ead2.news.atl.e arthlink.net...[color=blue]
> Dee,
>
> I create applications that integrate a Palm Pilot with Access. The Palm[/color]
Pilot[color=blue]
> is programmed for data entry and after data is entered in the Palm Pilot,[/color]
the[color=blue]
> Palm Pilot is synchronized to an Access database and the data entered in[/color]
the[color=blue]
> Palm Pilot is transferred to the Access database. You could take your Palm[/color]
Pilot[color=blue]
> to the field, enter your field data and after returning to your office,[/color]
transfer[color=blue]
> the data to your database. Email me at my email address below if you are
> interested.[/color]


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]

Re: Query grouping problem

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" <assaynet@hotma il.com> wrote in message
news:2jquq1F11g f2sU1@uni-berlin.de...[color=blue]
> "jag" <mdee@benet.net .au> wrote in message
> news:40d82705$1 @quokka.wn.com. au...[color=green]
> > 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?
> >[/color]
>
>
> 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[/color]
e.g.[color=blue]
> a separate table of hole depths in metres. Alternatively, get your boss to
> buy a good core-logging program :-)
>
>
>
>
>
>
>
>
>
>[/color]