Query based on two sets of depth intervals

I have two tables (this all has to do with drilling).  The first table 
consists of a drill hole label, from depth, to depth, and geology (i.e. what 
rock is present at that interval).  My second table has a drill hole label, 
sample number, from depth, to depth, and nickel content.  I want to be able 
to query out what geology is present (from table 1) for each sample (table 2) 
interval.  The geology intervals do not necessarily match the sample 
intervals.

I'm unsure how to code this.  Is there anyone out there who could give me 
some pointers?  Anything I've tried so far has given me bad results.

Casa

0
Utf
2/22/2008 3:05:02 PM
access.queries 6343 articles. 1 followers. Follow

4 Replies
559 Views

Similar Articles

[PageSpeed] 57

Please post some sample data from each table with the proper table and field 
names.

Then show an example of how you would like to see the data returned from the 
query.
-- 
Jerry Whittle, Microsoft Access MVP 
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

"Casa" wrote:

> I have two tables (this all has to do with drilling).  The first table 
> consists of a drill hole label, from depth, to depth, and geology (i.e. what 
> rock is present at that interval).  My second table has a drill hole label, 
> sample number, from depth, to depth, and nickel content.  I want to be able 
> to query out what geology is present (from table 1) for each sample (table 2) 
> interval.  The geology intervals do not necessarily match the sample 
> intervals.
> 
> I'm unsure how to code this.  Is there anyone out there who could give me 
> some pointers?  Anything I've tried so far has given me bad results.
> 
> Casa
> 
0
Utf
2/22/2008 3:42:02 PM
Here is some example data:

Geology table:

DH Label  From(metres)  To(metres)  Geology
1             0                    1                 UM
1             1                    5                 GB
1             5                    8.5              SH
1             8.5                 11.2             MV

Samples table:

DH Label  Sample#  From(metres)  To(metres)  Nickel(ppm)
1             100          1                    2                 3000
1             101          2                    3                 2200
1             102          3                    4                 1600
1             103          4                    5                 2000
1             104          5                    6                 560
1             105          6                    7                 870
1             106          7                    8                 1500
1             107          8                    9                 2400
1             108          9                    10               3080     

I'd like to see:

DH Label  Sample#  From(metres)  To(metres)  Nickel(ppm)  Geology
1             100          1                    2                 3000       
    GB
1             101          2                    3                 2200       
    GB
1             102          3                    4                 1600       
    GB
1             103          4                    5                 2000       
    GB
1             104          5                    6                 560        
     SH
1             105          6                    7                 870        
     SH
1             106          7                    8                 1500       
    SH
1             107          8                    9                 2400       
    SH,MV
1             108          9                    10               3080        
   MV

So all I'd like to do is join the geology column (from the geology table) to 
the samples table.  Is this possible? If a sample spans two different 
geological units, is it possible to have Access list both units (as presented 
in my example sample interval 8-9 metres)?

Any help would be appreciated.

Casa


"Jerry Whittle" wrote:

> Please post some sample data from each table with the proper table and field 
> names.
> 
> Then show an example of how you would like to see the data returned from the 
> query.
> -- 
> Jerry Whittle, Microsoft Access MVP 
> Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
> 
> "Casa" wrote:
> 
> > I have two tables (this all has to do with drilling).  The first table 
> > consists of a drill hole label, from depth, to depth, and geology (i.e. what 
> > rock is present at that interval).  My second table has a drill hole label, 
> > sample number, from depth, to depth, and nickel content.  I want to be able 
> > to query out what geology is present (from table 1) for each sample (table 2) 
> > interval.  The geology intervals do not necessarily match the sample 
> > intervals.
> > 
> > I'm unsure how to code this.  Is there anyone out there who could give me 
> > some pointers?  Anything I've tried so far has given me bad results.
> > 
> > Casa
> > 
0
Utf
2/22/2008 9:00:01 PM
Will your sample intervals always be in 1m increments, could you have:

DH Label  Sample#  From(metres)  To(metres)  Nickel(ppm)
 1             100          1                    2                 3000
 1             101          2                    3                 2200
 1             102          3                    6                 1600
 1             103          6                   10                2000

If they will always be in 1m increments, this should work:

