Update multible records from one table to another

[Guest]

I am working w\two tables in access. the 1st table is a cross reference
table called OEM x-reference. It contains a part number with the
corresponding OEM part number. The second table is a parts table. I'd like
to run an update query if possible to combine all OEM part numbers into the
parts table under 1 field.

example:
Table1: OEM x-reference
Part Number OEM part number
100-008 30727
100-008 AM30900
100-012 17210-ZE3-505
100-012 023575

Table2: Parts
Part Number OEM Part Numbers
100-008 nul
100-012 nul

Desired results
Part Number OEM Part Numbers
100-008 30727; AM30900
100-012 17210-ZE3-505; 023575

Let me know if more info is needed.

 

[John Vinson]

On Mon, 9 Jan 2006 13:27:02 -0800, Glen B <Glen

I am working w\two tables in access. the 1st table is a cross reference
table called OEM x-reference. It contains a part number with the
corresponding OEM part number. The second table is a parts table. I'd like
to run an update query if possible to combine all OEM part numbers into the
parts table under 1 field.

example:
Table1: OEM x-reference
Part Number OEM part number
100-008 30727
100-008 AM30900
100-012 17210-ZE3-505
100-012 023575

Table2: Parts
Part Number OEM Part Numbers
100-008 nul
100-012 nul

Desired results
Part Number OEM Part Numbers
100-008 30727; AM30900
100-012 17210-ZE3-505; 023575

Let me know if more info is needed.

You can do this with some VBA code:

http://www.mvps.org/access/modules/mdl0004.htm

However, I really must question the wisdom of storing this redundant,
non-atomic, unnormalized field! What will you do with it? It's not
going to be convenient for searching, and you can use the module to
generate this string as needed for reporting purposes.

John W. Vinson[MVP]

 

[Guest]

On Mon, 9 Jan 2006 13:27:02 -0800, Glen B <Glen


You can do this with some VBA code:

http://www.mvps.org/access/modules/mdl0004.htm

However, I really must question the wisdom of storing this redundant,
non-atomic, unnormalized field! What will you do with it? It's not
going to be convenient for searching, and you can use the module to
generate this string as needed for reporting purposes.

John W. Vinson[MVP]

John,

Thanks for the reply. The reson for this is because I'm creating an
ecommerce website with a 3rd party ecommerce package. I am using the default
..ASP files given to display the parts information. I thought it would be
best to display the OEM cross reference in 1 field without having to
maniplulate the .asp files. I'm only using access to create this field
nothing more.

 

[John Vinson]

I'm only using access to create this field
nothing more.

OK, the VBA in the link I posted should do the trick. Post back if
you're having trouble with it.

John W. Vinson[MVP]