Advice need how to setup table relationship

[larpup]

I have a small app in A2003 similar to Northwind. If you change a
customer address in the customer table, it changes all orders in the
(previously entered into the ) orders table (when you complete a new
record in the orders form.)

I need the customer address, city, state, zip, etc. to remain static in
the orders form for previously entered orders. I realize I must make
another table but need advice how to set the relationships. I do not
want to utilize the DLookup function unless I have to.

Any advice will be greatly appreciated.

Larry

 

[Allen Browne]

The approach in Northwind should suit you perfectly.

The Customer table has the client's usual address.
The Orders table has the shipping address for this particular order.
The Orders form is based on Orders Qry - a query that uses both tables.
The CustomerID combo on the Orders form has code in its AfterUpdate event
that copies the client address fields into the order address fields as the
default. You can type in a different address if you want, and it stays
recorded with that order.

 

[larpup]

Allen,

Thanks, guess I missed that but will research later tonight and get
back to you.

Regards,

Lar

 

[larpup]

Allen,

The answer was so simple I am embarrassed for asking the question...
Thank you.

Larry

 

[larpup]

Studied it, got it.

My scenerio is a wee bit different. I need to make the Customers static
in the order table. I don't need to make field assignments as in the
Northwind example.

The difference is that the Ship To is is accomplished by field
assignments. I understand that. I would need to make field assignments
to the actual Customers.. OR,, have duplicate data (at times) depending
if the Customer data changes.

Am I making myself clear here?

Lar

 

[Allen Browne]

Hi Lar. I think your first statement means you have solved this to your
satisfaction.

Yes, there are other data structures that may be useful. For the one I'm
working on at present, one client has many addresses of different types
(street, postal, delivery, billing, ...) A job for a client can be delivered
to any suitable type of address for that client, so the Job table does not
store the addresses but refers to one in the ClientAddress table, with a
button for adding a new client address as needed. That sounds similar to
what you are doing.

Another approach is to nest clients inside each other, e.g. Bob Jones can be
a client, and he can be an employee of Ajax Ltd who is also a client in
their own right, and could even be a subsidiary of Acme Corp who is also a
client in its own right. This gives you the possibility to assign particular
addresses for clients and also have them inherit the address of their
parent. There's an example of this structure in:
People in households and companies - Modelling human relationships
at:
http://allenbrowne.com/AppHuman.html

 

[larpup]

Allen,

Let me explain with some better detail.

I have a DropDown of customers which the customer selects from a form
where the main data is Orders. The DropDown is the customer table. The
Customer table is linked to the Order table. Many to one.

I have an "address book" of Customers and it's possible that an address
change can occur. It a change does occur, all previous transactions
will change in the Orders Form.

Are you recommending that I have an the customer data hidden and then
do field assignments to OrderAddress, OrderCity, etc...

In this database, I have a shipper and a consignee, so there are two
DropDowns.I would need to hide the customer data for Consignees as well
and do field assignments then.. This just doesn't seem like the best
solution to me.

It appears to me that the order (job) should store the actual data, but
this will cause redundant data as well.

Still confused. Sorry,

Regards,

Lar

 

[larpup]

Allen,

I guess what I'm try to say is..

I have a client and there are many orders entered for him. The client
has only one address. The client moves to a new city, hence new
address, but same ClientId. How do you setup the table/relationship so
the old records don't change to the new address?

Lar

 

[J. Goddard]

You will need another table to keep track of when addresses change,
which will have as a minimum -

ClientID
Start Date
End Date (often null for the current address)
Address Stuff....

Every client will have at least one record in this table.

Your queries would then have to figure out which address is valid for a
particular order date; I've had to try to do this (thankfully not on a
regular basis), and as I recall the solution involves some tricky
where-clauses with the dates. Perfectly do-able, however.

It's easy to think of many situations requiring this type of
arrangement, for example military training:

When a military member takes a training course, what was their rank
at the time, and what base were they posted to at the time?

Hope this gets you pointed in the right direction.

John

 

[John Vinson]

I have a client and there are many orders entered for him. The client
has only one address. The client moves to a new city, hence new
address, but same ClientId. How do you setup the table/relationship so
the old records don't change to the new address?

By storing the address in the Orders table at the time the order is
created. Seems redundant but it's not - this is the "address at the
time of the order", the address in the Client table is the "address as
of today". The former is an attribute of the order; the latter is an
attribute of the client.

John W. Vinson[MVP]

 

[Allen Browne]

John has explained it well, Lar.
And that's exactly what Northwind does with its orders.

 

[larpup]

John,

In my form I have a CBODropDown to select the Client (since it was
probably entered previously.)

If I understand you correctly make the ClientName, Addr1, Addr2, City,
State, Zip part of the orders table. How do I make the SQL statement
to populate these fields? In order words, how do I obtain this
information in code? Do I take the ClientID from the Client table and
if so, how do I make field assignments. I am only dealing with on
client per order?

Beginning to feel a little dense here.

Lar

 

[John Vinson]

John,

In my form I have a CBODropDown to select the Client (since it was
probably entered previously.)

If I understand you correctly make the ClientName, Addr1, Addr2, City,
State, Zip part of the orders table. How do I make the SQL statement
to populate these fields? In order words, how do I obtain this
information in code? Do I take the ClientID from the Client table and
if so, how do I make field assignments. I am only dealing with on
client per order?

Beginning to feel a little dense here.

Well, I was terse to the point of incomprehensibility there... sorry!

You'll need VBA code to do this. There are two ways you can do it: one
would be to execute an Append query; but the better choice might be to
include all these fields in the Combo Box's RowSource query. You can
set the widths of these fields to zero in the ColumnWidths property.
In the combo's AfterUpdate event you can "push" the fields into
textboxes (which could be invisible, if you wish) bound to the address
fields:

Private Sub CBODropDown_AfterUpdate()
If Not IsNull(Me.CBODropDown) Then
Me.txtClientName = Me.CBODropDown.Column(1)
Me.txtAddr1 = Me.CBODropDown.Column(2)

<etc etc>

End If
End Sub

Note that the Column property is zero based - I'm assuming that the
first column (aka Column(0) ) is the bound ClientID.

John W. Vinson[MVP]

 

[larpup]

John,

Yes, Column(0) is ClientID

Ok.
1. My ComboBox RowSource is from the Client table, yes?
2. The AfterUpdate event takes the data from the fields in the ComboBox
RowSource (Client table query) and assign it to the txt boxes (of the
order table)... Then the client can change whatever he would like, yes?

Is my understanding correct? If so, and I think it is, you have solved
my problem. I didn't think of doing field assigment's from the
RowSource.

My idea was when the user enters in a new client, the fields (client
record) would go into the Client table. I guess I could do an append
query upon update as well, if the Client does not exist. Would this be
the best way to populate the Client table as well?

Thank you very much for your patience,

Lar

 

[John Vinson]

My idea was when the user enters in a new client, the fields (client
record) would go into the Client table. I guess I could do an append
query upon update as well, if the Client does not exist. Would this be
the best way to populate the Client table as well?

I'd suggest setting the combo's Limit to List property to True, and
using its NotInList event to pop open a form in Dialog mode to let the
user enter new client information.

John W. Vinson[MVP]

 

[larpup]

John,

I can't thank you enough... I don't always take advantage of what
Access has to offer and since I don't program on a regular basis, it's
a little difficult for me to think of this functionality.

Again, really appreciate all you and the forum have taught me.

Thx,

Lar