Access Query

[henk]

hi everyone,
I am using a query that will show me the sales figures. basically it shows me how many of each part i have sold in a particular year, my query works perfect for that. Now What i want is let say there is a part no that we have not sold at all in 2011. so my query return me with all the years on which we have sold something but it wont return anything for 2011 since we have not sold any of those part that year. i want my query to return that part no and everything and display 0 in the qty sold. Any help is appreciated. i am usng access 2007

 

[pr2-eugin]

Hello and Welcome to AWF..

Could you post the Query that you have so far?

 

[henk]

hi i just attched my query. so if you see in the query i am pulling all the sales record that i have since 2007.

 

[pr2-eugin]

That really is not what I wanted.. Switch to the SQL view of the Query and copy and paste it here... But looking at the criteria, you have placed 2007 and the ID inside "" which makes them Strings.. Have the Fiscal Year and ID been defined as Text?

 

[henk]

SELECT Nz([CUST_ORDER_ID],'n/a') AS cust_ord, IIf([TYPE]="O",[QTY],[QTY]*-1) AS Expr1, SYSADM_DEG_FISCAL_YEAR.FISCAL_YEAR, SYSADM_INVENTORY_TRANS.WORKORDER_BASE_ID, SYSADM_PART.ID, SYSADM_PART.DESCRIPTION, IIf([cust_ord]='n/a',[Expr1]=0,[Expr1]) AS Expr2
FROM (SYSADM_INVENTORY_TRANS INNER JOIN SYSADM_DEG_FISCAL_YEAR ON SYSADM_INVENTORY_TRANS.TRANSACTION_DATE = SYSADM_DEG_FISCAL_YEAR.DATE) INNER JOIN SYSADM_PART ON SYSADM_INVENTORY_TRANS.PART_ID = SYSADM_PART.ID
WHERE (((SYSADM_DEG_FISCAL_YEAR.FISCAL_YEAR)>"2007") AND ((SYSADM_PART.ID)="515194"));

 

[pr2-eugin]

Could you shed some light on this...

But looking at the criteria, you have placed 2007 and the ID inside "" which makes them Strings.. Have the Fiscal Year and ID been defined as Text?

 

[henk]

sure,
so what i am doing here is just to get the sales figures right i only want the transactions that has a customer order id attached to it. then i am using the Exp1 and Exp2 in a way to get the actual sales figures of each part. it works fine and it give me right result. the only problem that i have is it wont show that year in which there was no sale made. lets say we have sold part no. 112 in 2010 and qty= 2, then the same part we have not sold at all in 2011 so the qty should be =0 but it wont show anything for that year and return null if i run it exclusively for that year. i want it to show everthing as it is in each filed and the qty will be zero for the years we have not sold anything. qty=Exp2.

 

[henk]

it is doing that because there is no transaction made in that year. i want to know if there is a workaround that i can use to get the desired result.

than you.

 

[pr2-eugin]

Could you upload a Stripped down version of your DB?? Look HERE 'How to'..

 

[henk]

sorry but i am not sure what you mean by striped down version. the databse i am using is huge.

 

[henk]

what i was thinking is to somehow use the iif function to get the desired result. something like this iif the fiscal year used in the criteria does not exist then use the part id , part description and qty=0 for those year else show the desired result. does that make any sense?