Query values based on the values of another record in a different table

[chrisjmcqueen]

Hey guys

I am pretty novice with access,

The data I have relates to the number of fish logged against the flow rate at that time.
I do not have the dates as they are irrelevant, they are all logged within one year.

I have another table with percentages split into 1,2,3,4,5 etc and the highest value flow rate and the lowest value flowrate allowed within that percentile.
fish records.accdb



What I need help is with totalling the number of fish in each percentile.


so far I have only been able to query the tables based on entering a percentage myself. I queried percentile value 40 and it came back with a number of fish totals where the flowrate sat between the Value A and Value B for 40 percent.


I hope I have explained myself well,
I appreciate any assistance in solving my problem

Thanks
C

[June7]

Would have been nice if you had included the query you built.

Do a GROUP BY (Totals) aggregate query. With query in Design View, click Totals from the design tab on ribbon. Access Help has guidelines.

The sql statement is:

SELECT DLookUp("Percentage","percentage",[FlowRate] & " Between RangeA And RangeB") AS Percentage, Count(fish.SalmonNumber) AS CountOfSalmonNumber
FROM fish
GROUP BY DLookUp("Percentage","percentage",[FlowRate] & " Between RangeA And RangeB");

[chrisjmcqueen]

GENIUS

Worked perfectly, thank you for taking the time to help me