Need to See Master records without Detail Records

  • Thread starter Thread starter saraqpost
  • Start date Start date
S

saraqpost

I only found one post on this in MS Access (others in Paradox, Oracle).
The "solution" is to use a Left Join, which I am using, but I am not
getting the records to show where I have only a Master record and no
details.

Below is my query and the subset of Data that SHOULD show, but doesn't
is presented first.

Thanks!
Sara

TrailerInYardKey DateDropped TrailerNum FreightCoKey
90 6/9/2006 198 6
91 6/1/2006 1 7
92 8/1/2006 98A 18

Key 90 has 2 detail records on tblTrailerInYardDetails; Key 91 and 92
have 0 Details.

SELECT DISTINCT tblTrailerInYard.TrailerInYardKey,
tblTrailerInYard.DateDropped, tblTrailerInYard.TrailerNum,
tblFreightCo.FreightCo, tblTrailerInYard.TrailerInYardStatus,
tblTrailerInYard.WhseKey,
tblTrailerInYardDetails.TrailerInYardDetailsKey,
tblTrailerInYardDetails.KeyRecNum, IIf([PONum]=1," ",[PoNum]) AS PO,
IIf([tblPOInfo].[PONum]=1," ",[tblPOInfo].[CreditSalesTo] & "-" &
[DeptCategory]) AS Dept, tblTrailerInYardDetails.NumPallets,
tblTrailerInYard.UnloadDate, tblFreightCo.FreightCOKey,
tblTrailerInYardDetails.TrailerInYardDetailsStatus

FROM (tblFreightCo
RIGHT JOIN tblTrailerInYard ON tblFreightCo.FreightCOKey =
tblTrailerInYard.FreightCoKey) LEFT JOIN ((tblTrailerInYardDetails
LEFT JOIN tblPOInfo ON tblTrailerInYardDetails.POKey = tblPOInfo.POKey)

LEFT JOIN [Static Dept Data] ON tblPOInfo.CreditSalesTo = [Static Dept
Data].CreditSalesTo) ON tblTrailerInYard.TrailerInYardKey =
tblTrailerInYardDetails.TrailerInYardKey
WHERE (((tblTrailerInYard.TrailerInYardStatus)="A") AND
((tblTrailerInYard.WhseKey)=[Forms]![frmListTrailersDroppedInYard]![txtWhseKey])
AND ((tblTrailerInYardDetails.TrailerInYardDetailsStatus)="A"))
ORDER BY tblTrailerInYard.DateDropped DESC;
 
The problem is caused by the where clause. You are screening out Null
values with this line
AND ((tblTrailerInYardDetails.TrailerInYardDetailsStatus)="A")

Try the following modification to the Where clause

WHERE tblTrailerInYard.TrailerInYardStatus="A" AND
tblTrailerInYard.WhseKey=[Forms]![frmListTrailersDroppedInYard]![txtWhseKey]
AND (tblTrailerInYardDetails.TrailerInYardDetailsStatus="A" OR
tblTrailerInYardDetails.TrailerInYardDetailsStatus is Null)

You might have to do the something similar for the first line. It depends
on what you want.
 
THANK YOU SO MUCH!!!

Not only does it work (did you hear me screech with shocked delight?),
but I understand WHY I wasn't getting the records.

All set, John.

Again, a million thanks.
sara


John said:
The problem is caused by the where clause. You are screening out Null
values with this line
AND ((tblTrailerInYardDetails.TrailerInYardDetailsStatus)="A")

Try the following modification to the Where clause

WHERE tblTrailerInYard.TrailerInYardStatus="A" AND
tblTrailerInYard.WhseKey=[Forms]![frmListTrailersDroppedInYard]![txtWhseKey]
AND (tblTrailerInYardDetails.TrailerInYardDetailsStatus="A" OR
tblTrailerInYardDetails.TrailerInYardDetailsStatus is Null)

You might have to do the something similar for the first line. It depends
on what you want.


I only found one post on this in MS Access (others in Paradox, Oracle).
The "solution" is to use a Left Join, which I am using, but I am not
getting the records to show where I have only a Master record and no
details.

Below is my query and the subset of Data that SHOULD show, but doesn't
is presented first.

Thanks!
Sara

TrailerInYardKey DateDropped TrailerNum FreightCoKey
90 6/9/2006 198 6
91 6/1/2006 1 7
92 8/1/2006 98A 18

