Combining freeform-entry and linked data into a single view

I have a shipping table in which the user enters/selects a "Ship To". The
user first selects an option button for whether a "Customer" or "Other" will
be specified. If Customer is selected, the user will select a customer from a
combobox. The Shipping table would then store the CustomerID corresponding to
the combobox selection, with Customer address info stored in a Customers
table. If "Other" is selected, the user will enter the "Ship To" data
directly into name/address/city/state/zip textboxes (data stored either
directly in Shipping or in a third table named "Other"?).

My question is, on my Shipping form, how can I display
name/address/city/state/zip as the same fields in a datasheet subform,
regardless of whether the data comes from the Customers table or an Other
table or directly from the shipping table?

This concept of combining freeform data entry and dropdown list selection
into one table seems to come up quite often. Any thoughts on how to elegantly
handle this scenario in general, or the above scenario specifically, would be
greatly appreciated.

Thanks,
John

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

Well,

I would think you would store the "Other" shipping data in a table somewhere,
and would then store either the CustomerID in a ShipToID field (with the
order data), or store the OrderID with the Other shipping data. This would
give you the ability to Left join between the Order table and the Customers
table (on the ShipToID field), and also from the Orders table to the
OtherShipping table (on the OrderID field).

Then, you could use the NZ function to get either the customer address data
or the "OtherShipping" address data,

Select OrderID,
NZ(Customer.Address1, OtherShipping.Address1) as Address1,
NZ(Customer.Name, OtherShipping.ShipTo) as As ShipTo,
...


HTH
Dale

JohnM77 wrote:
I have a shipping table in which the user enters/selects a "Ship To". The
user first selects an option button for whether a "Customer" or "Other" will
be specified. If Customer is selected, the user will select a customer from a
combobox. The Shipping table would then store the CustomerID corresponding to
the combobox selection, with Customer address info stored in a Customers
table. If "Other" is selected, the user will enter the "Ship To" data
directly into name/address/city/state/zip textboxes (data stored either
directly in Shipping or in a third table named "Other"?).

My question is, on my Shipping form, how can I display
name/address/city/state/zip as the same fields in a datasheet subform,
regardless of whether the data comes from the Customers table or an Other
table or directly from the shipping table?

This concept of combining freeform data entry and dropdown list selection
into one table seems to come up quite often. Any thoughts on how to elegantly
handle this scenario in general, or the above scenario specifically, would be
greatly appreciated.

Thanks,
John

--
HTH

Dale Fye

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

Thanks for your reply, Dale.

Shipping records are often independent of orders, since shipping is also
logged for non-order-related use. Therefore the "ShipTo" is specified
directly in the Shipping table, rather than by reference to OrderID.

Let me clarify my original table and field structure...

Three tables: 1) Shipping, 2) Customers and 3) GeneralShipTo

Both the Customers and GeneralShipTo tables contain address information. The
Shipping (Key = ShippingID) table contains a ShipToID field which contains
either a value from either the CustomerID field (Key to Customers table) or a
GeneralShipToID field (Key to GeneralShipTo table). Since Shipping can't
distinguish between a CustomerID and GeneralShipToID, I'm not sure how to
write the query that would blend these three tables together to show [Ship To
Name], Address, City, State, Zip, etc in one view.

Something tells me that the GeneralShipTo table should have a ShippingID
field, rather than the Shipping table carrying GeneralShipToID in ShipToID
(basically reversing the direction of the relationship between Shipping and
GeneralShipTo tables).

With all that in mind, does your left-join and NZ function method still apply?
I'll definitely study up on your suggestions. I just want to make sure I
understand the principle.

Thanks again for your time!

-John

Dale_Fye wrote:
Well,

I would think you would store the "Other" shipping data in a table somewhere,
and would then store either the CustomerID in a ShipToID field (with the
order data), or store the OrderID with the Other shipping data. This would
give you the ability to Left join between the Order table and the Customers
table (on the ShipToID field), and also from the Orders table to the
OtherShipping table (on the OrderID field).

Then, you could use the NZ function to get either the customer address data
or the "OtherShipping" address data,

Select OrderID,
NZ(Customer.Address1, OtherShipping.Address1) as Address1,
NZ(Customer.Name, OtherShipping.ShipTo) as As ShipTo,
...

