Combining freeform-entry and linked data into a single view

This technique should still work, as long as either tShipping.fCustomerID or
tShipping.fGeneralShipToID has a value

Dale

JohnM77 wrote:
Dale,

The tShipping.fCustomerID field will not necessarily contain a value. There
are often instances where items are shipped with no relation to an
Order/Customer.

-John

John,

[quoted text clipped - 26 lines]
Thanks,
John

--
HTH

Dale Fye

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/200907/1

Dale,

I tested your query method with the NZ function, and it's exactly what I'm
looking for. It even shows Shipping records where both tShipping.fCustomerID
AND tShipping.fGeneralShipToID are Null, which may be a possibility. Would
you not expect those records to show up in the result?

Thanks a million!

-John

Dale_Fye wrote:
This technique should still work, as long as either tShipping.fCustomerID or
tShipping.fGeneralShipToID has a value

Dale

Dale,

[quoted text clipped - 9 lines]
Thanks,
John


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/200907/1

Dale,

Taking your method one step further, how would I go about selecting
additional fields from the tGeneralShipTo or tCustomers tables, corresponding
to the Non-Null ID field in tShipping (tShipping.fCustomerID or tShipping.
fGeneralShipToID)? For instance, if in a given record in tShipping the
fGeneralShipToID field contains a value, how would I cause other fields from
tGeneralShipTo table (such as City, State, Zip) to also show up in the query?

Thanks,
John

JohnM77 wrote:
Dale,

I tested your query method with the NZ function, and it's exactly what I'm
looking for. It even shows Shipping records where both tShipping.fCustomerID
AND tShipping.fGeneralShipToID are Null, which may be a possibility. Would
you not expect those records to show up in the result?

Thanks a million!

-John

This technique should still work, as long as either tShipping.fCustomerID or
tShipping.fGeneralShipToID has a value
[quoted text clipped - 6 lines]
Thanks,
John

--
Message posted via http://www.accessmonster.com

John,

Yes, I would expect it to show the ShipIDs for those where there is no match
for either CustomerID or GeneralShipToID. To expand for additional fields
from the appropriate table, just keep using the NZ( ) function.

Try something like:

SELECT tS.ShippingID,
NZ([tGST].[Address], [tC].[Address]) AS ShipAddress,
NZ([tGST].[City], [tC].[City]) AS ShipCity,
NZ([tGST].[State], [tC].[State]) AS ShipState,
NZ([tGST].[Zip], [tC].[Zip]) AS ShipZip
FROM (tShipping as TS
LEFT JOIN tCustomers as tC
ON tS.CustomerID = tC.CustomerID)
LEFT JOIN tGeneralShipTo as tGST
ON tS.GeneralShipToID = tGST.GeneralShipToID;

----
HTH
Dale



"JohnM77 via AccessMonster.com" wrote:

Dale,

Taking your method one step further, how would I go about selecting
additional fields from the tGeneralShipTo or tCustomers tables, corresponding
to the Non-Null ID field in tShipping (tShipping.fCustomerID or tShipping.
fGeneralShipToID)? For instance, if in a given record in tShipping the
fGeneralShipToID field contains a value, how would I cause other fields from
tGeneralShipTo table (such as City, State, Zip) to also show up in the query?

Thanks,
John

JohnM77 wrote:
Dale,

I tested your query method with the NZ function, and it's exactly what I'm
looking for. It even shows Shipping records where both tShipping.fCustomerID
AND tShipping.fGeneralShipToID are Null, which may be a possibility. Would
you not expect those records to show up in the result?

Thanks a million!

-John

This technique should still work, as long as either tShipping.fCustomerID or
tShipping.fGeneralShipToID has a value
[quoted text clipped - 6 lines]
Thanks,
John

--
Message posted via http://www.accessmonster.com

Dale, you're a genius. It is now a complete solution to the problem. Thanks
for your time. You've been an enormous help.

Best regards,
John

Dale Fye wrote:
John,

Yes, I would expect it to show the ShipIDs for those where there is no match
for either CustomerID or GeneralShipToID. To expand for additional fields
from the appropriate table, just keep using the NZ( ) function.

Try something like:

SELECT tS.ShippingID,
NZ([tGST].[Address], [tC].[Address]) AS ShipAddress,
NZ([tGST].[City], [tC].[City]) AS ShipCity,
NZ([tGST].[State], [tC].[State]) AS ShipState,
NZ([tGST].[Zip], [tC].[Zip]) AS ShipZip
FROM (tShipping as TS
LEFT JOIN tCustomers as tC
ON tS.CustomerID = tC.CustomerID)
LEFT JOIN tGeneralShipTo as tGST
ON tS.GeneralShipToID = tGST.GeneralShipToID;

----
HTH
Dale

Dale,

[quoted text clipped - 24 lines]
Thanks,
John

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/200907/1