need to exclude when value = $0.00

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Been working on this for a long time...
My table contains sales & income from 2004 to current... thanks to this
group I am able to query YTD and MTD 2004 and 2005 on the same report.
BUT when I query 2005 only, report lists every single customer, showing $0.00
for customers with no 2005 sales....

I don't want the report to show customers with no sales in 2004

These are the expressions
YTD Income: Sum(Abs(Year([PostMonth])=(2005))*[CommPaid])
YTDSales: Sum(Abs(Year([PostMonth])=(2005))*[CommVolume])
JAN Income: Sum(Abs(Year([PostMonth])=2005 And
Month([PostMonth])=1)*[CommPaid])

Maybe I need to approach from the Report ?

THANKS !!
 
What does your overall query look like? I would have thought that you could
do this simply by adding a criteria to it, like YTDSales <> 0
 
TABLE contains fields: Vendor - Customer- Sales -Income - PostMonth -Rep

QUERY:
MTD Income: Sum(Abs(Year([PostMonth])=2005 And
Month([PostMonth])=1)*[Income]) note: I change the "1" to a "2" or "3" etc
for current month

YTD Income: Sum(Abs(Year([PostMonth])=(2005))*[Income])

YTD Sales: Sum(Abs(Year([PostMonth])=(2005))*[Sales])

Vendor: (so I can query by vendor only if necessary)

Customer: (so I can query my customer only if necessary)

PostMonth:

Rep: (so I can query by rep if necessary)

REPORT fields:
Vendor Customer Sales Income Rep PostMonth
The report returns my complete customer listing, I only want customers with
sales 2005 ... the report lists ALL my customers even those with no '05
sales, showing $0.00 , wihich is correct, but makes for a very long report.
 
Um, I get the impression that you're working with the Query Designer and
don't know much about SQL - I've put together your query in a test database,
and I think my initial suggestion of entering a criteria of "<> 0" for YTD
Sales will do the job.

The underlying SQL would then become:

SELECT Sum(Abs(Year([PostMonth])=2005 And Month([PostMonth])=1)*[Income]) AS
[MTD Income], Sum(Abs(Year([PostMonth])=(2005))*[Income]) AS [YTD Income],
Sum(Abs(Year([PostMonth])=(2005))*[Sales]) AS [YTD Sales], Table1.Vendor,
Table1.Customer, Table1.Rep
FROM Table1
GROUP BY Table1.Vendor, Table1.Customer, Table1.Rep
HAVING (((Sum(Abs(Year([PostMonth])=(2005))*[Sales]))<>0));

If you're having trouble figuring it out, you could enter this in the SQL
view and see what the Query Designer then looks like...
 
Your impression is correct... I don't know diddles about SQL... guess I'll
have to study up on it then try your suggestion... at the moment it's beyond
me... but I'll figure it out...
 
You don't really need SQL to try my suggestion about the criteria, though -
how familiar are you with the Query Designer? The top half has the tables,
where you can create links by dragging fields from one table to another, and
the bottom half is a table where you can see the resulting output columns.

Each of these columns has, going down the page, Field:, Table:, Sort:, Show:,
Criteria:, or: and then several blank lines.

YTDSales should be in the Field: part of one of these columns. If in the
Criteria: part of that same column you put <> 0, that should also do the job.
 
I'm pretty comfortable w/Query Designer... I'll give it a try :) THANKS
THANKS THANKS for the help!
 
Back
Top