Add multiple records to one field

[Ken]

table 1 has 3 fields, uniqueID (primary key no duplicates), name and
specialty.
table 2 has 3 fields, uniqueID and practiceID, practiceName. The uniqueID is
linked to the uniqueID in table 1 and there are multiple entries for a single
record in table one. I want to get all the results from all records for
uniquedID in table 2 in table 2 and insert them into the specialty field in
table 1 like 10,20,30. Hope this makes sense. How could I do this?

Thanks

 

[Michel Walsh]

Cannot do it. At least, as I understand it.

If table1.uniqueID is a primary key, you can have only one record with a
given value for it. So, no, you cannot "get all" (more than one) "records
for" (a given value of) "uniqueID from table2 " and " insert them " (more
than one) ïn table1!

That would end up with multiple records with the same uniqueID value in
table1.



Vanderghast, Access MVP

 

[Ken]

I don't want to create multiple records in table 1.

Say table 2 has 3 records for uniqeID 1 and the data for the field
practicename is 10 for the fist record, 20 for the second record and 25 for
the third record, I want to insert 10,20,25 into the field specialty in table
1 for UniqueID 1. -Thanks

 

[KARL DEWEY]

Back up your database before doing any global updates. Try this using your
table names instead of A_1 and AA_1.
UPDATE A_1 LEFT JOIN AA_1 ON A_1.UniqueID = AA_1.UniqueIDCopy SET
A_1.Speciality = [Speciality] & " " & [PracticeID] & " " & [PracticeName];

 

[Michel Walsh]

Ok, you want to concatenate all the values.

In Access 2007, there is a MFU field type, but I don't use that. Maybe there
is a simple solution using that.. thing, but it is not very good SQL, and I
doubt it can really stand the road, at least, in my applications.

If specialty field in table1 is alphanumerical, and currently filled with
NULLs, then:

UPDATE table1 INNER JOIN table2 ON table1.productID=table2.productID
SET table1.Specialty = (table1.Specialty + ", ") & "" & table2.practiceName


should do.


Note that if you run the query TWICE, you are likely to end up with "10,
25, 30, 10, 25, 30"

unless you re-set the field table1.Specialty to NULL before the second run.




Hoping it may help,
Vanderghast, Access MVP