SELECT S.[DH Label], S.[Sample#], S.[From(meters)],
            S.[To(meters)], S.[Nickel(ppm)], G.Geology
FROM Samples S, Geology G
WHERE S.DH = G.DH
AND S.[From(meters)] >= BETWEEN G.[From(meters)] 
AND S.[From(meters)] <= G.[To(meters)]
AND S.[To(meters)] >= G.[From(meters)] 
AND S.[To(meters)] <= G.[To(meters)]

HTH
Dale
-- 
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



"Casa" wrote:

> Here is some example data:
> 
> Geology table:
> 
> DH Label  From(metres)  To(metres)  Geology
> 1             0                    1                 UM
> 1             1                    5                 GB
> 1             5                    8.5              SH
> 1             8.5                 11.2             MV
> 
> Samples table:
> 
> DH Label  Sample#  From(metres)  To(metres)  Nickel(ppm)
> 1             100          1                    2                 3000
> 1             101          2                    3                 2200
> 1             102          3                    4                 1600
> 1             103          4                    5                 2000
> 1             104          5                    6                 560
> 1             105          6                    7                 870
> 1             106          7                    8                 1500
> 1             107          8                    9                 2400
> 1             108          9                    10               3080     
> 
> I'd like to see:
> 
> DH Label  Sample#  From(metres)  To(metres)  Nickel(ppm)  Geology
> 1             100          1                    2                 3000       
>     GB
> 1             101          2                    3                 2200       
>     GB
> 1             102          3                    4                 1600       
>     GB
> 1             103          4                    5                 2000       
>     GB
> 1             104          5                    6                 560        
>      SH
> 1             105          6                    7                 870        
>      SH
> 1             106          7                    8                 1500       
>     SH
> 1             107          8                    9                 2400       
>     SH,MV
> 1             108          9                    10               3080        
>    MV
> 
> So all I'd like to do is join the geology column (from the geology table) to 
> the samples table.  Is this possible? If a sample spans two different 
> geological units, is it possible to have Access list both units (as presented 
> in my example sample interval 8-9 metres)?
> 
> Any help would be appreciated.
> 
> Casa
> 
> 
> "Jerry Whittle" wrote:
> 
> > Please post some sample data from each table with the proper table and field 
> > names.
> > 
> > Then show an example of how you would like to see the data returned from the 
> > query.
> > -- 
> > Jerry Whittle, Microsoft Access MVP 
> > Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
> > 
> > "Casa" wrote:
> > 
> > > I have two tables (this all has to do with drilling).  The first table 
> > > consists of a drill hole label, from depth, to depth, and geology (i.e. what 
> > > rock is present at that interval).  My second table has a drill hole label, 
> > > sample number, from depth, to depth, and nickel content.  I want to be able 
> > > to query out what geology is present (from table 1) for each sample (table 2) 
> > > interval.  The geology intervals do not necessarily match the sample 
> > > intervals.
> > > 
> > > I'm unsure how to code this.  Is there anyone out there who could give me 
> > > some pointers?  Anything I've tried so far has given me bad results.
> > > 
> > > Casa
> > > 
0
Utf
2/22/2008 9:17:00 PM
This query is close but no cigar as it only returns one Geology:

SELECT Samples.[DH Label],
 Samples.[Sample#],
 Samples.[From(metres)],
 Samples.[To(metres)],
 Samples.[Nickel(ppm)],
(Select Top 1 Geology.Geology 
  FROM Geology
  WHERE Geology.[DH Label] = Samples.[DH Label]
  AND samples.[From(metres)] Between Geology.[From(metres)] 
      And Geology.[To(metres)]) as Geologies
FROM Samples
GROUP BY  Samples.[DH Label],
 Samples.[Sample#],
 Samples.[From(metres)],
 Samples.[To(metres)],
 Samples.[Nickel(ppm)] ;

-- 
Jerry Whittle, Microsoft Access MVP 
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Casa" wrote:

> Here is some example data:
> 
> Geology table:
> 
> DH Label  From(metres)  To(metres)  Geology
> 1             0                    1                 UM
> 1             1                    5                 GB
> 1             5                    8.5              SH
> 1             8.5                 11.2             MV
> 
> Samples table:
> 
> DH Label  Sample#  From(metres)  To(metres)  Nickel(ppm)
> 1             100          1                    2                 3000
> 1             101          2                    3                 2200
> 1             102          3                    4                 1600
> 1             103          4                    5                 2000
> 1             104          5                    6                 560
> 1             105          6                    7                 870
> 1             106          7                    8                 1500
> 1             107          8                    9                 2400
> 1             108          9                    10               3080     
> 
> I'd like to see:
> 
> DH Label  Sample#  From(metres)  To(metres)  Nickel(ppm)  Geology
> 1             100          1                    2                 3000       
>     GB
> 1             101          2                    3                 2200       
>     GB
> 1             102          3                    4                 1600       
>     GB
> 1             103          4                    5                 2000       
>     GB
> 1             104          5                    6                 560        
>      SH
> 1             105          6                    7                 870        
>      SH
> 1             106          7                    8                 1500       
>     SH
> 1             107          8                    9                 2400       
>     SH,MV
> 1             108          9                    10               3080        
>    MV
> 
> So all I'd like to do is join the geology column (from the geology table) to 
> the samples table.  Is this possible? If a sample spans two different 
> geological units, is it possible to have Access list both units (as presented 
> in my example sample interval 8-9 metres)?
> 
> Any help would be appreciated.
> 
> Casa
> 
> 
> "Jerry Whittle" wrote:
> 
> > Please post some sample data from each table with the proper table and field 
> > names.
> > 
> > Then show an example of how you would like to see the data returned from the 
> > query.
> > -- 
> > Jerry Whittle, Microsoft Access MVP 
> > Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
> > 
> > "Casa" wrote:
> > 
> > > I have two tables (this all has to do with drilling).  The first table 
> > > consists of a drill hole label, from depth, to depth, and geology (i.e. what 
> > > rock is present at that interval).  My second table has a drill hole label, 
> > > sample number, from depth, to depth, and nickel content.  I want to be able 
> > > to query out what geology is present (from table 1) for each sample (table 2) 
> > > interval.  The geology intervals do not necessarily match the sample 
> > > intervals.
> > > 
> > > I'm unsure how to code this.  Is there anyone out there who could give me 
> > > some pointers?  Anything I've tried so far has given me bad results.
> > > 
> > > Casa
> > > 
0
Utf
2/22/2008 9:52:01 PM
Reply:

Similar Artilces:

Setting Chart X and Y values indirectly
I would like to set X-Values in my chart in a cell of a worksheet, so that when the range of my x-values changes I will not have to go into the wizard and type it again. For example, I want to use the following X-values in my chart: =DATA!$I$2:$I$20 But instead of typing the above in the X Values: field in the 'Source Data / Series' wizard, I would like to type it, say, in cell $A$1 of 'Sheet1', and then type in the X Values field in the 'Source Data / Series' wizard the following: =Sheet1!$A$1 I tried it and it does not work. Can you please let me know if th...

SUMIF between two dates (or a specific Month & Year)
A B 1 DATE AMOUNT 2 4/10/2006 $36.52 3 2/16/2007 $45.12 4 3/14/2007 $65.33 5 4/20/2007 $29.15 6 4/21/2007 $45.20 7 4/22/2007 $47.15 8 5/14/2007 $41.03 9 5/15/2007 $16.21 10 Apr-07 $0.00 I would like to SUM April of 2007's amounts. I would like to put the month I am looking for in a specific cell (say A10 in this example) and the formula in B10 would SUMIF column A has a date in the range of April 1 to April 30, 2007 and return $121.50. Thank you for any help. John13 On Mon, 06 Aug 2007 00:33:57 -0000, John13 <johnasmith13@gmail.com> wrote: > A...

Query to join records according to "hierarchy"
I didn't know how else to describe it. I have records with this structure: Fields: Name; Last Name; Group John; Brown; 1-1 Carl; Rogers; 1-1 Brian; Mann; 1-2 Charles; Grant; 2-1 Eddie; Murphy; 2-2 Carmen; Elec; 3-1 Ellen; Smith; 3-2 ......and so on What I need is to combine the field Name in group 1-1 to 1-2, 2-2, 3-2. Then 2-1 with 2-2, 3-2. Then 3-1 with 3-2. Is this possible? If so, how can I do it? Thanks for the help. On Jul 4, 11:55 am, sergio.prin...@gmail.com wrote: > I didn't know...

How To Stop Acces From Update The Record When The Subform Query Is On Focus?
Hi I need to know how to stop acces from update the record when the subform query is on focus? Thanks In news:1177155216.699820.268130@d57g2000hsg.googlegroups.com, Chipcom <bz1977@gmail.com> wrote: > > I need to know how to stop acces from update the record when the > subform query is on focus? If you mean that you want to keep the main form's record from being saved when the subform gets the focus, the only ways you can do that are: 1. Have the main form be unbound. In that case your own code has to do the job of reading the record, assigning its field values to ...

Setting the default contact list for Outlook 2007
When I create an email message, and click on "To" to search my contact list, by default it always goes to the Global Address List. Is there a way to make my Contact List the default? "brenda" <brenda@discussions.microsoft.com> wrote in message news:FED962FD-27E7-42BA-B0CE-FEA9256DDEC4@microsoft.com... > When I create an email message, and click on "To" to search my contact > list, > by default it always goes to the Global Address List. Is there a way to > make my Contact List the default? When that Address Book window opens, click Tool...

Query Output
Help, I am working with make table queries. I have a A make table query and a B make table query each with various information that the query has preformed. I am trying to get the following output into a text file. A Make Table Information B Make Table Information A Make Table Information B Make Table Information etc. There are over 300 items in each table. Each A Corresponds with a B. I need to keep the 2 records separate for where i have to upload them. I tried having a common identifier for the two tables and creating a big query with all the information on, but i ...

Which query is being used by which form/report?
Is there any way to find out whether a query is being used, and by which reports or forms? Thanks, Dean S This will list the RecordSource property of your forms and reports: Public Function ShowSources() Dim accObj As AccessObject Dim strDoc As String For Each accObj In CurrentProject.AllForms strDoc = accObj.Name DoCmd.OpenForm strDoc, acDesign, WindowMode:=acHidden Debug.Print strDoc, Forms(strDoc).RecordSource DoCmd.Close acForm, strDoc Next For Each accObj In CurrentProject.AllReports strDoc = accObj.Name DoCmd.Open...

Set sale pricing; ; 25.00% marked up from cost
Using this scheme, I wanted it to be reported to MS that there is an issue here, the marked up from cost is not the correct 25%...you can set the % to whatever, it just doesn't work! -- Thank You Vince :) ...

OL2003: Multiple folder view
We're using Outlook 2003 in Exchange environment and we're using common "office calendar" in exchange public folders for all kinds of little happenings. When in calendar view, I can check the "office calender" and view beside my own calender. However, when I restart Outlook and return to calendars, I have to check the "office calender" again. - Is there any way to make Outlook remember this setting? Thanks, Jussi No, it's not persistent. From Outlook's Help task pane (F1 to open it) choose Communities and enter a suggestion. So that those o...

Match two tables using unique ID number
I have two tables using item number as the identifer for each row. It is possible that their are items in either tables that do not match the other table. I need to match the two tables so that each unique item is in the same row and unique items in each table are on a seperate row. So you only care about the key identifier columns??? If yes, create a new worksheet. Copy the key column from the first worksheet into column A. copy the key column from the second worksheet into column B. Add headers to row 1 (if you don't have them already). Then run this macro: Option Explicit Sub t...

Settings Change
I've set up my e-mail account and been using it for about a year without any problems. Recently, I have had some sent and receive errors. When checking the settings I've noticed the following Correct settings User me@myhost.com Password: ***** POP: mail.myhost.com SMTP: mail.myhost.com Settings changed by...? User me@myhost.com / mail.myhost.com Password: ***** POP: localhost SMTP: mail.myhost.com Any ideas why this happens? I've got a firewall and scanned for viruses - looks like I'm clean. This most likely caused by your virusscanner and/or spamfilter acting lik...

Prefered Provider and pin based transactions
Do I understand it correctly that pin based debit transactions will work from within RMS with a verifone 1000se and NPC or does it mean that it only works with Citibank? My parttimers are not PC savvy so they need to be able to everything through RMS interface. Thanks, M Manny, Its not a Citibank issue, its a Vital issue. If NPC can provide a Vital connection, you're good to go. -- * Get Secure! - www.microsoft.com/security You must be using Outlook Express or some other type of newsgroup reader to see and download the file attachment. If you are not using a reader, follow the l...

delete user define setting
Hi I have previously entered a user defined chart in the custom types in Excel 2002, however, no loger use this and want to delete it in order to use the default setting when pressing "F11" how do i get rid of it? thank in advance Brian -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.aspx/excel-chart/200701/1 Select a chart. Go to Chart menu > Chart Type > Custom Types tab. Click User Defined button, select Default under Chart Type, and click Delete button. This resets the default to the original (default) default chart type. - Jon ------- Jon P...

Create two entries in a table with one entry in a form.
I have a database that is going to be used to track the movement of parts through the material handling group. This group receives parts in from the warehouse (Which adds the received qty to the material handler's inventory), upon request they release parts to the repair department (which deducts the qty from the material handler's inventory). When the repair department has complete the repair of a part it is then sent back to the material handling department who in turn sends the parts to the repack department to be packed and ready for shipment. When the repair center has completed ...

Mac problems opening excel files when connected to two windows shares
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel I'm noticing a strange problem when I try to open excel files when I connect to a second windows server share. I'm getting a cannot open excel file error. Not sitting at my work mac computer at the moment so not quite sure that was the exact error. <br><br>This error is occuring on both leopard and snow leopard with Office 2008 for mac. <br><br>The two windows servers are being connected to by smb://server1/common and smb://server2/common. <br><br>If i connect to each one i...

How to choose a form record based on selection from a subform?
I have a main form (in Access 2007) with a few fields for the user to fill in. Below the data entry fields is a subform that lists all the records from the same query the main form is based on. This is to make it easy for the users to see the current record set as well as a quick way for them to select records for editing. I want to make it so that if the user clicks on a subform record, the main form will go to that record for editing. So far I put a hidden textbox on the main form and set it's Control Source to "=[Subform]![RecID]. The textbox is updated with the Rec...

Send/Receive settings reverting on close
Help! This keeps happening to me. I try to make changes to the send/receive settings in Outlook 2003, but when I close Outlook and restart, the settings revert to the original settings. This time, I'm trying to check off "My outgoing mail server requires authentication". Everything is fine until I close and restart Outlook - then the option is UNCHECKED again. I'm logged in (always) as an administrator of my computer. ...

Customer record count query
Hi everbody - I am struggling here - I need a query showing the number of customers that I have and also a record count as well. The result should look like this for eg> name address city state phone mary 555 main st miami florida 2015551212 henry 896 mott st orlando florida 201777888 total records = 2 select custname, address1, city, state, phone from RM00101 Compute count(*) -- Charles Allen, MVP "Michael@nyresume.com" wrote: > Hi everbody - > I am struggling here - I need a query showing the number of customers that I > h...

Delete Queries using Unmatched Query
Reading many helpful responses on this site, but unfortunately none of them seem to remedy my current issue. Just learning Access, took a quick college prep course, got the basics. Now I am expanding freelance. SO the scenarios is my test idea that I have intent to apply on larger scale. I have a database with 15 records... lets say reservations (tbl_reservations) for a hotel. Used a maketable to copy that table because it gets updated often to cancelled or complete status. So I filter out the 9 of those 15 reservations which are still in pending status and make the new table (tbl...

In Dialog Based application are you Create View class.
Hai, In Dialog Based application are you Create View class. Thank you, Bye You should not need a view a MFC Dialog based appliation. If you do, look at SDI/MDI instead. You can use a CFormView based view which uses a dialog as a template in Doc/View paradigm. --------- Ajay Kalra ajaykalra@yahoo.com You can create a view in a dialog: http://www.codeproject.com/docview/dfv.asp But I haven't seen that done very often. I tend to just use an SDI or MDI framework for anything that needs a view class. Tom "jagadeesh" <jagadeeshbabu.mca@gmail.com> wrote in mes...

lstBox Record Set won't change
With the code below I'm attempting to change the row source for lstRNnotesLU (a list box) based on a value entered on the frmVisitNewEdit field called ReasonForVisitTest. For some reason it is not working. Then record set is not changed by the code. It continues to use the underlying query for the rowsource rather than the rowsource based on the code below. Your help is greatly appreciated. Thanks Rob ****************************************************************************************************************** Dim T_Visit As String T_Visit = Nz(Forms!frmPtDemographicNe...

Delete two columns with formula
I need to delete 2 columns (with formula) and only display the last two columns. When I delete the first 2 columns, the last two columns display #REF!. I know I can copy the info I need to a new sheet and paste special the value. Is there a way to fix this on the current sheet I am working on. Thanks. "ED" <ED@discussions.microsoft.com> wrote in message news:1944C248-624E-4D95-AD8E-86412991A77F@microsoft.com... >I need to delete 2 columns (with formula) and only display the last two > columns. > > When I delete the first 2 columns, the last two columns di...

How do I make a chart based upon time sheets?
I have the data as follows: Name Time In Time Out Time In Time Out (to account for lunch breaks). What I want to do is create a chart that graphically shows how many people are in the building at any given time. So I want the times on the bottom and the person's name on the vertical axis, and then have a line that runs across the cells that the person is on the clock. Any ideas? Thanks. ...

Queries 05-14-07
Dear Sirs, I have two select queries. I would like to combine the data from this two, into one query (something like merging the data) - query 1 + query 2 = query 3 Can someone tell me how to do do that? Thanks Klaus It depends... We're not there. We can't see what you're doing. Post the SQL of the two queries as well as information about what the queries do and what you would like this 3rd query to output and we'll get back to you. Cheers, Jason Lepack On May 14, 8:41 am, Amateur <Amat...@discussions.microsoft.com> wrote: > Dear Sirs, > I have two select que...

Set Association
When I receive an e-mail with an attachment I get the message 'Set Association' in the control panel. I have searched and cannot find this. Is it maybe because I have Vista and the attachment is in Word? -- Kind regards Anna and Peter La Passiflore B&B 0033 475 931276 This newsgroup is for discussions about Microsoft Access, a relational database program. It sounds as if this question is related either to Vista or to your e-mail program. In any case another newsgroup would be a better source of information. By the way, when posting to any public newsgroup it is best to...