A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Setting Up & Running Reports
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Report made of a query cross table



 
 
Thread Tools Display Modes
  #1  
Old June 2nd, 2010, 12:47 PM posted to microsoft.public.access.reports
pedro
external usenet poster
 
Posts: 129
Default 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  
Old June 2nd, 2010, 01:48 PM posted to microsoft.public.access.reports
John Spencer
external usenet poster
 
Posts: 7,815
Default 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  
Old June 4th, 2010, 12:54 PM posted to microsoft.public.access.reports
pedro
external usenet poster
 
Posts: 129
Default 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  
Old June 6th, 2010, 09:16 PM posted to microsoft.public.access.reports
John Spencer
external usenet poster
 
Posts: 7,815
Default 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  
Old June 7th, 2010, 12:57 PM posted to microsoft.public.access.reports
pedro
external usenet poster
 
Posts: 129
Default 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  
Old June 7th, 2010, 02:30 PM posted to microsoft.public.access.reports
John Spencer
external usenet poster
 
Posts: 7,815
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 01:25 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.