Update field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi All

in two linked tables, one to many, I want to update a memo field in the
main tbl to the values found in the related records from the linked table
Table1 has Colourname & Memofield
Table2 has: two columns weight / ref no
2 1
3 380
3.6 126
The updated memo field must look like: Mix 2g ref1 + 3g ref380 + 3.6g ref126

How can I do this
 
Elly,

I can't see how these two tables are related? So it is difficult to
give specific help. But I would eliminate the Memofield from Table1. I
think it would be best to make a User-Defined Function, which you can
use to generate your "Mix x+y+z" text whenever you need it, rather than
trying to duplicate it into a table field.
 
I agree with Steve, you shouldn't store duplicate data (once in the memo
field and once in table2).
However, you have touched on a common issue facing the developer: data
stored as a set of rows needs to be presented as a single row with multiple
columns (or formated to fit into one column of the 'parent table' -- your
case)

In the case at hand we might have the following talbles (just guessing)

Table1
ColorKey (primaryKey) ColorDescription
1 Really Pink
2 Dull Red

Table2
refID (primaryKey) ColorKey (ForeignKey) Ref Grams
1 1 1
2
2 1 380
3
3 1 126
3.6
4 2 1
10
5 2 99
1
6 2 500
6

Desired Output:

1 Really Pink Mix: 2g Ref1 + 3g Ref380 + 3.6g Ref 126
2 Dull Red Mix: 10g Ref1 + 1 g Ref99 + 6g Ref500

This could become even more interesting if we do not alway have three and
only three colors to mix eg.
Table 2 (new row)
7 2 700 2

Now we want:
2 Dull Red Mix: 10g Ref1 + 1 g Ref99 + 6g Ref500 + 2g Ref700

Using a VBA function as Steve Suggests is the 'easy way' (if you are familar
with VBA)

Function ReturnMix(ColorKey) as string
'air code'
build and open a querydef to:
find the rows containing the color key
step through the rows to:
build the desired string
when done return the string
close the querydef
End Function

For those that are "VBA challenged" this could be somewhat more difficult
and they will have to resort to a set of queries to get the information.
(My decision when faced with this was to learn more about VBA rather
than trying to deal with the complexity of the queries, so I defer to
someone with more experience to provide help in how to build the required
queries -- I just know 'it can be done')

Ed Warren
 
Thanks guys

Duane I used the generic concatenate function - this is what I needed - no
need for a memo field anymore! Thanks for your help
 
Back
Top