HTH
Dale

I have a shipping table in which the user enters/selects a "Ship To". The
user first selects an option button for whether a "Customer" or "Other" will
[quoted text clipped - 17 lines]
Thanks,
John


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

Given this structure, I would personally put 2 ShipToID fields
(ShipToCustomerID, ShipToGeneralID) in my shipping table. If you only have a
single ShipToID field, and it could contain either a CusomersID or a
GeneralShipToID, then how will you ever know which was intended? And if you
have a CustomersID that matches a GeneralShipToID, how would you know which
to use?

Dale

JohnM77 wrote:
Thanks for your reply, Dale.

Shipping records are often independent of orders, since shipping is also
logged for non-order-related use. Therefore the "ShipTo" is specified
directly in the Shipping table, rather than by reference to OrderID.

Let me clarify my original table and field structure...

Three tables: 1) Shipping, 2) Customers and 3) GeneralShipTo

Both the Customers and GeneralShipTo tables contain address information. The
Shipping (Key = ShippingID) table contains a ShipToID field which contains
either a value from either the CustomerID field (Key to Customers table) or a
GeneralShipToID field (Key to GeneralShipTo table). Since Shipping can't
distinguish between a CustomerID and GeneralShipToID, I'm not sure how to
write the query that would blend these three tables together to show [Ship To
Name], Address, City, State, Zip, etc in one view.

Something tells me that the GeneralShipTo table should have a ShippingID
field, rather than the Shipping table carrying GeneralShipToID in ShipToID
(basically reversing the direction of the relationship between Shipping and
GeneralShipTo tables).

With all that in mind, does your left-join and NZ function method still apply?
I'll definitely study up on your suggestions. I just want to make sure I
understand the principle.

Thanks again for your time!

-John

Well,

[quoted text clipped - 21 lines]
Thanks,
John

--
HTH

Dale Fye

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

I see what you're saying with the ID conflict. It looks like I would then
populate only one of the ShipToID fields, thereby making the distinction in
the source of address fields. I've created lots of basic Access queries, but
none required manually writing the SQL statement. I'll study up on the lingo
and see if I can come up with a query that blends the redundant Customers and
GeneralShipTo fields into single fields.

Thanks,
John

Dale_Fye wrote:
Given this structure, I would personally put 2 ShipToID fields
(ShipToCustomerID, ShipToGeneralID) in my shipping table. If you only have a
single ShipToID field, and it could contain either a CusomersID or a
GeneralShipToID, then how will you ever know which was intended? And if you
have a CustomersID that matches a GeneralShipToID, how would you know which
to use?

Dale

Thanks for your reply, Dale.

[quoted text clipped - 32 lines]
Thanks,
John


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

Dale,

I created a Union query to combine the Customers and GeneralShipTo records
into a single view called AllShipToAddresses . Then I created another query
that includes the Shipping table and AllShipToAddresses query. The result is
a massive recordset consisting of over 3 million records.

I see now that the problem with my method is that the AllShipToAddresses
query has only a single ShipToID field, which cannot link to the two fields
GeneralShipToID and CustomerID. If I'm not mistaken, this is called a Cross
Join, because the query has no explicit join defined.

Reference:
http://office.microsoft.com/en-gb/ac...CH100645771033


Do you know of a way to get around the problem of linking two fields with one,
or should I scrap this approach altogether?

Thanks,
John

Dale_Fye wrote:
Given this structure, I would personally put 2 ShipToID fields
(ShipToCustomerID, ShipToGeneralID) in my shipping table. If you only have a
single ShipToID field, and it could contain either a CusomersID or a
GeneralShipToID, then how will you ever know which was intended? And if you
have a CustomersID that matches a GeneralShipToID, how would you know which
to use?

Dale

Thanks for your reply, Dale.

[quoted text clipped - 32 lines]
Thanks,
John


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

John,

At this point, part of my problem is that I'm not entirely sure of the names
of your tables and fields. Could you please post that in your next reply?

1. Did you modify the Shipping table by creating the GeneralShipToID field?
If not, you are going to have difficulties.

2. Once you have this new field, you are going to have to try to sort out
whether the ShipToID field that you currently have in your Shipping table is
a valid CustomerID. If not, then it should probably match up with the
GeneralShipToID. You could probably do something like:

a. Backup the database
b. Backup the database
c. UPDATE tblShipping
SET GeneralShipToID = ShipToID
FROM tblShipping
LEFT JOIN tblCustomers
ON tblShipping.ShipToID = tblCustomers.CustomerID
WHERE tblCustomers.CustomerID IS NULL

This would fill in the GeneralShipToID field for those records where the
ShipToID does not match a CustomerID. The problem with this is that if your
CustomerID field is an autonumber field, and your GeneralShipToID field in
your GeneralShipTo table is also an autonumber, then you are likely to have
lots of overlap. Is there any other way that you can determine whether the
ShipToID field in tblShipping is a CustomerID

3. Once you get the single ShipToID field divided into ShipToCustomerID and
GeneralShipToID, then you can create a single query between tblShipping, with
LEFT JOINs to tblCustomers and tblGeneralShipTo.

JohnM77 wrote:
Dale,

I created a Union query to combine the Customers and GeneralShipTo records
into a single view called AllShipToAddresses . Then I created another query
that includes the Shipping table and AllShipToAddresses query. The result is
a massive recordset consisting of over 3 million records.

I see now that the problem with my method is that the AllShipToAddresses
query has only a single ShipToID field, which cannot link to the two fields
GeneralShipToID and CustomerID. If I'm not mistaken, this is called a Cross
Join, because the query has no explicit join defined.

Reference:
http://office.microsoft.com/en-gb/ac...CH100645771033

Do you know of a way to get around the problem of linking two fields with one,
or should I scrap this approach altogether?

Thanks,
John

Given this structure, I would personally put 2 ShipToID fields
(ShipToCustomerID, ShipToGeneralID) in my shipping table. If you only have a
[quoted text clipped - 10 lines]
Thanks,
John

--
HTH

Dale Fye

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

Dale,

Thanks for sticking with me on this. I'll try to simplify the scenario. I
don't know conventional nomenclature, but for this purpose I'll use the
following prefixes:

t = Table
f = Field
q = Query

Structu

Table: tShipping
Fields: fShippingID (Key), fCustomerID and fGeneralShipToID.

Table: tCustomers
Fields: fCustomerID and fAddress
(Note: tCustomers.fCustomerID field is linked to tShipping.fCustomerID)

Table: tGeneralShipTo
Fields: fGeneralShipToID and fAddress
(Note: tGeneralShipTo.fGeneralShipToID field is linked to tShipping.
fGeneralShipToID)

My ultimate goal is to create a datasheet view which displays shipping
records, including a single Address field showing either the tCustomers.
fAddress or the tGeneralShipTo.fAddress, depending on whether tShipping.
fCustomerID or tShipping.fGeneralShipToID is populated (never both).

Hopefully that clears up the table structure and what I need to accomplish
with it.

Thanks for your time!

-John

Dale_Fye wrote:
John,

At this point, part of my problem is that I'm not entirely sure of the names
of your tables and fields. Could you please post that in your next reply?

1. Did you modify the Shipping table by creating the GeneralShipToID field?
If not, you are going to have difficulties.

2. Once you have this new field, you are going to have to try to sort out
whether the ShipToID field that you currently have in your Shipping table is
a valid CustomerID. If not, then it should probably match up with the
GeneralShipToID. You could probably do something like:

a. Backup the database
b. Backup the database
c. UPDATE tblShipping
SET GeneralShipToID = ShipToID
FROM tblShipping
LEFT JOIN tblCustomers
ON tblShipping.ShipToID = tblCustomers.CustomerID
WHERE tblCustomers.CustomerID IS NULL

This would fill in the GeneralShipToID field for those records where the
ShipToID does not match a CustomerID. The problem with this is that if your
CustomerID field is an autonumber field, and your GeneralShipToID field in
your GeneralShipTo table is also an autonumber, then you are likely to have
lots of overlap. Is there any other way that you can determine whether the
ShipToID field in tblShipping is a CustomerID

3. Once you get the single ShipToID field divided into ShipToCustomerID and
GeneralShipToID, then you can create a single query between tblShipping, with
LEFT JOINs to tblCustomers and tblGeneralShipTo.

Dale,

[quoted text clipped - 22 lines]
Thanks,
John


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

John,

Here is the way I would address it:

