Report made of a query cross table
in [Reports]
|
From: Pedro on 2 Jun 2010 07:47 Hi everyone It must be very simple for you guys but i can't find it: How to make a report, made of a query cross table, but only show records that the total of all months are diferent than zero (0). Negative and positive values. That report sums the sales for each person, each months and i want to know who sell and who's not selling. Tks in advance Pedro
From: John Spencer on 2 Jun 2010 08:48 Post the SQL of your cross tab query. It is possible that all you have to do is add a having clause to it. Generically that might look something like: TRANSFORM Sum(SomeValue) as MonthlySum SELECT Person FROM SomeTable GROUP BY Person HAVING SUM(SomeValue) <> 0 PIVOT SomeMonthField John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County Pedro wrote: > Hi everyone > It must be very simple for you guys but i can't find it: How to make a > report, made of a query cross table, but only show records that the total of > all months are diferent than zero (0). Negative and positive values. > That report sums the sales for each person, each months and i want to know > who sell and who's not selling. > Tks in advance > Pedro
From: Pedro on 4 Jun 2010 07:54 Hi John and many thanks for your answer, although i didn't understand much what i have to do... Here goes my SQL and i hope it helps and your answer can help me. TRANSFORM Sum([Qry Base].CUSTO) AS SomaDeCUSTO SELECT [Qry Base].TELEFONE, [Qry Base].CONTA, [Qry Base].[Dt Adesão], [Qry Base].Pacote, [Qry Base].Status, [Qry Base].Protocolo, [Qry Base].Rede, [Qry Base].[N Emp/Ag], [Qry Base].Nome, [Qry Base].[Ext VPN], [Qry Base].NIF, [Qry Base].[Nº conta], [Qry Base].[Dt envio Processo], [Qry Base].[SIM], [Qry Base].[Dt Conclusão], [Qry Base].Observações, [Qry Base].Utilizador, [Qry Base].DETALHE, Sum([Qry Base].CUSTO) AS [Total de CUSTO] FROM [Qry Base] GROUP BY [Qry Base].TELEFONE, [Qry Base].CONTA, [Qry Base].[Dt Adesão], [Qry Base].Pacote, [Qry Base].Status, [Qry Base].Protocolo, [Qry Base].Rede, [Qry Base].[N Emp/Ag], [Qry Base].Nome, [Qry Base].[Ext VPN], [Qry Base].NIF, [Qry Base].[Nº conta], [Qry Base].[Dt envio Processo], [Qry Base].[SIM], [Qry Base].[Dt Conclusão], [Qry Base].Observações, [Qry Base].Utilizador, [Qry Base].DETALHE PIVOT Format([Periodo],"mmm") In ("Jan","Fev","Mar","Abr","Mai","Jun","Jul","Ago","Set","Out","Nov","Dez"); Tks again Pedro "John Spencer" wrote: > Post the SQL of your cross tab query. > > It is possible that all you have to do is add a having clause to it. > Generically that might look something like: > > TRANSFORM Sum(SomeValue) as MonthlySum > SELECT Person > FROM SomeTable > GROUP BY Person > HAVING SUM(SomeValue) <> 0 > PIVOT SomeMonthField > > John Spencer > Access MVP 2002-2005, 2007-2010 > The Hilltop Institute > University of Maryland Baltimore County > > Pedro wrote: > > Hi everyone > > It must be very simple for you guys but i can't find it: How to make a > > report, made of a query cross table, but only show records that the total of > > all months are diferent than zero (0). Negative and positive values. > > That report sums the sales for each person, each months and i want to know > > who sell and who's not selling. > > Tks in advance > > Pedro > . >
From: John Spencer on 6 Jun 2010 16:16 TRANSFORM Sum([Qry Base].CUSTO) AS SomaDeCUSTO SELECT [Qry Base].TELEFONE, [Qry Base].CONTA, [Qry Base].[Dt Adesão] , [Qry Base].Pacote, [Qry Base].Status, [Qry Base].Protocolo, [Qry Base].Rede , [Qry Base].[N Emp/Ag], [Qry Base].Nome, [Qry Base].[Ext VPN], [Qry Base].NIF , [Qry Base].[Nº conta], [Qry Base].[Dt envio Processo], [Qry Base].[SIM] , [Qry Base].[Dt Conclusão], [Qry Base].Observações, [Qry Base].Utilizador , [Qry Base].DETALHE, Sum([Qry Base].CUSTO) AS [Total de CUSTO] FROM [Qry Base] GROUP BY [Qry Base].TELEFONE, [Qry Base].CONTA, [Qry Base].[Dt Adesão] , [Qry Base].Pacote, [Qry Base].Status , [Qry Base].Protocolo, [Qry Base].Rede , [Qry Base].[N Emp/Ag], [Qry Base].Nome, [Qry Base].[Ext VPN] , [Qry Base].NIF, [Qry Base].[Nº conta], [Qry Base].[Dt envio Processo] , [Qry Base].[SIM], [Qry Base].[Dt Conclusão], [Qry Base].Observações , [Qry Base].Utilizador, [Qry Base].DETALHE HAVING Sum([Qry Base].CUSTO) <> 0 PIVOT Format([Periodo],"mmm") In ("Jan","Fev","Mar","Abr","Mai","Jun","Jul","Ago","Set","Out","Nov","Dez"); If you have some values show up that should be zero try changing that additional line to account for small arithmetic errors that can happen when summing numbers with fractional portions. HAVING Sum([Qry Base].CUSTO) > 0.00000001 and Sum([Qry Base].CUSTO) < -0.00000001 John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County Pedro wrote: > Hi John and many thanks for your answer, although i didn't understand much > what i have to do... > Here goes my SQL and i hope it helps and your answer can help me. > > TRANSFORM Sum([Qry Base].CUSTO) AS SomaDeCUSTO > SELECT [Qry Base].TELEFONE, [Qry Base].CONTA, [Qry Base].[Dt Adesão], [Qry > Base].Pacote, [Qry Base].Status, [Qry Base].Protocolo, [Qry Base].Rede, [Qry > Base].[N Emp/Ag], [Qry Base].Nome, [Qry Base].[Ext VPN], [Qry Base].NIF, [Qry > Base].[Nº conta], [Qry Base].[Dt envio Processo], [Qry Base].[SIM], [Qry > Base].[Dt Conclusão], [Qry Base].Observações, [Qry Base].Utilizador, [Qry > Base].DETALHE, Sum([Qry Base].CUSTO) AS [Total de CUSTO] > FROM [Qry Base] > GROUP BY [Qry Base].TELEFONE, [Qry Base].CONTA, [Qry Base].[Dt Adesão], [Qry > Base].Pacote, [Qry Base].Status, [Qry Base].Protocolo, [Qry Base].Rede, [Qry > Base].[N Emp/Ag], [Qry Base].Nome, [Qry Base].[Ext VPN], [Qry Base].NIF, [Qry > Base].[Nº conta], [Qry Base].[Dt envio Processo], [Qry Base].[SIM], [Qry > Base].[Dt Conclusão], [Qry Base].Observações, [Qry Base].Utilizador, [Qry > Base].DETALHE > PIVOT Format([Periodo],"mmm") In > ("Jan","Fev","Mar","Abr","Mai","Jun","Jul","Ago","Set","Out","Nov","Dez"); > > Tks again > Pedro > > > "John Spencer" wrote: > >> Post the SQL of your cross tab query. >> >> It is possible that all you have to do is add a having clause to it. >> Generically that might look something like: >> >> TRANSFORM Sum(SomeValue) as MonthlySum >> SELECT Person >> FROM SomeTable >> GROUP BY Person >> HAVING SUM(SomeValue) <> 0 >> PIVOT SomeMonthField >> >> John Spencer >> Access MVP 2002-2005, 2007-2010 >> The Hilltop Institute >> University of Maryland Baltimore County >> >> Pedro wrote: >>> Hi everyone >>> It must be very simple for you guys but i can't find it: How to make a >>> report, made of a query cross table, but only show records that the total of >>> all months are diferent than zero (0). Negative and positive values. >>> That report sums the sales for each person, each months and i want to know >>> who sell and who's not selling. >>> Tks in advance >>> Pedro >> . >>
From: Pedro on 7 Jun 2010 07:57
Hi John and again, many thanks for your answer. I wouldn't like to change my qry cose it'll change all other reports based on the same query. I would like, if possible, something like, in the filter properties of the report, ([Qry Base].Total de CUSTO])<>0, but that doesn't work. Can you help? Again? Tks a lot Pedro "John Spencer" wrote: > TRANSFORM Sum([Qry Base].CUSTO) AS SomaDeCUSTO > SELECT [Qry Base].TELEFONE, [Qry Base].CONTA, [Qry Base].[Dt Adesão] > , [Qry Base].Pacote, [Qry Base].Status, [Qry Base].Protocolo, [Qry Base].Rede > , [Qry Base].[N Emp/Ag], [Qry Base].Nome, [Qry Base].[Ext VPN], [Qry Base].NIF > , [Qry Base].[Nº conta], [Qry Base].[Dt envio Processo], [Qry Base].[SIM] > , [Qry Base].[Dt Conclusão], [Qry Base].Observações, [Qry Base].Utilizador > , [Qry Base].DETALHE, Sum([Qry Base].CUSTO) AS [Total de CUSTO] > FROM [Qry Base] > GROUP BY [Qry Base].TELEFONE, [Qry Base].CONTA, [Qry Base].[Dt Adesão] > , [Qry Base].Pacote, [Qry Base].Status > , [Qry Base].Protocolo, [Qry Base].Rede > , [Qry Base].[N Emp/Ag], [Qry Base].Nome, [Qry Base].[Ext VPN] > , [Qry Base].NIF, [Qry Base].[Nº conta], [Qry Base].[Dt envio Processo] > , [Qry Base].[SIM], [Qry Base].[Dt Conclusão], [Qry Base].Observações > , [Qry Base].Utilizador, [Qry Base].DETALHE > > HAVING Sum([Qry Base].CUSTO) <> 0 > > PIVOT Format([Periodo],"mmm") In > ("Jan","Fev","Mar","Abr","Mai","Jun","Jul","Ago","Set","Out","Nov","Dez"); > > If you have some values show up that should be zero try changing that > additional line to account for small arithmetic errors that can happen when > summing numbers with fractional portions. > > HAVING Sum([Qry Base].CUSTO) > 0.00000001 > and Sum([Qry Base].CUSTO) < -0.00000001 > > John Spencer > Access MVP 2002-2005, 2007-2010 > The Hilltop Institute > University of Maryland Baltimore County > > Pedro wrote: > > Hi John and many thanks for your answer, although i didn't understand much > > what i have to do... > > Here goes my SQL and i hope it helps and your answer can help me. > > > > TRANSFORM Sum([Qry Base].CUSTO) AS SomaDeCUSTO > > SELECT [Qry Base].TELEFONE, [Qry Base].CONTA, [Qry Base].[Dt Adesão], [Qry > > Base].Pacote, [Qry Base].Status, [Qry Base].Protocolo, [Qry Base].Rede, [Qry > > Base].[N Emp/Ag], [Qry Base].Nome, [Qry Base].[Ext VPN], [Qry Base].NIF, [Qry > > Base].[Nº conta], [Qry Base].[Dt envio Processo], [Qry Base].[SIM], [Qry > > Base].[Dt Conclusão], [Qry Base].Observações, [Qry Base].Utilizador, [Qry > > Base].DETALHE, Sum([Qry Base].CUSTO) AS [Total de CUSTO] > > FROM [Qry Base] > > GROUP BY [Qry Base].TELEFONE, [Qry Base].CONTA, [Qry Base].[Dt Adesão], [Qry > > Base].Pacote, [Qry Base].Status, [Qry Base].Protocolo, [Qry Base].Rede, [Qry > > Base].[N Emp/Ag], [Qry Base].Nome, [Qry Base].[Ext VPN], [Qry Base].NIF, [Qry > > Base].[Nº conta], [Qry Base].[Dt envio Processo], [Qry Base].[SIM], [Qry > > Base].[Dt Conclusão], [Qry Base].Observações, [Qry Base].Utilizador, [Qry > > Base].DETALHE > > PIVOT Format([Periodo],"mmm") In > > ("Jan","Fev","Mar","Abr","Mai","Jun","Jul","Ago","Set","Out","Nov","Dez"); > > > > Tks again > > Pedro > > > > > > "John Spencer" wrote: > > > >> Post the SQL of your cross tab query. > >> > >> It is possible that all you have to do is add a having clause to it. > >> Generically that might look something like: > >> > >> TRANSFORM Sum(SomeValue) as MonthlySum > >> SELECT Person > >> FROM SomeTable > >> GROUP BY Person > >> HAVING SUM(SomeValue) <> 0 > >> PIVOT SomeMonthField > >> > >> John Spencer > >> Access MVP 2002-2005, 2007-2010 > >> The Hilltop Institute > >> University of Maryland Baltimore County > >> > >> Pedro wrote: > >>> Hi everyone > >>> It must be very simple for you guys but i can't find it: How to make a > >>> report, made of a query cross table, but only show records that the total of > >>> all months are diferent than zero (0). Negative and positive values. > >>> That report sums the sales for each person, each months and i want to know > >>> who sell and who's not selling. > >>> Tks in advance > >>> Pedro > >> . > >> > . > |