Hiding duplicate records 1

[jmc014]

This question has been asked may times before but after reading the threads, I cannot seem to find a solution:

I've got a query that contains three tables (inner joined), while i get the results I'm searching for, I often get duplicate records.
Here is the SQL for the query in question:

SELECT LOI_Crew.Id, LOI_Crew.Idd, LOI_Crew.Name, LOI_Crew.Surname, LOI_Crew.Rank, LOI_Crew.Nationality, LOI_Crew.POB, LOI_Crew.DOB, LOI_Crew.Passport, LOI_Crew.Date_Issue_PP, LOI_Crew.Date_Expiry_PP, LOI_Crew.SBook, LOI_Crew.Date_Issue_SB, LOI_Crew.Date_Expiry_SB, LOI_Crew.Time_Onboard, LOI_Crew.Delete, LOI_Crew.Pasado_Policia, LOI_Data.VesselName, LOI_Data.Voy, LOI_Data.Bandera, LOI_Data.IMO, LOI_Data.Procedencia, LOI_Data.Destino, LOI_Data.Arrival_Date, LOI_Data.Arrival_Time, LOI_Data.Dept_Date, LOI_Data.Dept_Time, LOI_Data.[Embassy/Consulate], LOI_Data.Embassy_Fax, LOI_Data.Embassy_Email, LOI_Data.Automovil, LOI_Data.Tren, LOI_Data.Avion, LOI_Data.Enrolamiento, LOI_Data.Transferencia, LOI_Data.Fin_Contrato, LOI_Data.Crewing_Office, LOI_Data.Crewing_Tel, LOI_Data.Crewing_Email, LOI_Data.[1_Aeropuerto_Salida], LOI_Data.[1_Aeropuerto_Llegada], LOI_Data.[1_Salida_Date], LOI_Data.[1_Salida_Time], LOI_Data.[1_Llegada_Date], LOI_Data.[1_Llegada_Time], LOI_Data.[1_Numero_Vuelo], LOI_Data.[2_Aeropuerto_Salida], LOI_Data.[2_Salida_Date], LOI_Data.[2_Salida_Time], LOI_Data.[2_Llegada_Date], LOI_Data.[2_Llegada_Time], LOI_Data.[2_Numero_Vuelo], LOI_Data.[3_Aeropuerto_Salida], LOI_Data.[3_Aeropuerto_Llegada], LOI_Data.[3_Salida_Date], LOI_Data.[3_Salida_Time], LOI_Data.[3_Llegada_Date], LOI_Data.[3_Llegada_Time], LOI_Data.[3_Numero_Vuelo], LOI_Data.[4_Aeropuerto_Salida], LOI_Data.[4_Aeropuerto_Llegada], LOI_Data.[4_Salida_Date], LOI_Data.[4_Salida_Time], LOI_Data.[4_Llegada_Time], LOI_Data.[4_Numero_Vuelo], LOI_Data.[5_Aeropuerto_Salida], LOI_Data.[5_Aeropuerto_Llegada], LOI_Data.[5_Salida_Date], LOI_Data.[5_Salida_Time], LOI_Data.[5_Llegada_Date], LOI_Data.[5_Numero_Vuelo], LOI_Data.Aeropuerto_Entrada_Esp, LOI_Data.Fecha_Entrada_Esp, LOI_Data.Puerto_Embarque, LOI_Data.Comentarios, LOI_Data.Extra_Details, LOI_Data.[2_Aeropuerto_Llegada], LOI_Data.[5_Llegada_Time], LOI_Data.Code_Id, LOI_Crew.SDate, LOI_Crew.Code_Id, Taxi_Date_Search.WOrder, Taxi_Date_Search.Voa
FROM (LOI_Data LEFT JOIN LOI_Crew ON LOI_Data.Idd = LOI_Crew.Idd) INNER JOIN Taxi_Date_Search ON LOI_Data.Code_Id = Taxi_Date_Search.Id
WHERE ((Not (LOI_Crew.SDate) Is Null) AND ((Taxi_Date_Search.Voa)=True));

Can someone tell me the best way to hide the duplicate from my query once it's run.
Thanks for all,

 

[dhookom]

Change the query to a totals/group by query. You can then set some fields/columns to Group By and others to another aggregate such as Min or Max.

Duane
Hook'D on Access
MS Access MVP

 

[jmc014]

Thanks, while your explanation make sence, I'm not quite sure how that's going to work.

Example:

My query will sometimes give me the following reqults (or something similar):

Id Idd Name Sirname Rank
------------------------------------------
2 v02 John Smith c/o
136 v136 Mark Simons m/m
2 v02 John Smith c/o
136 v136 Mark Simons m/m

My aim is to hide the duplicate entires so that I only get two reports and not four (once printed)

The Id field is autonumber while the Idd field is text, I cannot change these as I could run into all sorts of problems.
How can I implement your idea to get the needed results? I'm not sure If I can?

Thanks,

 

[Andrzejek]

Did you try:
[tt]
SELECT [red]DISTINCT[/red] LOI_Crew.Id, ...
[/tt]
???

Have fun.

---- Andy

 

[jmc014]

Thanx,

I'm having fun, sort of..(al least for the time being) I already tried the SELECT DISTINCT, but It did'nt work. It grouped the results insted of deleting/hiding the duplicate value. I got:

2 v02 John Smith c/o
2 v02 John Smith c/o
136 v136 Mark Simons m/m
136 v136 Mark Simons m/m

Could it be that I'm using this command the wrong way?
Thanks for any further input,

 

[dhookom]

This query

SELECT Id, Idd, Name, Sirname, Rank
FROM ...
GROUP BY Id, Idd, Name, Sirname, Rank;

Will not return:
[tt]
2 v02 John Smith c/o
136 v136 Mark Simons m/m
2 v02 John Smith c/o
136 v136 Mark Simons m/m
[/tt]

Duane
Hook'D on Access
MS Access MVP

 

[jmc014]

It worked, thanks lads,

The seconed inner join to third table was the problem, the duplications stopped as soon as I deleted the third table.

 

[PHV]

Anyway, a SELECT DISTINCT shhould do the same dedumplicate stuff, dot.