Key 90 has 2 detail records on tblTrailerInYardDetails; Key 91 and 92
have 0 Details.

SELECT DISTINCT tblTrailerInYard.TrailerInYardKey,
tblTrailerInYard.DateDropped, tblTrailerInYard.TrailerNum,
tblFreightCo.FreightCo, tblTrailerInYard.TrailerInYardStatus,
tblTrailerInYard.WhseKey,
tblTrailerInYardDetails.TrailerInYardDetailsKey,
tblTrailerInYardDetails.KeyRecNum, IIf([PONum]=1," ",[PoNum]) AS PO,
IIf([tblPOInfo].[PONum]=1," ",[tblPOInfo].[CreditSalesTo] & "-" &
[DeptCategory]) AS Dept, tblTrailerInYardDetails.NumPallets,
tblTrailerInYard.UnloadDate, tblFreightCo.FreightCOKey,
tblTrailerInYardDetails.TrailerInYardDetailsStatus

FROM (tblFreightCo
RIGHT JOIN tblTrailerInYard ON tblFreightCo.FreightCOKey =
tblTrailerInYard.FreightCoKey) LEFT JOIN ((tblTrailerInYardDetails
LEFT JOIN tblPOInfo ON tblTrailerInYardDetails.POKey = tblPOInfo.POKey)

LEFT JOIN [Static Dept Data] ON tblPOInfo.CreditSalesTo = [Static Dept
Data].CreditSalesTo) ON tblTrailerInYard.TrailerInYardKey =
tblTrailerInYardDetails.TrailerInYardKey
WHERE (((tblTrailerInYard.TrailerInYardStatus)="A") AND
((tblTrailerInYard.WhseKey)=[Forms]![frmListTrailersDroppedInYard]![txtWhseKey])
AND ((tblTrailerInYardDetails.TrailerInYardDetailsStatus)="A"))
ORDER BY tblTrailerInYard.DateDropped DESC;
Click to expand...
Click to expand...
 
THANK YOU SO MUCH!!!

Not only does it work (did you hear me screech with shocked delight?),
but I understand WHY I wasn't getting the records.

All set, John.

Again, a million thanks.
sara


John said:
The problem is caused by the where clause. You are screening out Null
values with this line
AND ((tblTrailerInYardDetails.TrailerInYardDetailsStatus)="A")

Try the following modification to the Where clause

WHERE tblTrailerInYard.TrailerInYardStatus="A" AND
tblTrailerInYard.WhseKey=[Forms]![frmListTrailersDroppedInYard]![txtWhseKey]
AND (tblTrailerInYardDetails.TrailerInYardDetailsStatus="A" OR
tblTrailerInYardDetails.TrailerInYardDetailsStatus is Null)

You might have to do the something similar for the first line. It depends
on what you want.


I only found one post on this in MS Access (others in Paradox, Oracle).
The "solution" is to use a Left Join, which I am using, but I am not
getting the records to show where I have only a Master record and no
details.

Below is my query and the subset of Data that SHOULD show, but doesn't
is presented first.

Thanks!
Sara

TrailerInYardKey DateDropped TrailerNum FreightCoKey
90 6/9/2006 198 6
91 6/1/2006 1 7
92 8/1/2006 98A 18

Key 90 has 2 detail records on tblTrailerInYardDetails; Key 91 and 92
have 0 Details.

SELECT DISTINCT tblTrailerInYard.TrailerInYardKey,
tblTrailerInYard.DateDropped, tblTrailerInYard.TrailerNum,
tblFreightCo.FreightCo, tblTrailerInYard.TrailerInYardStatus,
tblTrailerInYard.WhseKey,
tblTrailerInYardDetails.TrailerInYardDetailsKey,
tblTrailerInYardDetails.KeyRecNum, IIf([PONum]=1," ",[PoNum]) AS PO,
IIf([tblPOInfo].[PONum]=1," ",[tblPOInfo].[CreditSalesTo] & "-" &
[DeptCategory]) AS Dept, tblTrailerInYardDetails.NumPallets,
tblTrailerInYard.UnloadDate, tblFreightCo.FreightCOKey,
tblTrailerInYardDetails.TrailerInYardDetailsStatus

