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
0
Utf
6/2/2010 11:47:01 AM
access.reports 4434 articles. 0 followers. Follow

5 Replies
1429 Views

Similar Articles

[PageSpeed] 42

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
0
John
6/2/2010 12:48:37 PM
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
> .
> 
0
Utf
6/4/2010 11:54:07 AM
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
>> .
>>
0
John
6/6/2010 8:16:07 PM
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
> >> .
> >>
> .
> 
0
Utf
6/7/2010 11:57:43 AM
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
>>>> .
>>>>
>> .
>>
0
John
6/7/2010 1:30:20 PM
Reply:

Similar Artilces:

Customer Reports by "Month" Criterion
Hi, I wasn't able to get RMS to generate customer reports by the "Month" criterion, using one of the custom definable DATE fields. Some businesses keep track of their customers DOBs and it would be very useful for them to run a report and get a list of all customers born, say in the month of July. I searched thru the KB at PartnerSource, and all I found was article 869845 issued for QuickSell 2000, which basically uses a numeric field to to assign a value for each month of the year. For businesses with a large customer list, that could be quite a task. I was wondering if...

Table of Contents for each section
I have tried several times to create a TOC for each section of my document. They way it is now I can simply go to the Insert a TOC and it will insert e perfect TOC. All my text has been changed to the appropriate Level and I have no problem there. But I need to seperate the chapters to the start of their respective sections (I have next page breaks inserted at the start of every chapter). I have tried the bookmark method, no luck. I have tried other methods but I think my problem is that I have selected the appropriate fields and changed their levels appropriately. I have not mes...

Insert Query skipping duplicate values
I have two tables -Individuals -Participation Participation table has two fields IndividualID - related to the Individual table ParticipationCode it's a one to many relationship where the participation table has a complex/dual primary key (the ID and the Code) I need to run an insert query that will insert a participation code for an individual ONLY if they do not have that code already (because of the complex/dual key) any ideas? Thanks in advance. Chris Well, if you have made the primary key those two fields, then you won't be able to add any existing combination - you wil...

Recursive Query
I've been working with a recursive query recently and I've been unable to get the expected results. I'm looking to pull a customer purchase record for each time they purchased a particular product and to give them a discount if it's been over a year since they purchased a product and then additional discounts if it's been a year since they last received a discount. So, if I have the following information: LineID Customer ProdPurchaseDate 1 Cust1 2007-05-05 2 Cust1 2007-09-20 3 Cust1 2008-04-16 4 Cust1 2008-06-...

form fields not saving to table
I have created a database with a form and three of the fields are not saving the information to the table. I have checked the row and control sources and now at a loss and thoughts? As you scroll through existing records is the data of the table displayed? Post the SQL of the query or form source. -- Build a little, test a little. "Kim" wrote: > I have created a database with a form and three of the fields are not saving > the information to the table. I have checked the row and control sources and > now at a loss and thoughts? > > ...

Excel not saving query parameters
Hi, I have folowing problem... it seems like excel problem... who knows.... using excel 2007 I've created simple macro enabled excel workbook. Then I use Microsoft Query to access one of my SQL Server tables ... create query with parameter. Then I use excel "Parameters" dialog to set parameter as a reference to specific cell. everything works fine.... however when i close and reopen excel file parameters setup is lost... how to preserve parameter setup? Hi, I'm having exactly the same problem in MS Excel 2007 & I came across this thread - I see you posted a while ago b...

Status in table PurchaseOrderEntryDetail
Hello, We're trying to populate serial # into the PurchaseOrderEntryDetail table and run across the column 'Status'. What are the possible values for Status column in this table? Most of the time we see status of '2'. What does that mean? Please help. Many Thanks & Best Regards, Nikki ...

Use icon sets relatively in a pivot table
Hi All! I can use icon sets for certain things, but am unable to figure out how to use them with my existing data. I have a pivot table that may list sales from Quarter 4. If you expand that level of the pivot table you would see the monthly, weekly and daily sales that constituted Quarter 4. But I want to show icons to give a visual cue if Q4 was up from Q3. I would need to do this again in the future to see if Q1 is up from the previous years Q4. Sample Data would be: Week Sales (want to see icons in this column, beside numbers) 4 20 3 15 I w...

