having zeros appear in a query

[Schmals]

i am trying to run a query where there are some zeros in one of the columns. when i run the query, it only shows the items that have numbers, and the zero rows are gone. is there a formula i have to put into the table or query to avoid this??

thanks,
schamls

 

[PHV]

Any chance you could post the SQL code of your query and say us which field exhibits this behaviour ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Schmals]

i don't think i am using a SQL code (not really sure . basically i have 5 columns.

customer
sales dollars (most have sales, some are zeros)
salesmen
year (ie. 04, 05)
month

i need to specify the month and year for the query. when i run the query, it is only showing the customers that have sales for that specified time. also i want to see during that time what customers had zero sales..

does that help???

thanks
schmals

 

[HarleyQuinn]

You can look at the SQL code for the query by clicking View-->SQL View.

HarleyQuinn
---------------------------------
Help us to help you,
read FAQ222-2244 before posting.

 

[jebry]

Hi!

This looks like a sales table, do you also have a table where information is stored about salesmen? If you do, you can add that table to the query and use an outer join to the sales table. This will allow you to get all of the salesment in the query, even if they didn't have any sales.

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[Schmals]

sorry this is so late... here is the SQL code... when i created this query, i had to do it in 2 parts in order to do sum the columns...

here is part 1

SELECT ARHIMAST.AR_MM, ARHIMAST.AR_YY, ARHITRAN.AR_WEIGHT, ARHITRAN.AR_SALES, ARHITRAN.AR_PROD, INMAST.IN_PACK, INMAST.IN_DESC, INMAST.IN_BRAND, INMAST.IN_ORIGIN, [ar_sales]-[complete cost] AS [total profit], [ar_weight]*([ar_mcost]+[ar_freunit]+[ar_rebunit]+[ar_comunit]) AS [complete cost], ARHITRAN.AR_DOCNO, ARMAST.AR_ADDR1, ARMAST.AR_ADDR2, ARMAST.AR_ADDR3, ARMAST.AR_CITY, ARMAST.AR_STATE, ARMAST.AR_ZIP, ARMAST.AR_BUYGRP, ARMAST.AR_SLSMAN, ARMAST.AR_TERMS, ARMAST.AR_PPHONE, ARMAST.AR_PFAX, ARMAST.AR_CREDIT, ARMAST.AR_PCONT1, ARMAST.AR_PCONT2, ARMAST.AR_DPHONE, ARMAST.AR_NAME, ARMAST.AR_CUST
FROM ((INMAST INNER JOIN ARHITRAN ON INMAST.IN_NO = ARHITRAN.AR_PROD) INNER JOIN ARHIMAST ON (ARHITRAN.AR_DOCNO = ARHIMAST.AR_DOCNO) AND (ARHITRAN.AR_CUST = ARHIMAST.AR_CUST)) INNER JOIN ARMAST ON ARHIMAST.AR_CUST = ARMAST.AR_CUST
WHERE (((ARHIMAST.AR_MM)="07") AND ((ARHIMAST.AR_YY)="04") AND ((ARHITRAN.AR_PROD) Between 1 And 313)) OR (((ARHIMAST.AR_MM)="07") AND ((ARHIMAST.AR_YY)="04") AND ((ARHITRAN.AR_PROD) Between 315 And 318)) OR (((ARHIMAST.AR_MM)="07") AND ((ARHIMAST.AR_YY)="04") AND ((ARHITRAN.AR_PROD) Between 320 And 999)) OR (((ARHIMAST.AR_MM)="08") AND ((ARHIMAST.AR_YY)="04") AND ((ARHITRAN.AR_PROD) Between 1 And 313)) OR (((ARHIMAST.AR_MM)="08") AND ((ARHIMAST.AR_YY)="04") AND ((ARHITRAN.AR_PROD) Between 315 And 318)) OR (((ARHIMAST.AR_MM)="08") AND ((ARHIMAST.AR_YY)="04") AND ((ARHITRAN.AR_PROD) Between 320 And 999)) OR (((ARHIMAST.AR_MM)="09") AND ((ARHIMAST.AR_YY)="04") AND ((ARHITRAN.AR_PROD) Between 1 And 313)) OR (((ARHIMAST.AR_MM)="09") AND ((ARHIMAST.AR_YY)="04") AND ((ARHITRAN.AR_PROD) Between 315 And 318)) OR (((ARHIMAST.AR_MM)="09") AND ((ARHIMAST.AR_YY)="04") AND ((ARHITRAN.AR_PROD) Between 320 And 999)) OR (((ARHIMAST.AR_MM)="10") AND ((ARHIMAST.AR_YY)="04") AND ((ARHITRAN.AR_PROD) Between 1 And 313)) OR (((ARHIMAST.AR_MM)="10") AND ((ARHIMAST.AR_YY)="04") AND ((ARHITRAN.AR_PROD) Between 315 And 318)) OR (((ARHIMAST.AR_MM)="10") AND ((ARHIMAST.AR_YY)="04") AND ((ARHITRAN.AR_PROD) Between 320 And 999)) OR (((ARHIMAST.AR_MM)="11") AND ((ARHIMAST.AR_YY)="04") AND ((ARHITRAN.AR_PROD) Between 1 And 313)) OR (((ARHIMAST.AR_MM)="11") AND ((ARHIMAST.AR_YY)="04") AND ((ARHITRAN.AR_PROD) Between 315 And 318)) OR (((ARHIMAST.AR_MM)="11") AND ((ARHIMAST.AR_YY)="04") AND ((ARHITRAN.AR_PROD) Between 320 And 999)) OR (((ARHIMAST.AR_MM)="12") AND ((ARHIMAST.AR_YY)="04") AND ((ARHITRAN.AR_PROD) Between 1 And 313)) OR (((ARHIMAST.AR_MM)="12") AND ((ARHIMAST.AR_YY)="04") AND ((ARHITRAN.AR_PROD) Between 315 And 318)) OR (((ARHIMAST.AR_MM)="12") AND ((ARHIMAST.AR_YY)="04") AND ((ARHITRAN.AR_PROD) Between 320 And 999)) OR (((ARHIMAST.AR_MM)="01") AND ((ARHIMAST.AR_YY)="05") AND ((ARHITRAN.AR_PROD) Between 1 And 313)) OR (((ARHIMAST.AR_MM)="01") AND ((ARHIMAST.AR_YY)="05") AND ((ARHITRAN.AR_PROD) Between 315 And 318)) OR (((ARHIMAST.AR_MM)="01") AND ((ARHIMAST.AR_YY)="05") AND ((ARHITRAN.AR_PROD) Between 320 And 999)) OR (((ARHIMAST.AR_MM)="02") AND ((ARHIMAST.AR_YY)="05") AND ((ARHITRAN.AR_PROD) Between 1 And 313)) OR (((ARHIMAST.AR_MM)="02") AND ((ARHIMAST.AR_YY)="05") AND ((ARHITRAN.AR_PROD) Between 315 And 318)) OR (((ARHIMAST.AR_MM)="02") AND ((ARHIMAST.AR_YY)="05") AND ((ARHITRAN.AR_PROD) Between 320 And 999)) OR (((ARHIMAST.AR_MM)="03") AND ((ARHIMAST.AR_YY)="05") AND ((ARHITRAN.AR_PROD) Between 1 And 313)) OR (((ARHIMAST.AR_MM)="03") AND ((ARHIMAST.AR_YY)="05") AND ((ARHITRAN.AR_PROD) Between 315 And 318)) OR (((ARHIMAST.AR_MM)="03") AND ((ARHIMAST.AR_YY)="05") AND ((ARHITRAN.AR_PROD) Between 320 And 999)) OR (((ARHIMAST.AR_MM)="04") AND ((ARHIMAST.AR_YY)="05") AND ((ARHITRAN.AR_PROD) Between 1 And 313)) OR (((ARHIMAST.AR_MM)="04") AND ((ARHIMAST.AR_YY)="05") AND ((ARHITRAN.AR_PROD) Between 315 And 318)) OR (((ARHIMAST.AR_MM)="04") AND ((ARHIMAST.AR_YY)="05") AND ((ARHITRAN.AR_PROD) Between 320 And 999)) OR (((ARHIMAST.AR_MM)="05") AND ((ARHIMAST.AR_YY)="05") AND ((ARHITRAN.AR_PROD) Between 1 And 313)) OR (((ARHIMAST.AR_MM)="05") AND ((ARHIMAST.AR_YY)="05") AND ((ARHITRAN.AR_PROD) Between 315 And 318)) OR (((ARHIMAST.AR_MM)="05") AND ((ARHIMAST.AR_YY)="05") AND ((ARHITRAN.AR_PROD) Between 320 And 999));

