Return sum of values between ranges in a second table

[amateur]

I have a database with tables of data pertaining to drill holes. The data are separated into different tables grouped by the type of data collected and generally these data are linked by the hole number and depth.

In one table, each row represents data collected from sequential 3 metre intervals;
hole_id, from, to, rock_type


hole1, 3, 6, granite
hole1, 6, 9, shale
Hole1, 9, 12, shale

In a second table, each row represents data collected from sequential 1 metre intervals;
Hole_ID, From, to, Number_of_fractures
hole1, 3, 4, 3
hole1, 4, 5, 1
hole1, 5, 6, 2

I need to create a query which will find the total number of fractures in each interval in table 1. For the example above, in table 1 the interval is 3-6 metres and the corresponding number of fractures from table 2 would be 6.
All I have figured out so far is to create a query which extracts the hole_id, from, to from the 3 m interval table. I cannot figure out how to get access to sum the values from table 2 that fall within these depth ranges.

Thanks in advance,
Ama

[June7]

Maybe use domain aggregate:

SELECT *, DSum("Number_of_fractures", "table2", "Hole_ID='" & [hole_id] & "' AND [From]>=" & [From] & " AND [To]<=" & [To]) AS SumFractures FROM table1;

Suggest you could use better fieldnames than FROM and TO. These words have special meaning in VBA and SQL.

[amateur]

Thank you for your reply
I attempted to use this function with no success. I am sure I am the problem here, this is well outside my level of expertise (none). Maybe I can be a bit more specific so, if you were inclined, you could give the actual copy-and-paste format. This is a nightmare for me.
Here's my table layout with from and to modified for the reasons you indicated above.

Fractures table:

HOLE_ID	FROM_m	TO_m	FRACTURES	COMMENTS
Hole55	17	18	5
Hole55	18	19	5
Hole55	19	20	9

Other table:

HOLE_ID	FROM_m	TO_m	D LENGTH	M LENGTH	DIFFERENCE	%RECOVERY	BREAKS	COMMENTS
Hole55	17	20	3	2.91	0.09	97	0

[June7]

What does 'no success' mean - error message, wrong results, nothing happens? Could post the SQL statement of attempted query.

I should have also mentioned use of spaces and special characters/punctuation (underscore is exception) should be avoided in naming convention.

SELECT Fractures.*, DSum("DLength","Other","Hole_ID='" & [hole_id] & "' AND [From_m]>=" & [From_m] & " AND [To_m]<=" & [To_m]) AS SumFractures FROM Fractures;

[amateur]

Thank you again! but unfortunately, the desired outcome was not reached. Here's the (partial) result.

HOLE_ID	FROM_m	TO_m	FRACTURES	COMMENTS	SumFractures
WAT08-014	26	27	9		9
WAT08-014	27	28	8		8
WAT08-014	28	29	6		6
WAT08-014	29	30	8		8

The intervals are still in 1 m lengths and the sumfractures is equal to the fracture count for each record. I initiate this process by using the query design then adding the Recovery and Fracture tables, setting up a join between Hole_ID in each so that all records from Recovery are included and those that match from Fractures. I then went to SQL view and pasted in your code. After executing the query, the Recovery table is gone. Not sure if that is any help. I look at rocks for a living, perhaps that will give you an indication of my code savviness.

FYI The length d column is just the length of the interval for each record in Recovery table.

I hear what you are saying about naming conventions - I inherited this database and the reason behind what I am doing is to extract the contents of this database and put it in a different, master database my employer uses. This data is from a company that my employer purchased so many conventions/metrics/etc are different. It has been a challenge.

[June7]

What field to you want to sum or do you just want a count of records that meet criteria?

You did not provide enough data in post 3 to reconcile with the output. The data is completely different.

Show example raw data and show the desired output from that data.

[amateur]

