Query for Total count and percentage

[aparnawangu]

Hi Team,

I want to generate a report for enquiries for a daycare centre.
I want to capture total enquiries count and percentage per centre.

Centres are like W1,W2,W3.......W10.
I want separate count for all the centres.
Pls suggest how to do this.

Thanks in advance
Aparna

 

[Pezzini]

As a rough guide, I'd try it using two queries:

No 1 would be something like Qry-TotalCount do a simple total quote for all of the centres

No 2 would be something like Qry-CentreCount, this would be a count for each of the centres individually, then link this to Qry-TotalCount and use a expression to calculate the percentage, e.g. Perc: [Count per centre]/[TotalCount]

Suspect that might not be the clearest explanation you'll get, but could point you in the right direction.

 

[aparnawangu]

Sorry but i am very new to access.Can u pls explain in a simpler manner.

Thanks
Aparna

 

[arnelgp]

SELECT tblcenter.centers, Sum(tblcenter.inq) AS SumOfinq, (select sum(inq) from tblcenter) as TotalInq, FormatPercent(SumOfInq/TotalInq) as Pct
FROM tblcenter
GROUP BY tblcenter.centers;

replace the fieldname with the fieldname from your table.

 

[Pezzini]

Here's a quick mock up as a slower version to what arnelgp, but if you're that new to access it might help you see what's going on:

Qry-TotalCount
SELECT Sum([tbl-centres].Enquiry) AS SumOfEnquiry
FROM [tbl-centres];

Qry-CenterCount
SELECT [tbl-centres].CentreID, Sum([tbl-centres].Enquiry) AS SumOfEnquiry, Sum([tbl-centres].Enquiry/[Total]) AS Perc
FROM [tbl-centres], [Qry-TotalCount]
GROUP BY [tbl-centres].CentreID;

 

[aparnawangu]

I tried but not happening:banghead:
Pls pls help me step by step
I will give u the column names of my table
Table name is Enquiry_Form
Columns are Enquiry_Form_No,Enquiry_Date,Centre_Name.
Centre_Name is a lookup table connected to Centre table.

My query is
I need total enquiries (weekly enquiry generation report for all the centres like W1,W2....W10).
Total Enquiry nos separately for all centres and percentage also per centre.

Pls help.

Thanks
Aparna

 

[Pezzini]

Since you are so new, I’d suggest that you do this in four steps, I’ve slowed this right down in hope that it helps you learn (a) how to write enquiries and (b) think in the smaller steps that are sometimes necessary before leaping into the more complicated single step versions that many of the experts here can teach you.

First to need to know how many enquiries each centre had in the time period you are interested in, so each time you run this query you will need to ensure that you specify the dates you are interested in (in red below – note that this gave US format even though in design view I typed UK format it will work with whatever version you have setup on your computer). This change can be made by hand or by use of variable, for the time being, stick with doing it by hand until you grow more familiar with Access and what it can do. First query is:

Qry_DailyTotalInWeek
SELECT Enquiry_Form.Centre_Name, Enquiry_Form.Enquiry_Date, Count(Enquiry_Form.Enquiry_Form_no) AS CountOfEnquiry_Form_no
FROM Enquiry_Form
GROUP BY Enquiry_Form.Centre_Name, Enquiry_Form.Enquiry_Date
HAVING (((Enquiry_Form.Enquiry_Date) Between #12/1/2015# And #12/7/2015#));


Next you’ll need to know how many enquiries were received in total per centre, note that here you are using the first query not the table to get the numbers you want.
Qry-WeeklyTotal
SELECT [Qry-DailyTotalInWeek].Centre_Name, Sum([Qry-DailyTotalInWeek].CountOfEnquiry_Form_no) AS Enquiries, [Qry-TotalInWeek].Total
FROM [Qry-DailyTotalInWeek], [Qry-TotalInWeek]
GROUP BY [Qry-DailyTotalInWeek].Centre_Name, [Qry-TotalInWeek].Total;

Next you want to know how many enquiries you had in total so you can work out the total in the week, again working off the query not the table.
Qry-TotalInWeek
SELECT Sum([Qry-DailyTotalInWeek].CountOfEnquiry_Form_no) AS Total
FROM [Qry-DailyTotalInWeek];


Finally you want to see the report by centre name, with the weekly total and the percentage calculation, which the following gives you:
Qry-WeeklyEnqByCentre
SELECT [Qry-WeeklyTotal].Centre_Name, [Qry-WeeklyTotal].Enquiries, [Enquiries]/[total] AS Perc
FROM [Qry-WeeklyTotal];


As an aside, please note that these are all very basic queries, if you go into CREATE and use QUERY DESIGN, you can add tables and do some playing around with queries, test out what does what. The button you might find particularly useful is the the Sigma (Totals) button. Play with what that can do, see for yourself how a small change will make a difference. Also in this mode (you can turn all of the above SQL view to Design view very easily) use the property sheet to change formats, this is important in Qry-WeeklyEnqByCentre as the “Perc” column needs to be set up to show as a percentage rather than then decimal it calculates. If you have trouble with this, suggest you search for Access tutorials to work through, there are plenty out there, that will help you get to grips with the various inbuilt features of query creation and how to format output.

Hope this helps.

 

[aparnawangu]

Hi Team,

I am ok with the queriws but can you pls let me know how to group first query result in second one.

Thanks
Aparna

 

[Pezzini]

Not sure what this question is aiming at, but here's two possible answers.

If you are refering to the queries above, you don't need to do any more grouping that has already been done.

If this is a more general query, then you create your query, I'd suggest using the graphical interface for this rather than the SQL since you're not overly comfortable with querying yet, and then you group by using the in-built functionality - i.e. you click on the sigma button in the ribbon that appears when you start creating queries. Then watch for the 'Group by' line that appears, remember if everything is 'group by' it achieves nothing.

Does this help?

 

[aparnawangu]

I am not clear with the highlighted part what to write there.Pls guide me regarding that.I got the first query result but for next query how to use the result query there i am getting stuck.
SELECT [Qry-DailyTotalInWeek].Centre_Name, Sum([Qry-DailyTotalInWeek].CountOfEnquiry_Form_No) AS Enquiries, [Qry-TotalInWeek].Total
FROM [Qry-DailyTotalInWeek], [Qry-TotalInWeek]
GROUP BY [Qry-DailyTotalInWeek].Centre_Name, [Qry-TotalInWeek].Total;

Pls let me know what to write there

Thanks
Aparna

 

[Pezzini]

The parts you have highlighted are the names of the queries you are raising queries on to get the necessary totals. I've put in the names of the queries as given above, if you've used different names then you put the name of your queries in there instead.

so if you called the queries Query1 and Query2, then second would read:
SELECT [Query1].Centre_Name, Sum([Query1].CountOfEnquiry_Form_No) AS Enquiries, [Query2].Total
FROM [Query1], [Qry-TotalInWeek]
GROUP BY [Query1].Centre_Name, [Query2].Total;

Does that help?