Solved Need help in Query (2 Viewers)

jocph

Member
Local time
Today, 23:34
Joined
Sep 12, 2014
Messages
61
I am currently updating a project I inherited. I am trying to simplify some of the queries and I come accross this:

Code:
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:

1586955145111.png


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

I’m here to help
Staff member
Local time
Today, 08:34
Joined
Oct 29, 2018
Messages
21,358
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

Member
Local time
Today, 23:34
Joined
Sep 12, 2014
Messages
61
Hi theDBGuy, I was thinking along those lines but still on the process of figuring out how to do it. Update you later.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:34
Joined
Oct 29, 2018
Messages
21,358
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

Member
Local time
Today, 23:34
Joined
Sep 12, 2014
Messages
61
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

I’m here to help
Staff member
Local time
Today, 08:34
Joined
Oct 29, 2018
Messages
21,358
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:

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

jocph

Member
Local time
Today, 23:34
Joined
Sep 12, 2014
Messages
61
I'm almost there. I think the nulls from the dates are not being included. how do I include them?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:34
Joined
Oct 29, 2018
Messages
21,358
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

Member
Local time
Today, 23:34
Joined
Sep 12, 2014
Messages
61
Yes. In case the NDE is not requested yet. Got it to work with this:

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

Seems to be giving me the desired results.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:34
Joined
Oct 29, 2018
Messages
21,358
Yes. In case the NDE is not requested yet. Got it to work with this:

Code:
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

Member
Local time
Today, 23:34
Joined
Sep 12, 2014
Messages
61
Thanks theDBGuy for your guidance! I'm sure I will be back for more.
 

Users who are viewing this thread

Top Bottom