Can't seem to get the correct result

[allboutdB]

Hi Everyone,

I have this sales tracking dB where i track sales and return for the ee's , I am trying to write a query where i would take the total sales for the month and the total returns(returns categorized by electronics and non electronics) and work out a %(i.e. in the dB employee DBOS has 400 sales and 3 returns all 3 returns are nonelectronics, anything other than "Remote Starter", "Car Alarm","Stereo" is non electronics so his nonelectronics sales Performance would be (400-3)/400 = 99.25%, and he does not have electronics return so that should be 100% but as those record don't show up in the qry_electronics_Returns as he does not have any electronics return i do not have any records for him for electronics sales performance which would actually be 100% as he had no electronics returned, the final result i get is by averaging the two % but because the employee does not have any electronic items returned i am not getting his final result as there are no records for this ee in the qry_electronics_return, so my question is, is there anyway that i could show his result as 100% for electronics sales performance so that i would get the correct final result for this ee which is (99.25%+100%)/2= 99.63% rather than not getting any result for this ee or any other ee's who do not have a return in one of the two categories

Regards

 

[duluter]

I created a left join query on your Sales_Reps table and your qry_Electronics_Return query to add in the employees who did not have electronics returns, giving these employees a return count of zero for the selected month:

SELECT Sales_Reps.UserID, nz([Total_electronics_Return],0) AS TotalElectronicSales, nz(qry_electronics_Return.Month,(SELECT TOP 1 qry_electronics_Return.Month FROM qry_electronics_Return)) AS MONTH
FROM Sales_Reps LEFT JOIN qry_electronics_Return ON Sales_Reps.UserID = qry_electronics_Return.UserID;

Note that my method of assigning a month to the zero-count records may be flawed, depending on how your data is set up. In your example table, all of the records were from the same month. I assume that in your real system you have multiple months worth of data. In that case, you need to first query these tables to return records from the month you want, then base the above query off of this result set.

Anyway, you could create a similar query on your non-electronics returns, and base your subsequent queries off of these.

Hope this helps.


Duluter

 

[allboutdB]

Hi duluter,

Thank you so much this is exactly what i was looking for, so just that i understand the nz function will return a 0 even if the record does not exist for the employee? as i am starting out in access and don't have much experience with access would you recommend a good book on access functions

Thank you once again for your help

Cheers

 

[duluter]

The nz function will return either the value that you feed into it, or, if that value is null, then the function returns whatever you specify. In this case, I specified for it to be zero.

I myself am still learning about the Access environment. Perhaps others here know a good book on Access functions.


Duluter

 

[allboutdB]

I Just read up on the nz function on the MSDN and and a few other sites and i still didn't get it until i read your last post,you made it clear.

thanks again