duplacate output

[Crash1hd]

the following sql doesnt quite do what I am tryinh to achieve

SELECT DISTINCT Employees.tblInitials, Inbound.tblDate_Review AS Inbound, Inbound.tblRRound, Outbound.tblDate_Review AS Outbound, Outbound.tblRRound, Critical_Criteria.tblDate_Review AS [Critical Criteria], Mandatory_Skills_Data.tblDate_Review AS Data, Mandatory_Skills_Helpdesk.tblDate_Review AS Helpdesk, Mandatory_Skills_IRO.tblDate_Review AS IRO
FROM (((((Employees LEFT JOIN Inbound ON Employees.tblInitials = Inbound.tblInitials) LEFT JOIN Outbound ON Employees.tblInitials = Outbound.tblInitials) LEFT JOIN Critical_Criteria ON Employees.tblInitials = Critical_Criteria.tblInitials) LEFT JOIN Mandatory_Skills_Data ON Employees.tblInitials = Mandatory_Skills_Data.tblInitials) LEFT JOIN Mandatory_Skills_Helpdesk ON Employees.tblInitials = Mandatory_Skills_Helpdesk.tblInitials) LEFT JOIN Mandatory_Skills_IRO ON Employees.tblInitials = Mandatory_Skills_IRO.tblInitials;

so when its run I get a table like so

tblInitials	Inbound		Inbound.tblRRound	Outbound	Outbound.tblRRound	Critical Criteria	Data	Helpdesk	IRO
AAS		2/10/2004	1			3/23/2004	2				
AAS		2/10/2004	1			6/29/2004	3

the problem is that you will see the Inbound.tblRRound is duplicated because there is 2 outbound dates how do I stop it from duplicate fields?

 

[pbaldy]

The way to eliminate the "duplicates" is to eliminate the Outbound fields from the SELECT clause. The DISTINCT predicate eliminates duplicates based on all fields in the SELECT clause. Since the Outbound fields are different, it includes both records.

 

[Crash1hd]

but I need one that shows all 5 or do I need to make 5 individual ones and call them accordingly

 

[pbaldy]

Shows all 5 fields but only one record? Which data should it get for the 2 outbound fields, which are different? Can you describe in more detail what you want to see?

 

[Crash1hd]

where it shows this

tblInitials	Inbound		Inbound.tblRRound	Outbound	Outbound.tblRRound	Critical Criteria	Data	Helpdesk	IRO
AAS		2/10/2004	1			3/23/2004	2				
AAS		2/10/2004	1			6/29/2004	3

it should show this

tblInitials	Inbound		Inbound.tblRRound	Outbound	Outbound.tblRRound	Critical Criteria	Data	Helpdesk	IRO
AAS		2/10/2004	1			3/23/2004	2				
AAS							6/29/2004	3

In the end I am creating a form that will display the dates that each inbound / outbound ext... that where done

I found that by creating a subform in the form seems to do the trick