Simple query to calculate totals from 2 different tables (1 Viewer)

Queries really are my weak point, however in my attempt to learn and get better I am having a play around with them.

I have two tables:

The first one is TblAcrft which is where I store the list of aircraft that I have - denoted by Reg. Each aircraft / Reg has a Model assigned to it, this is either A330 / CRJTK / EM90.

The second one is TblRecords where the main bulk of my data will be saved. This has ID and Type (although in the future it will also include other columns, but none of them will be involved in this current query issue). Type is equal to Model, but I named them differently in the example to make it easier to pick apart. Type is a user input.

What I would like to do is to be able to calculate a distinct count of how many times Model appears in TblAcrft and also a distinct count of how many times Type appears in TblRecords.

The attached image shows how the query should look, but I am struggling to get it to work as it appears to be multiplying the values as opposed to keeping them separate from each other.

You need to use a sub query;

Thanks Minty.

Apologies for effectively asking this question in two parts, but I am trying to ask it in parts so that I can learn in baby-steps.

If I were to add an extra field in my TblAcrft to have "Active" as a true/false check box, how would I go about "filtering" my query results so that I would only see the results for the active aircraft (where active = true).

I have managed to tweak the query to filter the CountOfModel column, but I am struggling to work out how to get the filtering on the typecount column.

I get that it will have something to do with amending the subquery, but can't seem to understand where exactly it needs to go.

It depends... What is worth remembering is that the criteria don't need to be in the output of a query, so this is the simple route;

So filter it in the sub query.

I tried copying that code into my query and it didn't appear to filter the data in the column typecount.

If I have done the maths correctly, typecount should be 106 for A330, 30 for CRJTK and 68 EM90 when filtered based on the active aircraft.

Your active field is really in the wrong table ?
Because of the way they are related it won't filter the way you want.

So does that mean there is no way to get this calculation to work in this way? Or do I just have to use a different method?

Not sure how to proceed on filtering out all records from non-active aircraft.

Your tables are a little confusing - they seem to have the pretty much the same data in them?

You can't filter them with the current relationship because they are only joined on the aircraft type, which is repeated multiple times.

I appreciate my tables are pretty confusing. I am trying to think if there is a ready-made data set that I can use to assist with solving this issue.

Does the attached dataset help a bit more?

If not is it possible to make an example using other fields / date that you know works and I could try picking it apart to make it relevant to mine?

Let me know if there is any way I can help you to help me.

Still working on this, but with no success.