Report made of a query cross table
in [Reports]
|
From: John Spencer on 7 Jun 2010 09:30 You can copy the query, modify it, and use the modified copy for this one report. Also, if you are using VBA code to call the report, you should be able to include the filter Dim strFilter as String StrFilter = "[Total de CUSTO]<>0" DoCmd.OpenReport "MyReportName",acViewPreview,,strFilter As long as the field Total de Custo is used someplace in the report, you can filter by it. John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County Pedro wrote: > 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 >>>> . >>>> >> . >> |