Getting rid of auto-numbering

[sudhirhinduja]

Hi guys,

I have 2 tables, Companies and Executives.

1 Company has many Executives (Relationship 1 - many)

CompID is PK in Company and FK in Executives. ExecID is PK in Executives.

I have 2 years of data 2003 and 2004. I need to merge the two years.

I want to get rid of auto-numbered CompID and auto-numbered ExecID.

Most companies have same CompID in both years, however the ExecID is totally different. I have now cleaned up the data. So I want to asign a new auto-number to each table.

I do not know much on VB, so any help is greatly appreciated.

Thanks a lot,

 

[DeborahP]

Copy the table but only paste the data strutuce not the data and provide a new name.

In the existing tables, remove the auto-number field and the copy and paste into the new table. This will start the autonumber over in the new table.

Hope this helps. I have done this in the past and it works for me.

Deborah

 

[ColinEssex]

I may be wrong here but something doesn't sound right

You have one table of companies with an AutoNumber (compID) as a unique identifier, you also have another table of executives with an autonumber (ExecID) as a unique identifier. You should also have the CompID as a foreign key in the Exec table as this will link the Exec to a company. The companies should only be in the companies table (lookup table) once, likewise the executives should only be in the Executives table (lookup table) only once.

One would expect the CompID to be the same for every year.

Why would you want to delete the AutoNumber? its only purpose should be to act as a unique identifier?

Col

 

[sudhirhinduja]

Deborah,

If I do that how do I link each Executive to the new table? Won't the relationship get affected?

Colin,

I plan to have another field EIN number as the primary key since that is unique. However, I need to merge the two years of data so I shall have to merge one more column of ProxDate to have a composite primary key consisting of two columns (EIN and ProxDate).
So the same company shall appear twice (once for each year).
Let me know how you look at it?

Thanks,
Sudhir.