Query to find the Count

[JithuAccess]

Hi
Going back to your totals query as described in post #4, change the Group By to Count (not Sum) as you want to count the records for each country.
Your 'stupid method', as you described it, is unnecessary

This is what I am getting

 

[isladogs]

That is the correct results for what you've done but not what you want.
Start again.

Add the Country field twice to a new totals (aggregate) query.
Both will say Group By. Change the second of these to Count,
Does that give what you want?

 

[JithuAccess]

Yes Sir,

It works. Thanks a lot

Thank You

 

[JithuAccess]

That is the correct results for what you've done but not what you want.
Start again.

Add the Country field twice to a new totals (aggregate) query.
Both will say Group By. Change the second of these to Count,
Does that give what you want?

But unfortunately I have to included 3,4 Parameter Values and I am getting a different results when I am including these parameter values. The last option is to adopt my "Stupid" method.

Any way thanks a lot for your kind Help and efforts

 

[isladogs]

What are the parameters you have to include? It may be possible to manage them.

 

[JithuAccess]

What are the parameters you have to include? It may be possible to manage them.

Thanks a lot for your reply.

My Live Table Actually have 12 Fields. I have to given the parameters like "Approval Dates" — Between Fiscal Years like 01-Apr-2019 and 31-Mar-2020, "Service Type" — [ABC], "Data Entry Date", "Payment Amount" – Between $20,000 and $200,000 and the most complex one "Travel Cost" — Total of Flight Cost + Taxi Cost + Reimbursement Cost - Paid by Client * Number of Days

Thanks

 

[isladogs]

Can you upload an example of what you are trying to do.

 

[JithuAccess]

Sure Sir,

This is the values in my Table:

Now I want to find the total count of countries and the parameters values are: Travel Cost greater than $10,000.00 and Approval date Between 2017-01-31 and 2020-03-02. The query Result should be:

USA: 3, Mexico 1 and Canada 0

My Query Design:

Query Result

 

[isladogs]

Remove the field SName and both instances of FName from your query.
Next change Group By to Where for the two fields Travel Cost and Approval Date
The query results will then look similar to those in post #23 but there will be no record for Canada as the value is zero.
If you need that Canada record to be displayed as well, an extra step is required

 

[JithuAccess]

Remove the field SName and both instances of FName from your query.
Next change Group By to Where for the two fields Travel Cost and Approval Date
The query results will then look similar to those in post #23 but there will be no record for Canada as the value is zero.
If you need that Canada record to be displayed as well, an extra step is required

Thanks a lot. It works

Result

Could you kindly let me know what would be that Extra step if I have to display Canada Record as well?

Thanks

 

[isladogs]

One way is as follows
1. Save your original query as e.g. Query1
2. Create a new query and add another copy of Table4 to the query design window as well as Query1. Use a left join from Table4 to the query joining on the Country field.
2. Now change the query to unique values in the property sheet to prevent duplicates
3. Add fields as shown below and run the query

Note the use of the Nz function

The new query SQL will be

SELECT DISTINCT Table4.Country, Nz([CountOfCountry],0) AS Total
FROM Table4 LEFT JOIN Query1 ON Table4.Country = Query1.Country;

 

[JithuAccess]

You Are Awesome

 

[JithuAccess]

Thanks a lot

 

[isladogs]

You're welcome. Hopefully you can see how it works