Split table containing data..possible in this case?

D

Daniel

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
 
K

Klatuu

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 spreadsheet 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.
 
D

Daniel

Thank you for your reply. This is indeed defficult. I could not save changes
to the joined spreadsheet, 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 spreadsheet 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 spreadsheet 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?
 
K

Klatuu

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

Daniel

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 sheet 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
 
K

Klatuu

Your tables are not defined as I had expected.
Table C should be
CONTACT_ID (Primary Key - Autonumber)
FirstName
Name
GroupCode (Foreign Key to Group Table)
SectionCode (Foreign Key to Section Table)
Function
Phone1
Phone2
Email1
Email2
FaxNo
RoomNo

Table A
ADDRESS_ID (Primary Key - Autonumber)
CONTACT_ID (Foreign Key - Long Integer) - Will contain the primary key of the
contact for
this address
Company
Location
Street
PostalCode
City
Country

GroupCode Table
GroupCode (Primary Key)
GroupDescription

SectionCode Table
SectionCode (Primary Key)
Section Description

Now, link your spreadsheet as a table.

Create an append query that will append records to the GroupCode table.
using the spreadsheet field for Group Code.

Do the same for the SectionCodeTable.

Create an append query that will join the spreadsheet, the section code
table, and the groupcode table to append records to table C. Include the
fields to complete a record for table C.

Create an append query that will join the spreadsheet and table C. You will
want this to append to table A. For this pass, use the fields that end in 1
(City1, Street1, etc). Include the primary key of table C to append to the
foreign key in table A. This will add the first group of addresses to table A
and by adding the primary key from table C will associate the adress to the
contact.

Create an append query like the one above, but use the fields that end in 2.
 
D

Daniel

Interesting, I will need some time to go through this procedure.
Regards,
Daniel
 
D

Daniel

The Tables are now all related as you mentioned (all relations are one-to-one
at this point) but I can't seem to make this join:
Create an append query that will join the spreadsheet, the section code
table, and the groupcode table to append records to table C. Include the
fields to complete a record for table C.
Click to expand...
I added C S and G (S=tblSectionCode, G=tblGroupsCode) to an append query and
tried many possible join types but I get different errors each time or wrong
selections. G and S now contain each a sample of 266 records. The ID's in
both tbls are the same.
 
D

Daniel

Thank you for your efforts. I was finally able to separate the table,
although I could not add the Group and the Section Code. But this is not a
big problem, I will try again for this one, otherwise it must be typed over!
I added to the Address table a Yes/No field; this way one can easily choose
whether both addresses or only one of them should be included. Thank you..

Regards,
Daniel A


Regards,
Daniel A
 
Top