Calculate value based on value in query and another table

[accesscat]

I'm trying to work out how to calculate a field in a query - I want to calculate detection rate (i.e number of detections at one site as calculated in the query divided by the number of nights that site was active). The number of nights the site was active is stored in a table. The query calculates number of detections per month, e.g.
Site A June21 43
Site B June21 21
Site A July21 32
Site B July21 15


In my table of trap nights I have three fields - Site, MonthYear, TrapNights i.e.
Site A June21 30
Site B June21 25
Site A July31 31
Site B July31 10

I want an expression in my query that calculates the detection rate for each site for each month i.e.
Site A June21 43/30=1.43
Site B June21 21/25=0.84
Site A July21 32/31=1.03
Site B July21 15/10=1.5

How do I create an expression to calculate this? Thanks in advance.

[June7]

Build a query that includes both tables with compound join on the site and date fields.

[accesscat]

Thanks, can you provide an overview on how to do this please?

[Cottonshirt]

I want to calculate detection rate (i.e number of detections at one site as calculated in the query divided by the number of nights that site was active). The number of nights the site was active is stored in a table.

if I understand you, you want to calculate the average number of detections by dividing a number in a query by a number in a table.

to do that, open a query design window. in the Show Table dialogue select the query holding the number of detections, and the table holding the number of nights the site was active.

join these two on the site name or number.

then your calculation is [detections]/[nights active]

be sure to use the correct division symbol for the type of answer you require.

dividing like this \ gets you an integer
dividing like this / gets you a decimal number


good luck with your project


Cottonshirt

[accesscat]

Thank you so much, that worked!

[June7]

Doesn't the join have to be compound - site and date fields?