RE: Split table containing data..possible in this case?

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Hallo Klatuu,

Tbl C: FirstName, Name, GroupCode, SectionCode, Function, Phone1, Phone2,
Email1, Email2, FaxNo, RoomNo
Tbl A: Company1, Location, Streat1, PostalCode1, City1, Country, Company2,
Streat2, PostalCode2, City2
Also have 2 tbls for GroupCode and SectionCode
The Excel *** contains all above fields and has the same column names.

Relations designed (empty) tables were:

Table C Table A Relation
IDC has a Primary Key IDC no Primary Key one-to-many
IDA

Enforce referential integrity: checked
Cascade update related fields: checked
Cascade delete related fields: checked

Relations created tables are:

Table C Table A Relation
IDC has a Primary Key IDA one-to-many
IDA

Enforce referential integrity: checked
Cascade update related fields: can NOT be checked
Cascade delete related fields: checked

....
Daniel

"Klatuu" wrote:

I don't think you really need C. Can you describe the layout of the
spread*** and your two tables, Contacts and Addresses?

"Daniel" wrote:

Thank you for your reply. This is indeed defficult. I could not save changes
to the joined spread***, apparently not allowed in Access; then I decided
to import the contacts to the .mdb (which means a new table). I tried the
method of Append query, I could not succeed but your idea led me to this,
sorry a bit long story:

First imported the spread*** and given a primary key

Ignored the 2 already existing empty tables that I had: contacts and address

Created 2 exact tables from the imported spread*** A & C with a query

Joined both LastName (name) fields in C & A one-to-one

Opened tbl C and given a primary key to ID

Using Lookup Wizard in C to the ID in Table A (ID in A has no primary key)

Relations now:
ID from Tbl* A_1 ( *in relations) is linked to ID1 in tbl C
Name in tbl C to Name in tbl A

Now deleted the Name-Name relation
Next: Edit the linked ID in A_1 to ID1 in tbl C
In the window Edit Relationships Clicked Create New
Joined C ID And A ID clicked OK
Enforce Referential Integrity
Click Create
Click Create: Accepted
Deleted the relation ID in A_1 To ID1 in C
SAVE

What a situation?... -:)
To check the result I used Select query. Because I did not delete anything
from both tables in the beginning, the data seems to be correct. If this
makes sense at all.. the result now is 2 tables: Tbl C has one ID field
linked to tbl A ID, one-to-many. Is this still correct?


"Klatuu" wrote:

Yes, your tables are correct if there can be more than one address for a
contact.

As to loading the data from the spreadsheets, it doesn't really matter
whether you merge them or not. Populating your Access tables from them may
be a little tricky. I would suggest one append query to pull the columns
from the spreadsheets you need for the contacts. Then you will need another
append query that will pull the columns needed for the address. For this,
you will have to join the contacts table with the spread*** data to match
the addresses to the contacts and include the contact's primary key in the
address records.

To do this, it will probably be best to link to the spreadsheets and use
them as tables in your queries.

"Daniel" wrote:

Hallo,

I am trying to set up a database for the contacts of our company (about
2000). I created 2 tables one for the contacts and the other one for their
addresses; I separated them because each contact has 2 addresses.
'One-to-many relationship'. (I am not sure of this is a correct way either).
These tables contain no data yet.

I receive the data in several Excel sheets. My thought is to merge all these
sheets first then import them into the database. My question is how can I
split the data after I have imported them? I have no clue how to do this. Am
I going wrong about it...any help?

Thanks in advance
Daniel A
.

Quantcast