Graph with Top 5 and "Others" as Category (1 Viewer)

[evillarrealca]

Hi, I'm building a graph after a Query that groups the Categories and Sums the amount for each category, the problem is that there are more than 10 categories, so I want to just graph the Top 5 and a new category named "Other" for the rest of the Income amount, so I can show a Pie Graph with the Top 5 and "Other" to have the 100% of Income in one Graph. Does any body have done this?
Thanks in advance for the help!

 

[jdraw]

For clarity, your Pie chart will have "6 slices", correct?
Some thoughts to consider:
T1,t2,t3,t4,t5,other
Other = Total-top5
Percentages
(Tx/total)*100 for x = 1-5
Other = 100 -( T1% +T2%.....+T5%)

 

[evillarrealca]

Hi jdraw, that is correct, the pie will have 6 slices.
I got what you said but how do I write it into the query? or is it specified in the graph?

 

[jdraw]

I think you will need more than 1 query. I haven't done much with charts in quite a while.
I anticipate a Top5 query and another with all records not in Top5 as OTHER, then a union query as source for a chart, but it's been a while.
Can you post some sample data?

 

[evillarrealca]

Attached is png of the query result (IncomeTable). That query is the one I used to build the Chart (IncomeChart).

 

[evillarrealca]

Hi jdraw, thanks for the tip but I've been having trouble to write the NOT IN query correctly. If you can help with this, here is the SQL query for the original Income query which has ALL the results:

SELECT tblTransacciones.tipoTran, Sum(tblTransacciones.montoTran) AS SumOfmontoTran, tblCuentas.nombreCuenta, tblBeneficiarios.idTpoBenef_FK, tblCuentas.idTipoCuenta_FK
FROM tblBeneficiarios INNER JOIN (tblCuentas INNER JOIN tblTransacciones ON tblCuentas.idCuenta = tblTransacciones.cuentaTran_FK) ON tblBeneficiarios.idBeneficiario = tblTransacciones.benefTran_FK
WHERE (((tblTransacciones.fechaTran)>=Nz([Forms]![frmInicio].[Form]![NavigationSubform].[Form]![frmTableroIngresosInicio]![FechaDe],#1/1/2018#) And (tblTransacciones.fechaTran)<=Nz([Forms]![frmInicio].[Form]![NavigationSubform].[Form]![frmTableroIngresosInicio]![FechaA],#12/31/9999#)))
GROUP BY tblTransacciones.tipoTran, tblCuentas.nombreCuenta, tblBeneficiarios.idTpoBenef_FK, tblCuentas.idTipoCuenta_FK
HAVING (((tblTransacciones.tipoTran)="Ingreso") AND ((tblCuentas.idTipoCuenta_FK)<>8));

and this is the SQL query for the TOP 5 results:

SELECT TOP 5 tblTransacciones.tipoTran, Sum(tblTransacciones.montoTran) AS SumOfmontoTran, tblCuentas.nombreCuenta, tblBeneficiarios.idTpoBenef_FK, tblCuentas.idTipoCuenta_FK
FROM tblBeneficiarios INNER JOIN (tblCuentas INNER JOIN tblTransacciones ON tblCuentas.idCuenta = tblTransacciones.cuentaTran_FK) ON tblBeneficiarios.idBeneficiario = tblTransacciones.benefTran_FK
WHERE (((tblTransacciones.fechaTran)>=Nz([Forms]![frmInicio].[Form]![NavigationSubform].[Form]![frmTableroIngresosInicio]![FechaDe],#1/1/2018#) And (tblTransacciones.fechaTran)<=Nz([Forms]![frmInicio].[Form]![NavigationSubform].[Form]![frmTableroIngresosInicio]![FechaA],#12/31/9999#)))
GROUP BY tblTransacciones.tipoTran, tblCuentas.nombreCuenta, tblBeneficiarios.idTpoBenef_FK, tblCuentas.idTipoCuenta_FK
HAVING (((tblTransacciones.tipoTran)="Ingreso") AND ((tblCuentas.idTipoCuenta_FK)<>8))
ORDER BY Sum(tblTransacciones.montoTran) DESC;

Thanks for your support!

 

[jdraw]

We really need a copy of your database (zip format) to work with the queries.

See the Allen Browne
TOP n records per group
at this link http://allenbrowne.com/subquery-01.html

 

[evillarrealca]

Thank you.
Here is the file.

 

[jdraw]

Using your data and the queries you supplied.

I copied the queries you sent in post as QJ_AllRecords and QJ_Top5.
-Then converted these to Make Table queries (for ease of use) as TJ_AllRecords and TJ_Top5.
-Then used these tables in a query QJ_OtherDetail

SELECT TJ_AllRecords.tipoTran
, TJ_AllRecords.SumOfmontoTran
, TJ_AllRecords.nombreCuenta
, TJ_AllRecords.idTipoCuenta_FK
, TJ_Top5.nombreCuenta
FROM TJ_AllRecords LEFT JOIN TJ_Top5 ON 
TJ_AllRecords.nombreCuenta = TJ_Top5.nombreCuenta
WHERE (((TJ_Top5.nombreCuenta) Is Null))
ORDER BY TJ_AllRecords.SumOfmontoTran DESC;

to get the "Other records"
-Then this query

SELECT "Other" AS TmpName
, Sum(QJ_OtherDetail.SumOfmontoTran) AS SumOfSumOfmontoTran
FROM QJ_OtherDetail;

to get a name for this category "Other" and a Total amount

Hope this helps. I have an appointment and must go out for rest of day.

 

[evillarrealca]

Thanks jdraw, I'll see if I can put this together.
I really appreciate your time!
Have a great day!

 

[jdraw]

Any status update on this?

 

[evillarrealca]

I tried to put the Querys together but I ended needing 5 querys, I guess it wasn't the way it has to be.
I was also looking a way to import the data from excel.
I appreciate if you can detail a bit more how to do this.

 

[poliadisa]

Hi jdraw, that is correct, the pie will have 6 slices.
I got what you said but how Tutuapp 9Apps ShowBox do I write it into the query? or is it specified in the graph?

for the rest of the Income amount, so I can show a Pie Graph with the Top 5 and "Other" to have the 100% of Income in one Graph. Does any body have done this?

 

[evillarrealca]

Hi jdraw, I just use the routine you sent me, using Is NULL as a Criteria in the query to only select the results I needed (I used this instead of NOT EXISTS) and it worked perfectly. Thank you very much for that input!!

 

[airboneone]

how do I write it into the query?

 

[mariashare]

I think you will need more than 1 query, I haven't done much with charts in quite a while.