Move data between tables with INSERT-LOOP?

[Guest]

Greetings
I need to move all the contact information (contained in 5 fields- call it the address table) into the table (call it the main table) that contains all the other associated information. Tables are related via the "vendor_ID" key field.

There are about 7000 records.

I tried the attached code- with no luck- am I in the ballpark?

Thanks!

newportri

[Dan_Bracuk]

Could you elaborate on the statement, "No Luck"?

Also, you say the tables are related but your queries have different datasources.

If they really are in the same database, and the tables really are joined, why are you even bothering to copy the data?

[Guest]

Sorry- yes the tables are in the same DB because I imported them together.

I am trying to clean this DB up for eventual porting to SQL Server (it is in Access now) - I saw no good reason why the designer used 2 separate tables- one with most info on each vendor, but split the addresses off in another table?

I guess there are lumpers and splitters- but I can't see the point of inserting/updating/deleting form 2 tables vs 1?

ERROR: The field 'Vendor1.vendor_ID' cannot contain a Null value because the Required property for this field is set to True. Enter a value in this field.

Thanks for your quick response.

[Dan_Bracuk]

If they are the same db, you don't need to loop. The syntax is

insert into sometable
(field1, field2, etc)
select stuff
from some_other_tables

Your specific error is that you have a field that was declared not null and are not providing a value.

[The_ScareCrow]

quote:

---

I saw no good reason why the designer used 2 separate tables

---

Maybe a vendor can have multiple addresses ??

Ken

[Guest]

Ken & Dan:

Thanks for all your help with this.

I thought that might be why (vendor can have multiple addresses) but that's not the case here.

I think the problem is that I'm not used to seeing 5 or 6 junction tables when 1 or 2 would do- guess that makes me a "lumper".

The DB itself is filled with double entries and other data input errors made over the last several years, ending up with junction tables with 70,000 records, etc.- just am not prepared to re-invent the DB structure- will work around the issues.

Thanks again-

newportri