Appending Records To Different Tables

All-

I have two tables, A(Company) and B(Owner). I want to import the excel sheet into a temp table. Second I want to write two append queries that will append records to the company table and a query to append records to the owner table the relationship is one to many. Both tables are set for auto numbering. My problem is that when I do the append and view the data through my form I'm not getting matching records.

Example

One row in the Excel Sheet:
company, Industry, contact, role

Table 'Company'
Company, industry

Table 'Owner'
Contact, role


So in the Company table I have a 'Owner ID' Foreign key. How do I get the keys to match up?

re: Appending Records To Different Tables

Are there any articles that someone can share to point me in the right directions?

-Dev1

re: Appending Records To Different Tables

Hi Dev1. In your 'Owner' table you will need to include the Owner ID field as the primary key. It is not mentioned at present, so you will not be able to create a 1-many relationship between owner and company.

There is an article on database normalisation and table structures in the Howto section of the forum (available at this link).

There is also a more technical article on database normalisation at Wikipedia, linked here.

-Stewart

re: Appending Records To Different Tables

Thank you but yes there is a Owner ID! I understand normilization. I need assistance on my original posting.

Thank you

re: Appending Records To Different Tables

Hi. If there is an Owner ID in the Owner table you have not told us about it - please review post #1 to see if you really did mention it at all! You have said there is an Owner ID as a foreign key for the Company table. Your Excel field list does not mention Owner ID at all.

We can only go by what you tell us - no more, no less.

Could you post some sample data from your two tables so we can get an idea of what is in them? As you are familiar with normalisation you must have primary and foreign key values set for these tables, so I am not at all clear why you are having any difficulty joining them.

-Stewart

re: Appending Records To Different Tables

Thank you for your assistence, but I was able to get the answer from another site.

-Dev1

re: Appending Records To Different Tables

It was implied that the OwnerID was a column in the the table (autonumbering). I didn't realize that I had to be so explicit. Usually that is implied!

re: Appending Records To Different Tables

Hi Dev1. You know your data, but we don't, and what is an implication to you is missing data to us that makes answering your post (and solving what is after all a problem that you are seeking a solution to!) more difficult.

Here is an example of how to post table MetaData :

Table Name=tblStudent
On post #1, it would be helpful to see some sample data. You have autonumber primary keys on both tables. Presumably the Owner ID foreign key in table Company is a Long Integer value (it cannot be an autonumber, as it would generate different key values that are not the same as the true primary key autonumber in your Owner table.

If there are no key values listed in your composite Excel table then the values appended to your Owner table will not have any direct relation to the Company table, as the autonumber is created for the first time as you append the rows. Setting the value of the Owner ID foreign key in your Company table from an autonumber in another table created only at append time is difficult; you would have to do some form of lookup of the value assigned based on performing some kind of transform of the data in your Owner table to come up with an alternate candidate key.

As the autonumbers in your Access tables are added at the append stage they are not any part of the data transferred. They are not candidate keys for your data, in the sense of identifying what makes your row data unique. Instead of using autonumbers after the event look for a candidate key from within your data, make that the primary key of your Owner table, then carry that forward as your foreign key in the Company table and you WILL be able to match the two tables on the key value. You are going to have to identify an alternate candidate key anyway to be able to lookup the autonumbers generated by the append as mentioned above.

-Stewart