Formula for cross tab data filling
Hi All Excel 2003 How to using formula for data filling as below (Y/N) ? Sheet A Product A Product B Product C System A Y N Y System B Y Y N Sheet B System A Product A System A Product C System B Product A System B Product B moonhkt ...

Validation List Query
Hi all, I've got a validation list showing a project problem status: New, Resolved, In Progress. These options are a named range 'Status'. Thus I do Data>Validation>Allow:List>Source:=Status No problem there. However, from time to time I need to input a custom status, which could be anything. The problem being that once I have a cell validated it does not allow me to type anything in it. Is there a means that keeps the validation drop down, but also lets me input my own data if, and when, I need to? I could always remove the validation from the individual cells, but h...

Pivot Table from Access through MS Query
I am trying to create an Excel pivot table from a simple Access query with 2 parameters, using MS Query. (start date, end date) I have been able to create the pivot table from the query easily without the parameters and I have been able to run the query by itself with the parameters, but when I try to run the pivot off the edited query with parameters, I get an error message that parameters are not allowed. Is there some way around this? Thank you. You could create a pivot table from the query without parameters. Then use code similar to the following, to prompt for a date range: '...

Change an underlying query paramaters by button on a form
Hi, I have a form that is bound to a query. The form displays a list of servers and a count of errors that have occured on each server. I also have two buttons on my form, one which is labelled 'All' and the other is labelled 'Errors Only'. I have modified the query (via the QBE grid) to allow for both situations, and copied the SQL as follows: When I click the 'All' button, I would like the following SQL to run: SELECT Full_Compare_Q.BTSContactGroup, Full_Compare_Q.Server, Full_Compare_Q.RecID, Abs(Sum(Services.Valid)) AS ErrorCount, IIf([Ping...

Tender Denomination report
hello is there a a tender denomination report. Any assistance would be great >thanks I would have to say there is not a report for denominations. The software doesn't keep track of how many pennies, dimes, quarters, etc.. that are going in and out of the till. It is keeping track of total change due or total change recieved. even though you can setup a denominations calculator to help you count your change, it is simply just used as a tool to help count money. But it is not keeping an audit log of how many pennies, dimes, quarters, etc.. were counted. -- Regards, Andy...

Hourly/Transaction Count Report
Hello, I know that using the POS I can easily get a report to show me my hourly sales, and give me a transaction count (basically a customer count), by doing shift f6. Can I get a print out of this through the manager reports? -- Thank You,; Vince That report is available in the Report Library. https://mbs.microsoft.com/downloads/customer/Custom_Detailed_Sales_Report_by_Hour.zip Another reason why you would want to be on a Maintenance Plan if not already. Rob "Vince" <Vince@discussions.microsoft.com> wrote in message news:0B9FFE84-2A2C-4AC0-B6D8-30B83BF436AA@microsoft....

Avoid redundant table design
------------------------------------------------------------------------------ -- I am trying to properly design a table with the following structure: tblLoan (Main Table) LoanID LoanDescription tblApplicant (Primary Applicant) ApplicantID LoanID FirstName LastName ISPrimary Rand605, Not much to go on... I am assuming you want to a database to track Loans? Then you actually need three tables. One for Loans, one for Applicants and the last one for Loan Applicants as more then one applicant can be on a Loan Application and an Applicant can have more then one loan ...

ap invoice query with gl distribution accounts
hello everybody- I need to make a query that will pull the posted payable payments of each invoice. I am going to use this [http://victoriayudin.com/2009/09/11/sql-view-for-all-posted-payables-payments-in-dynamics-gp/ ] query from Victoria Yudin [thank you Victoria] But I also need to specify what distribution GL account each invoice hit. How can I add that to this query? Thanks in advance. Here you go: SELECT dbo.PM10400.PMNTNMBR, dbo.PM10400.DOCNUMBR, dbo.PM10400.DOCDATE, dbo.PM10400.VENDORID, dbo.PM00200.VENDNAME, dbo.PM10400.PYENTTYP, dbo.PM10...

Problem with KB973475 & Excel Pivot Tables
Excel Office Prof. 2003 in Windows XP Prior to the installation of KB973475 we had no problems but now 1. when we add data to a pivot table and go to create a pivot chart report - when prompted to reselect the critira, excel crashes 2. working in a regular excel sheet, wanting to consoidate information, (retrieving data from a pivot table) excel crashes removing KB973475 allows these actions to complete without issue. But as soon as it is installed we have problems. I have read through the FAQ and the Vulnerability Info for KB973475 but do not see what would be a solution ...

How to delete extra rows in a table?
Hi In an MS Access database, I have a table that keeps the history of some user actions. I need to implement a mechanism to keep the number of entries in this table to a decent value. Shortly, I want to keep only the newest 5000 records. Each record has a timestamp field. I there a query that will delete all other records? Thanks. -----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 DELETE * FROM table_name WHERE date_column <= #earliest date you want to keep# Example of WHERE clause: WHERE invoice_date <= #2/1/2008# Substitute your table and column names. -- MGFoster:::mgf00 <at>...

View Null Results in Query
i have an access 2003 query SELECT [AlarmData Table Filtered By 60 Min Trip].[Event Type], [AlarmData Table Filtered By 60 Min Trip].State, DateSerial(Year([Date/Time*]),Month([Date/Time*]),Day([Date/Time*])) AS [Date], TimeSerial(Hour([Date/Time*]),Minute([Date/Time*]),0) AS TimePeriod, Count(*) AS Total FROM [AlarmData Table Filtered By 60 Min Trip] GROUP BY [AlarmData Table Filtered By 60 Min Trip].[Event Type], [AlarmData Table Filtered By 60 Min Trip].State, DateSerial(Year([Date/Time*]),Month([Date/Time*]),Day([Date/Time*])), TimeSerial(Hour([Date/Time*]),Minute([Date/...

Adding PST Tax onto Invoice Report
Hi there, Can anyone show me or help me start off in modifying the sales invoice report to display both GST and PST taxes for Canadian companies. I know that the report is within report writer but am clueless on how to start this off, but more on where I need to pull the data. Thanks. Hi Ian, This is fairly easy... you'll have to work with calculated fields and use the schdeduled tax table (TX_Detail_MSTR = TX00201). Normaly, those taxes are already taken care within the default SOP Blank Form, but I suspect that you want to show the various tax labels and their respec...

Access Query Not Found criteria
Hello - - I have a query in Access that searches in part a table that list the 'events' of employees. These events can be many things, name change, hire, term etc.... What I'm trying to do is search for data entry errors. In this case, for employees that were term'd (with a term date) but no term event was entered. The field name is CODE. I've done things like '<> TERM', Not In ("TERM"), NOT LIKE TERM...etc. But I getting a ton of records that will list all term'd employees it just excludes this CODE. Is there a criteria that would show on...

Anti Log Table from Log Tables
For my children I wanted to have practice in using log table while multiplying and dividing. For this I have a book Clark's Tables (Science Data Book) by Orient Longmans This has Log and Anti Log tables. This is what I have been using in my school days. Now this book is not easily available. I wanted to take a hard copy of the these Log and anti log tables. But I was not successful...as the paper is quite soiled. Then I made search on the net. I got a link. http://www.sosmath.com/tables/logtable/logtable.html Here I have log tables. I also would like to have Antilog tables also...which is...

custom report filter by customer & supplier
We would like to have the reporting capability to pull a customer list with name, address, email and phone number (where available of course) filtered on customers who have purchased Vera Bradley(Supplier) products in the past. We do use Vera Bradley as a department. The output would be, for example, 1000 customers out of 4000 in the total customer database have purchased a Vera Bradley product and thus show Vera Bradley in their detailed customer purchases. The report would give us just the subset of 1000 customers to use for target marketing purposes. The report would need to be...

Budget queries
Hi I'm new to setting up a budget. I have the following queries: 1. What does the green down arrow mean next to the name of the "Receive From" name? and what does the up red arrow mean under the Expenses? 2. Why is there row underneath the above heading called "Other Income for "Wages& Salary"? The amount in this row is 0. This is happening for every Payslip entry I have entered in the Payslip. 3. Why is there a "Subtotal"? I find this confusing to read. Can it be removed somehow? 4. I've got other headings with nothing in them eg....

Pivot Table based on date
Hi, I want to have a pivot-table that counts the no. of records based on month of year - my list contains dates in the format dd/mm/yyyy How can I get the pivot table to only be based on the month? As long as you have no blank spaces where dates should be, right click on the dates within the Pivot table and select Group / Outline. You will likely find Months is already selected, but if your data spans more than one year you will also need to click on Years as well, else it will group all Jans together, all Febs together etc. If you get a message that says unable to group, then you have ...