here is part 2

SELECT Sum([customer history part 1].AR_SALES) AS [Sum Of AR_SALES], Sum([customer history part 1].AR_WEIGHT) AS [Sum Of AR_WEIGHT], Sum([customer history part 1].[total profit]) AS [Sum Of total profit], Sum([customer history part 1].[complete cost]) AS [Sum of complete cost], [customer history part 1].IN_PACK, [customer history part 1].IN_DESC, [customer history part 1].IN_BRAND, [customer history part 1].IN_ORIGIN, [customer history part 1].AR_ADDR1, [customer history part 1].AR_ADDR2, [customer history part 1].AR_ADDR3, [customer history part 1].AR_CITY, [customer history part 1].AR_STATE, [customer history part 1].AR_ZIP, [customer history part 1].AR_BUYGRP, [customer history part 1].AR_SLSMAN, [customer history part 1].AR_TERMS, [customer history part 1].AR_PPHONE, [customer history part 1].AR_PFAX, [customer history part 1].AR_CREDIT, [customer history part 1].AR_PCONT1, [customer history part 1].AR_PCONT2, [customer history part 1].AR_DPHONE, [customer history part 1].AR_NAME, [customer history part 1].AR_CUST
FROM [customer history part 1]
GROUP BY [customer history part 1].IN_PACK, [customer history part 1].IN_DESC, [customer history part 1].IN_BRAND, [customer history part 1].IN_ORIGIN, [customer history part 1].AR_ADDR1, [customer history part 1].AR_ADDR2, [customer history part 1].AR_ADDR3, [customer history part 1].AR_CITY, [customer history part 1].AR_STATE, [customer history part 1].AR_ZIP, [customer history part 1].AR_BUYGRP, [customer history part 1].AR_SLSMAN, [customer history part 1].AR_TERMS, [customer history part 1].AR_PPHONE, [customer history part 1].AR_PFAX, [customer history part 1].AR_CREDIT, [customer history part 1].AR_PCONT1, [customer history part 1].AR_PCONT2, [customer history part 1].AR_DPHONE, [customer history part 1].AR_NAME, [customer history part 1].AR_CUST
HAVING ((([customer history part 1].AR_CUST)=[enter customer number]));

thanks for all your help!!

schmals