FROM (tblFreightCo
RIGHT JOIN tblTrailerInYard ON tblFreightCo.FreightCOKey =
tblTrailerInYard.FreightCoKey) LEFT JOIN ((tblTrailerInYardDetails
LEFT JOIN tblPOInfo ON tblTrailerInYardDetails.POKey = tblPOInfo.POKey)

LEFT JOIN [Static Dept Data] ON tblPOInfo.CreditSalesTo = [Static Dept
Data].CreditSalesTo) ON tblTrailerInYard.TrailerInYardKey =
tblTrailerInYardDetails.TrailerInYardKey
WHERE (((tblTrailerInYard.TrailerInYardStatus)="A") AND
((tblTrailerInYard.WhseKey)=[Forms]![frmListTrailersDroppedInYard]![txtWhseKey])
AND ((tblTrailerInYardDetails.TrailerInYardDetailsStatus)="A"))
ORDER BY tblTrailerInYard.DateDropped DESC;
Click to expand...
Click to expand...
 
I guess that explains the ringing in my ears. Glad it worked for you and
I'm even happier that you understand why it works.


THANK YOU SO MUCH!!!

Not only does it work (did you hear me screech with shocked delight?),
but I understand WHY I wasn't getting the records.

All set, John.

Again, a million thanks.
sara


John said:
The problem is caused by the where clause. You are screening out Null
values with this line
AND ((tblTrailerInYardDetails.TrailerInYardDetailsStatus)="A")

Try the following modification to the Where clause

WHERE tblTrailerInYard.TrailerInYardStatus="A" AND
tblTrailerInYard.WhseKey=[Forms]![frmListTrailersDroppedInYard]![txtWhseKey]
AND (tblTrailerInYardDetails.TrailerInYardDetailsStatus="A" OR
tblTrailerInYardDetails.TrailerInYardDetailsStatus is Null)

You might have to do the something similar for the first line. It
depends
on what you want.


I only found one post on this in MS Access (others in Paradox, Oracle).
The "solution" is to use a Left Join, which I am using, but I am not
getting the records to show where I have only a Master record and no
details.

Below is my query and the subset of Data that SHOULD show, but doesn't
is presented first.

Thanks!
Sara

TrailerInYardKey DateDropped TrailerNum FreightCoKey
90 6/9/2006 198 6
91 6/1/2006 1 7
92 8/1/2006 98A 18

Key 90 has 2 detail records on tblTrailerInYardDetails; Key 91 and 92
have 0 Details.

SELECT DISTINCT tblTrailerInYard.TrailerInYardKey,
tblTrailerInYard.DateDropped, tblTrailerInYard.TrailerNum,
tblFreightCo.FreightCo, tblTrailerInYard.TrailerInYardStatus,
tblTrailerInYard.WhseKey,
tblTrailerInYardDetails.TrailerInYardDetailsKey,
tblTrailerInYardDetails.KeyRecNum, IIf([PONum]=1," ",[PoNum]) AS PO,
IIf([tblPOInfo].[PONum]=1," ",[tblPOInfo].[CreditSalesTo] & "-" &
[DeptCategory]) AS Dept, tblTrailerInYardDetails.NumPallets,
tblTrailerInYard.UnloadDate, tblFreightCo.FreightCOKey,
tblTrailerInYardDetails.TrailerInYardDetailsStatus

FROM (tblFreightCo
RIGHT JOIN tblTrailerInYard ON tblFreightCo.FreightCOKey =
tblTrailerInYard.FreightCoKey) LEFT JOIN ((tblTrailerInYardDetails
LEFT JOIN tblPOInfo ON tblTrailerInYardDetails.POKey = tblPOInfo.POKey)

LEFT JOIN [Static Dept Data] ON tblPOInfo.CreditSalesTo = [Static Dept
Data].CreditSalesTo) ON tblTrailerInYard.TrailerInYardKey =
tblTrailerInYardDetails.TrailerInYardKey
WHERE (((tblTrailerInYard.TrailerInYardStatus)="A") AND
((tblTrailerInYard.WhseKey)=[Forms]![frmListTrailersDroppedInYard]![txtWhseKey])
AND ((tblTrailerInYardDetails.TrailerInYardDetailsStatus)="A"))
ORDER BY tblTrailerInYard.DateDropped DESC;
Click to expand...
Click to expand...
Click to expand...
 
Back
Top