HOLE_ID	FROM_m	TO_m	D LENGTH	M LENGTH	DIFFERENCE	%RECOVERY	BREAKS	COMMENTS
WAT10-063A	11	14	3	2.94	0.06	98	0
WAT10-063A	14	17	3	3.02	-0.02	100.67	0
WAT10-063A	17	20	3	2.94	0.06	98	0
WAT10-063A	20	23	3	2.95	0.05	98.33	0
WAT10-063A	23	26	3	3.04	-0.04	101.33	1	24
WAT10-063A	26	29	3	3.06	-0.06	102	0
WAT10-063A	29	32	3	2.9	0.1	96.67	0
WAT10-063A	32	35	3	3	0	100	0
WAT10-063A	35	38	3	2.98	0.02	99.33	1	35.1
WAT10-063A	38	41	3	2.92	0.08	97.33	0
WAT10-063A	41	44	3	2.95	0.05	98.33	0
WAT10-063A	44	47	3	3.05	-0.05	101.67	0
WAT10-063A	47	50	3	2.94	0.06	98	2	"49

HOLE_ID	FROM_m	TO_m	FRACTURES	COMMENTS
WAT10-063A	11	12	2
WAT10-063A	12	13	2
WAT10-063A	13	14	4
WAT10-063A	14	15	4
WAT10-063A	15	16	4
WAT10-063A	16	17	4
WAT10-063A	17	18	3
WAT10-063A	18	19	3
WAT10-063A	19	20	3
WAT10-063A	20	21	3
WAT10-063A	21	22	3
WAT10-063A	22	23	3
WAT10-063A	23	24	5
WAT10-063A	24	25	3
WAT10-063A	25	26	3
WAT10-063A	26	27	3
WAT10-063A	27	28	3
WAT10-063A	28	29	3
WAT10-063A	29	30	3
WAT10-063A	30	31	5
WAT10-063A	31	32	2
WAT10-063A	32	33	2
WAT10-063A	33	34	2
WAT10-063A	34	35	2
WAT10-063A	35	36	2
WAT10-063A	36	37	4
WAT10-063A	37	38	4
WAT10-063A	38	39	4
WAT10-063A	39	40	4
WAT10-063A	40	41	4
WAT10-063A	41	42	4
WAT10-063A	42	43	2
WAT10-063A	43	44	2
WAT10-063A	44	45	0
WAT10-063A	45	46	0
WAT10-063A	46	47	0
WAT10-063A	47	48	0
WAT10-063A	48	49	0
WAT10-063A	49	50	0

Desired:

HOLE_ID	FROM_m	TO_m	D LENGTH	M LENGTH	DIFFERENCE	%RECOVERY	BREAKS	COMMENTS	Fractures
WAT10-063A	11	14	3	2.94	0.06	98	0		8
WAT10-063A	14	17	3	3.02	-0.02	100.67	0		12
WAT10-063A	17	20	3	2.94	0.06	98	0		9
WAT10-063A	20	23	3	2.95	0.05	98.33	0		9
WAT10-063A	23	26	3	3.04	-0.04	101.33	1	24	16
WAT10-063A	26	29	3	3.06	-0.06	102	0		9
WAT10-063A	29	32	3	2.9	0.1	96.67	0		10
WAT10-063A	32	35	3	3	0	100	0		6
WAT10-063A	35	38	3	2.98	0.02	99.33	1	35.1	10
WAT10-063A	38	41	3	2.92	0.08	97.33	0		12
WAT10-063A	41	44	3	2.95	0.05	98.33	0		10
WAT10-063A	44	47	3	3.05	-0.05	101.67	0		0
WAT10-063A	47	50	3	2.94	0.06	98	2	"49	0

[June7]

Okay, think I just had the tables backwards.

SELECT Other.*, DSum("Fractures","Fractures","Hole_ID='" & [hole_id] & "' AND [From_m]>=" & [From_m] & " AND [To_m]<=" & [To_m]) AS SumFractures FROM Other;