Query with total field for using with form and report (1 Viewer)

[LukeSky]

Hi, guys. I apologize for my english because is not my native language.

I made a query that gathers all the people who have won a prize, each award has a value, that is why each person can be repeated.

According to the value of the prize I need get the sum of that baremación to classify people according to their achievements.

Now I have a calculated for each prize multiplied by its scale field, and this field use it in a report that the group provides rating values me.

The problem is I can not sort the records in the report by a calculated field. My solution was to use an auxiliary crosstab query and get the result I want, but for unknown reasons it does not work as a criterion to add a form field.

Original query:

SELECT OBJETIVO.Núm_Documento, TIPRELINFOOBJE.Relación, Count(OBJETIVO.Núm_Documento) AS FELICITACIONES, TIPRELINFOOBJE.Baremo, [FELICITACIONES]*[BAREMO] AS PUNTOS, InfOperativo.FECHA_RECEPCION
FROM InfOperativo INNER JOIN ((RELINFOOBJE INNER JOIN TIPRELINFOOBJE ON RELINFOOBJE.ID_TIPRELINFOOBJE = TIPRELINFOOBJE.ID_TIPRELINFOOBJE) INNER JOIN OBJETIVO ON RELINFOOBJE.ID_OBJE = OBJETIVO.ID_OBJETIVO) ON InfOperativo.ID_INFOPERATIVO = RELINFOOBJE.ID_INFO
WHERE (((TIPRELINFOOBJE.Relación) Like "Feli*" Or (TIPRELINFOOBJE.Relación) Like "Condeco*" Or (TIPRELINFOOBJE.Relación) Like "Pro*Condeco*") AND ((OBJETIVO.DESTINADO)="SI"))
GROUP BY OBJETIVO.Núm_Documento, TIPRELINFOOBJE.Relación, TIPRELINFOOBJE.Baremo, InfOperativo.FECHA_RECEPCION
HAVING (((InfOperativo.FECHA_RECEPCION)>=[B]#7/1/2016#[/B]))
ORDER BY Count(OBJETIVO.Núm_Documento) DESC , TIPRELINFOOBJE.Baremo DESC;

Cross-referencies query:

TRANSFORM Count(TIPRELINFOOBJE.Baremo) AS CuentaDeBaremo
SELECT OBJETIVO.Núm_Documento, Sum(TIPRELINFOOBJE.Baremo) AS SumaDeBaremo
FROM OPERACION INNER JOIN ((InfOperativo INNER JOIN ((RELINFOOBJE INNER JOIN TIPRELINFOOBJE ON RELINFOOBJE.ID_TIPRELINFOOBJE = TIPRELINFOOBJE.ID_TIPRELINFOOBJE) INNER JOIN (RELOBJEOPER INNER JOIN OBJETIVO ON RELOBJEOPER.ID_OBJE = OBJETIVO.ID_OBJETIVO) ON RELINFOOBJE.ID_OBJE = OBJETIVO.ID_OBJETIVO) ON InfOperativo.ID_INFOPERATIVO = RELINFOOBJE.ID_INFO) INNER JOIN INFOhorario ON InfOperativo.ID_INFOPERATIVO = INFOhorario.ID_INFO) ON OPERACION.ID_OPER = RELOBJEOPER.ID_OPER
WHERE (((InfOperativo.FECHA_RECEPCION)>=#7/1/2016#) AND ((TIPRELINFOOBJE.Relación) Like "Feli*" Or (TIPRELINFOOBJE.Relación) Like "*Condeco*") AND ((OPERACION.ID_OPER)=32) AND ((OBJETIVO.DESTINADO)="SI"))
GROUP BY OBJETIVO.Núm_Documento
PIVOT TIPRELINFOOBJE.Relación;

I had try use DSum:

PUNTOS: DSuma([Baremo];"CC Condecoracion Nueva 2";"[ID_OBJE]=" & [ID_OBJETIVO] & " AND ([FECHA_RECEPCION] BETWEEN " & Forms![Personal CDCIA TOLEDOI]!Texto47 & " AND " & Forms![Personal CDCIA TOLEDOI]!Texto85 & ")")

But I dont get run it.

I had trim sentences of SQL because are enormous. Dates on bold should be data linked with fields of form for generating report.

I hope that you could help me. ^^'. Thanks.

EDIT: Report would have apparience like:

 

[Ranman256]

You can sort records on ANY field.

 

[LukeSky]

You can sort records on ANY field.

I can not order resulta using calcules field of report, for this cause I need sumatory on query for using like group head of report.

 

[LukeSky]

Solved, I put screenshot: