Solved Need help in Query

[jocph]

I am currently updating a project I inherited. I am trying to simplify some of the queries and I come accross this:

SELECT DISTINCT DWR_1.NDEQRNo, DWR_1.ID AS RecID, DWR_Spools.ProjID, DWR_1.StatCode, 
DWR_Spools.DwgNo, DWR_Spools.SpoolNo, ([DWR_Spools]![WeldNoPrefix]+"-") & 
[DWR_Spools]![WeldNoType] & "-" & [DWR_Spools]![WeldNoSeq] & ("-"+[DWR_Spools]![WeldNoSuffix]) 
AS WeldNo2, DWR_Joint.Sortx1, DWR_1.DateWeld, DWR_1.NDERequestDate
FROM DWR_Joint INNER JOIN (DWR_Spool INNER JOIN (DWR_Spools 
LEFT JOIN DWR_1 ON DWR_Spools.ID = DWR_1.SpoolID) ON DWR_Spool.SpoolNo = DWR_Spools.SpoolNo) 
ON DWR_Joint.ID = DWR_Spools.ID
WHERE (((DWR_Spools.ProjID)=7))
ORDER BY DWR_Spools.DwgNo, DWR_Spools.SpoolNo, DWR_Joint.Sortx1, DWR_1.DateWeld, DWR_1.NDERequestDate;

Which gives this result:

As you can see, there are 2 records for WeldNo FW-54 and FW-61. How can I modify it to output only the latest record for those WeldNos without affecting the other records? I hope it makes sense.

 

[theDBguy]

Hi. What you could try for now is create a Totals query to return the most recent date for each WeldNo. Then, create another query To join your old query and the Totals query on the WeldNo and the date columns.

 

[jocph]

Hi theDBGuy, I was thinking along those lines but still on the process of figuring out how to do it. Update you later.

 

[theDBguy]

Hi theDBGuy, I was thinking along those lines but still on the process of figuring out how to do it. Update you later.

Okay, sounds good. Good luck!

 

[jocph]

Okay, so I made a query with only 2 of the fields shown (WeldNo2 and MaxofNDERequestDate), which is good since the duplicates are gone. Let's say I name the previous query as T1 and this query without duplicates T2.

How do I join them to get all fields from T1 but only the records from T2?

Tried every join in the query editor but it is not giving me the correct records.

 

[theDBguy]

Okay, so I made a query with only 2 of the fields shown (WeldNo2 and MaxofNDERequestDate), which is good since the duplicates are gone. Let's say I name the previous query as T1 and this query without duplicates T2.

How do I join them to get all fields from T1 but only the records from T2?

Tried every join in the query editor but it is not giving me the correct records.

For example:

SELECT T1.* FROM T1 INNER JOIN T2 ON T1.WeldNo=T2.WeldNo AND T1.NDERequestDate=T2.MaxofNDERequestDate

 

[jocph]

I'm almost there. I think the nulls from the dates are not being included. how do I include them?

 

[theDBguy]

I'm almost there. I think the nulls from the dates are not being included. how do I include them?

Hi. I don't follow. The Totals query should give you a date for each WeldNo. Are you saying some WeldNo records don't have a date?

 

[jocph]

Yes. In case the NDE is not requested yet. Got it to work with this:

ON t1.WeldNo2 = t2.WeldNo2
WHERE (((t1.NDERequestDate)=[t2].[MaxOfNDERequestDate]))
OR (((t1.NDERequestDate) Is Null));

Seems to be giving me the desired results.

 

[theDBguy]

Yes. In case the NDE is not requested yet. Got it to work with this:

ON t1.WeldNo2 = t2.WeldNo2
WHERE (((t1.NDERequestDate)=[t2].[MaxOfNDERequestDate]))
OR (((t1.NDERequestDate) Is Null));

Seems to be giving me the desired results.

Hi. Glad to hear you got it sorted out. Good luck with your project.

 

[jocph]

Thanks theDBGuy for your guidance! I'm sure I will be back for more.