If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Report made of a query cross table
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 |
#2
|
|||
|
|||
Report made of a query cross table
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 |
#3
|
|||
|
|||
Report made of a query cross table
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 . |
#4
|
|||
|
|||
Report made of a query cross table
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 . |
#5
|
|||
|
|||
Report made of a query cross table
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 . . |
#6
|
|||
|
|||
Report made of a query cross table
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 . . |
Thread Tools | |
Display Modes | |
|
|