Using DCount Function on a seperate Form

[keith01]

Good Evening,

I have been struggling with this, and after reviewing the Microsoft Access site, and various forums, do not seem to be able to come up with an answer

What I would like to do is create a continuous form, with one field from a query ("Aircraft Original Type:") and display this field name along with a count of the number of matching records.

The field does not contain a unique value, so there may be hundreds of different types, so i cannot see how the dcount function would work, as it relies on selecting a unique value and counting them, so what i am trying to do in essence is create a form such as this :-

Cessna 152 100
Cessna 172 20000
Cessna 210 520

So The "Cessna" part is in the Field "Aircraft Original Type", and what i am looking for is for access to make a count of each Cessna 152, then 172 etc etc

What would be the best way of going about this ?



Best Regards

keith

[Minty]

Create a separate query , lets call it qryAircraftCount

(Without knowing your actual table and field names) The SQL will be something like;

SELECT AircraftType, Count(AircraftType_ID) as Qty
FROM YourAircraftTable
Group By AircraftType

This assumes you have an ID field.

Now Join this query to your main table on the Aircraft type and you should get what you are after.
If it doesn't work show us the SQL of the query you made, and someone will try and fix it.

[Welshgasman]

So whare is the 152 , 172 located?
You just use that in the grouping.

[keith01]

Hi Minty & Welshgasman

Here is the SQL from my Query "Tbl_Airhistory Query" :-

SELECT Tbl_Airhistory.[Registration / Serial:], Tbl_Airhistory.[Aircraft Original Type:], Tbl_Airhistory.[Aircraft Generic Type:], Tbl_Airhistory.[Aircraft Version:], Tbl_Airhistory.[C/n (msn):], Tbl_Airhistory.[City / Airport:], Tbl_Airhistory.[Region / Country:], Tbl_Airhistory.[Event:], Tbl_Airhistory.[Photo Date:], Tbl_Airhistory.[Photo by:], Tbl_Airhistory.[Photo ID:], Tbl_Airhistory.[Operator Titles:], Tbl_Airhistory.Remarks, Tbl_Airhistory.Image_Path, Tbl_Airhistory.Category, Tbl_Airhistory.Country_Rgd, Tbl_Airhistory.Regn1, Tbl_Airhistory.Regn2, Tbl_Airhistory.Regn3
FROM Tbl_Airhistory;

The Cessna 152, Cessna 172 that i quoted above is sample data from the field [Aircraft Original Type:]

Regards

Keith

[mike60smart]

Hi Keith
Are you able to upload a zipped copy of the database?

[keith01]

Hi Mike60smart

Please see attached

Regards

Keith

My Air History.zip

[Welshgasman]

What part of Minty's sql did you not understand?

SELECT Tbl_Airhistory.[Aircraft Original Type:], Count(Tbl_Airhistory.[Aircraft Generic Type:]) AS [CountOfAircraft Generic Type:]
FROM Tbl_Airhistory
GROUP BY Tbl_Airhistory.[Aircraft Original Type:]
ORDER BY Tbl_Airhistory.[Aircraft Original Type:];

[keith01]

Hi Welshgasman

I did not understand where to place the actual SQL code, so if i create a new query as suggested, where do i place the above code ?

Regards

Keith

[Welshgasman]

That sql is the query the will be the recordsource for your form

[mike60smart]

Keith
You create a new Query in SQL View and Paste Gasman's Code.

You then create a new query and add the tbl Air_History and the query created by Gasman's Code.
Then do as Minty said and link the two tables by Aircraft Type

[keith01]

Hi Welshgasman

Now that you have explained that, I have got it to work !
I did not realise that you could place SQL inside of a query, so I have learned something new today
Many Thanks Everybody for all of your help

With Best Regards

Keith

[keith01]

Many Thanks as for your help Mike60smart & Minty !

Best Regards

[Welshgasman]

A query is just that, SQL