combine join properties Number 2 and Number 3

[hengsin]

two tables:
tblEmployeeForecast, tblEmployeeActual

Fields in tblEmployeeFOrecast:
EmployeeNoForecast, EmployeeNameForecast, CostCenterForecast

Fields in tblEmployeeActual:
EmployeeNoActual, EmployeeNameActual, CostCenterActual

eg data for tblEmployeeForecast:
EmployeeNoForecast EmployeeNameForecast
1 lim
2 heng
3 sin
4 tan

eg data for tblEmployeeActual:
EmployeeNoActual EmployeeNameActual
1 lim
2 heng
3 sin
5 kok

Note that the only different for those two tables is the data in the final field.

Problem:
I joint the two tables in query with EmployeeNoFOrestcast and EmployeeNoActual. I want to be able to display out all the data with all the feilds in the query result. If there is no corresponding data (in this example, EmployeeNo 4, EmployeeName tan and EmployeeNo5, EmployeeName kok are the different data for those two tables.

I noticed that the join properties Number 2 and Number 3 only fulfill part of i want. Maybe there is a way to join up the join properties Number 2 and Number 3. Any idea????

 

[LittleSmudge]

Well first off your post is very confusing because ( I Think ) you are using the term Field when you mean RECORD.

If that's not the case - then what follows will not help.
I hope it is the case because thats the only way I can seem to make sense of what you are talking about.


Essentially - you seem to want to combine the two tables into one and include all of the RECORDS for both tables.

To do that you need a UNION query

SELECT * FROM tblEmployeeForecast
UNION
SELECT * FROM tblEmployeeActual


Then you'll want to get rid of the duplications that the UNION creates, so you'll need DISTINCT

SELECT DISTINCT * FROM (
SELECT * FROM tblEmployeeForecast
UNION
SELECT * FROM tblEmployeeActual
)



'ope-that-'elps.



G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

 

[hengsin]

thanks, but my first language is not English. SOrry to confuse you.