SELECT tShipping.ShippingID,
NZ([tGeneralShipTo].[Address], [tCustomers].[Address]) AS
ShipAddress
FROM (tShipping LEFT JOIN tCustomers
ON tShipping.CustomerID = tCustomers.CustomerID)
LEFT JOIN tGeneralShipTo
ON tShipping.GeneralShipToID = tGeneralShipTo.GeneralShipToID;

Because of the Left Joins, you will get all records from tShipping. I would
expect that your tShipping.CustomerID field will always contain data, and
that the GeneralShipToID field in that table will only contain a value if the
shipping address is other than the customers address. So, by using the NZ
function, you can test to determine whether there is a value in the
GeneralShipToID field based on whether there is a match with tGeneralShipTo.
If so, then use the address in tGeneralShipTo. If not, then use the
customers address.

----
HTH
Dale



"JohnM77 via AccessMonster.com" wrote:

Dale,

Thanks for sticking with me on this. I'll try to simplify the scenario. I
don't know conventional nomenclature, but for this purpose I'll use the
following prefixes:

t = Table
f = Field
q = Query

Structu

Table: tShipping
Fields: fShippingID (Key), fCustomerID and fGeneralShipToID.

Table: tCustomers
Fields: fCustomerID and fAddress
(Note: tCustomers.fCustomerID field is linked to tShipping.fCustomerID)

Table: tGeneralShipTo
Fields: fGeneralShipToID and fAddress
(Note: tGeneralShipTo.fGeneralShipToID field is linked to tShipping.
fGeneralShipToID)

My ultimate goal is to create a datasheet view which displays shipping
records, including a single Address field showing either the tCustomers.
fAddress or the tGeneralShipTo.fAddress, depending on whether tShipping.
fCustomerID or tShipping.fGeneralShipToID is populated (never both).

Hopefully that clears up the table structure and what I need to accomplish
with it.

Thanks for your time!

-John

Dale_Fye wrote:
John,

At this point, part of my problem is that I'm not entirely sure of the names
of your tables and fields. Could you please post that in your next reply?

1. Did you modify the Shipping table by creating the GeneralShipToID field?
If not, you are going to have difficulties.

2. Once you have this new field, you are going to have to try to sort out
whether the ShipToID field that you currently have in your Shipping table is
a valid CustomerID. If not, then it should probably match up with the
GeneralShipToID. You could probably do something like:

a. Backup the database
b. Backup the database
c. UPDATE tblShipping
SET GeneralShipToID = ShipToID
FROM tblShipping
LEFT JOIN tblCustomers
ON tblShipping.ShipToID = tblCustomers.CustomerID
WHERE tblCustomers.CustomerID IS NULL

This would fill in the GeneralShipToID field for those records where the
ShipToID does not match a CustomerID. The problem with this is that if your
CustomerID field is an autonumber field, and your GeneralShipToID field in
your GeneralShipTo table is also an autonumber, then you are likely to have
lots of overlap. Is there any other way that you can determine whether the
ShipToID field in tblShipping is a CustomerID

3. Once you get the single ShipToID field divided into ShipToCustomerID and
GeneralShipToID, then you can create a single query between tblShipping, with
LEFT JOINs to tblCustomers and tblGeneralShipTo.

Dale,

[quoted text clipped - 22 lines]
Thanks,
John


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

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

Dale Fye wrote:
John,

Here is the way I would address it:

SELECT tShipping.ShippingID,
NZ([tGeneralShipTo].[Address], [tCustomers].[Address]) AS
ShipAddress
FROM (tShipping LEFT JOIN tCustomers
ON tShipping.CustomerID = tCustomers.CustomerID)
LEFT JOIN tGeneralShipTo
ON tShipping.GeneralShipToID = tGeneralShipTo.GeneralShipToID;

Because of the Left Joins, you will get all records from tShipping. I would
expect that your tShipping.CustomerID field will always contain data, and
that the GeneralShipToID field in that table will only contain a value if the
shipping address is other than the customers address. So, by using the NZ
function, you can test to determine whether there is a value in the
GeneralShipToID field based on whether there is a match with tGeneralShipTo.
If so, then use the address in tGeneralShipTo. If not, then use the
customers address.

----
HTH
Dale

Dale,

[quoted text clipped - 70 lines]
Thanks,
John

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