updating two fields with the same text

[dvdeelen]

i have a table customers with a contact-field (memo)
i have a table houses with a contact-field (memo)

if a customer visited a house, i would like to update both contact-fields with the same text.

For example customer 34 visited house 17 on the 17th of januari 2003 with manager UB.
I want to update both contact-fields with the following text:

17-1-03 UB
Customer 34 visited house 17, was not interested, too much badrooms



HOW CAN I DO THIS?

 

[Jack Cowley]

You need to normalize your tables and join them properly.

tblCustomers
CustomerID (Primary key & autonumber)
LastName
FirstName
Address
...other fields but no ContactField...

tblHouses
HouseID (Primary key & autonumber)
CustomerID (Foreign key & long integer)
Address
DateVisited
ContactField
...other fields...

hth,
Jack

 

[dvdeelen]

that is not what i mean.

in the contact field of the customers is more text, like when he first came to the office and what telefone-calls we made with him.

also in the contact field of the houses is more text.

only when a customer visits a house i want to update these two fields with the same text

For example

i had:

customer 4 (contact-field)
2-1-03 DvD
is interested in house 17, made appointement for 24-1, 17.00 h.


house 17 (contact field)
2-1-03 DvD
is interested in house 17, made appointement for 24-1, 17.00 h.

8-8-02 UB
4 bedrooms, 1 attic, can also be changed in a bedroom


at monday 27-1 i want to update both fields with this text
27-1-03 DVD
Customer 4 called, is not interested in huuse 17, it's too dark and there's no school in the neighbourhood.

 

[Jack Cowley]

You would be storing redundant data and there is no reason to do that. You can, at any time, look at the contact field of any house and see which customer(s) looked at it and you can also look at any customer and see which houses they looked at. If, for example, you had 8 customers look at one house are you going to put the same data in all 8 customers contact field as well as the contact field for the house? Now you will have stored the same data 9 times....

Take a look at this tutorial as it may help you to understand what I am suggesting.

Jack

 

[neileg]

I would have a third table to perform the link. This would consist of the id of the customer, the id of the house, your visit date, the staff member conducting the visit, your notes field, etc, etc.

This will enable you to have an unlimited number of houses per customer, and customers per house.

If a customer can only visit a house once, then you could combine the customer and house IDs to create a primary key and prevent duplicates. Don't do this if you need more than one record for the same customer and house.