Why not all data checked in query are shown?

[Harrold]

Hi

I have a few tables about salary. When i use query to show data for salary and allowances, the datasheet only come out with those result where both salary and allowances have figures together. eg employee A has salary and allowance, so his data show, but employee B only has only salary and his data not show.

When i create another two query separately for salary and allowance each, the data come out correctly.

Why the combination not work? Is there anything i shall do?

Thanks

 

[John Big Booty]

What is your table structure? What is the the default value for both those fields?

 

[Harrold]

Hi John,

Thanks for your prompt relpy.

I attached a copy of relationships for your review. What does default value mean?

Thanks again

 

[John Big Booty]

The Default value is the value a field will hold if there is no user input.

I suspect that you problem is arising because, where no allowance for an employee has been entered there is a Null value ie. no record in the Allowance table for the employeeID, and as you are querying employees and asking for Salary and Allowance, where there is no allowance that employee is not returned.

What you need to do is ensure that even where an employee is not entitled to an allowance his EmployeeID is still recorded in the Allowance table, but with a Zero value.

 

[marlan]

Let me add my view:

Are you using a Left or Right join? that can give you all employees, even if thay have no Salary and Allowance joind to them.

A double click on the joining line in the query builder - and you are there.

Good luck!

 

[Harrold]

Hi Marlan,

Thanks for your advise. But sorry that i cant really get it. Can you give me a link or detailed instruction?

 

[marlan]

Hi,

This is a left join in the query builder (see the left arrow?). that meens: All records from Orders table, even if they have no orderDetails. you can determan the type of join by double clicking on the arrow.

If this is new to you, you had better learn some joining in SQL, it is very usefull and powerfull.

try here: http://www.sqlcourse.com/index.html for basics
and here: http://www.sqlcourse2.com/index.html for more advanced.

i liked these, but there are many more.

 

[Harrold]

Hi

I have use those left join method and it works. But it has another issue. For those has salary and allowance, their names appear twice for the same entry. How so? and how to rectify this problem?

Thank you so much

 

[John Big Booty]

Have a look at the Group By option available in a Totals Query.

 

[Harrold]

Hi John,

Please see my attached file. It shows my query selection. My problem is like for Employee A, there are salary and allowance for Dec 2011 for him. hence, by right, in the query datasheet, the entry shall only appear once. but it appears twice for the same person.

So i dont know where i get things wrong.

i really appreciate your help.

 

[marlan]

Hi Harrold,

I think I would do it in 2 stages:
1. Have a query left link employees to Salary, and give it a name (say... EmpSal)
2. Left link EmpSal to Allowance.

There may be a more elegant way to do it, but this way sould work.