Access 2003 Updating Table field (memo) with contents of another Table

[ajolson1964]

I Want to update a memo field in one table with several different records of another table Text field.

Here is my issue I have two tables. One is the main table with several fields one of them being a Memo field. The other table is where each record has a field with text data and another field with Yes or No data. What I want to do is have the user check the yes/no box and have the data from the text field transfer to the memo field of the main table. However if the user selects multiple records of the yes/no box I want all of them to appear in the memo box for the record in the main table.

Any suggestions, should I set this up differently? Maybe a relationship between the two. Not to sure how to set it up. Any advice would be greatly appriscated
Here is an example of what I would like


Main Table

Name Job Title Job Description
John Doe Clerical Staff Filing, Faxing, Typing.

From the other table The user would have selected the following to update the Job Description field for John Doe

Yes – Filing

No- Driver

Yes – Faxing

Yes – Typing

No – Accounting

Again any help would be appreciated.

 

[dhookom]

It would help if you provided some actual table and field names as well as information on their relationship.

If you have fields named like "Filing", "Driver", etc this is quite un-normalized. Values like Filing and Driver should be stored in fields, not as field names. I would normalize the data first with a union query and then use code to concatenate multiple child records. There is a function you could use in the FAQs of the Queries forum.

Duane
Hook'D on Access
MS Access MVP

 

[fneily]

Maybe this will help.
Fundamentals of Relational Database Design

http://r937.com/relational.html

The normalization section could be explain better, but that's the usual article referenced here.

 

[MajP]

If interested try documenting your tables using the following:
FAQ700-6905

 

[ajolson1964]

The field is normalized, as there are 2 fields within that table. One is bullion and the other titled Descriptions. Under the description the user finds files with entries like, Filing, Driver, Faxing, Typing ect ect.

 

[dhookom]

Why can't you provide your actual table and field names? This would remove our concerns regarding normalization which seem to be a common concern of those of us that are trying to provide some assistance.

If "there are 2 fields within that table" how can there be any relationship to your main table?

Assuming you have multiple records in "the other table" related to one record in the "main table" then there is a function at faq701-4233 which might meet your needs.

Duane
Hook'D on Access
MS Access MVP