Is it possible to query two access tables where you want to know if a value/range in the first table is between two fields in the second table?

Question

I'm trying to query two tables, ASSAYS, AND LITHO in a diamond drillhole database.

I was given values (SAMPLE_NO) to search for in the ASSAYS table, to return values such as HOLE-ID, FROM, and TO. So each sample that we take has a HOLE-ID, SAMPLE_NO, FROM AND TO. One hole-id can have multiple sample numbers, but each sample number is unique. The from and to will be unique in each hole-id. This I can find no problem.

My coworker also wanted to know what rock type was associated with each sample. This info is located in another table so I'll need to figure out how to query for this. The information that this table holds is HOLE-ID, FROM, TO, and ROCKTYPE.

Can you post the table structure for ASSAYS as well as LITHO? — shahkalpesh, Jan 21 '13 at 15:05

score 0 · Answer 1 · answered Jan 21 '13 at 15:03

You're looking for what is called a JOIN. This allows you to JOIN data of multiple tables based on matiching column values. This could be your starting point:

SELECT a.*, l.*
FROM ASSAYS a LEFT JOIN LITHO l ON a.hole-id = l.hole-id
WHERE a.sample_no = 'XXXX'

Please google for JOIN and SQL to find out about the exact syntax.

Is it possible to query two access tables where you want to know if a value/range in the first table is between two fields in the second table?

1 Answers1