Importing from excel and/or word - new user

[Guest]

I will be setting up a small db in access and would like to know:
a) can I import data directly from Word (table format) or should I transfer
info to Excel
b) some fields have multiple entries per record - anything special to watch
out for or to do or prepare when importing that part of the information?

TIA
Rita

 

[Guest]

Hi Rita,

a) There's no built-in way of importing directly from Word tables. With a
simple table you may be able to copy and paste into an Access datasheet, but
normally it's a matter of pasting into Excel and cleaning up the data there,
or else tidying up the table in Word, then using Convert Table to Text (with
tabs between columns), saving the result as a text file, and finally
importing that into Access.

b) I'm not sure what you mean by fields having "multiple entries per
record", but it sounds like a complication. Access is a relational database
management system, and it's fundamental to the relational model that each
field in each record contains a single value, not multiple values. Depending
on the situation, it may be a matter of importing your data as is and then
normalising it into multiple tables, or maybe restructuring it first and then
importing it.

 

[Guest]

thanks for that. regarding multiple entries per record, what I mean is for
example:
Record 1:
field 1: Name
field 2: Address line 1, etc.
field 3: City, etc.
field 4: Type of company
field 5: Clients - it is here that there would be a variable number of
clients per record. So record 1 might have 25 clients, record 2 might have
50, record 3 might have 10, etc.

I would probably set up a 2nd db for Name/Client as well as 2 or 3 other
fields that are similar to "client" such as "branch offices".

Does this help clarify my question?
Rita

 

[John Nurick]

That makes things a bit clearer - but raises more questions.

In particular, can a single Client appear in more than one record?

More precisely, is there a many-to-many relationship between "Names" (or
whatever entity is associated with a record in your existing table) and
Clients - i.e. can one Client be associated with more than one Name as
well as one Name being associated with 25 or more Clients?

Or is it a one-to-many relationship in which each Name is associated
with multiple Clients but each Client is associated with at most one
Name?

If the latter, you need a second table (this may be what you refer to as
"a 2nd db") for Clients (one record per client) with a foreign key field
to link it to the "Names" table.

If the former, the way to implement a many-to-many relationship is with
three tables, one for "Names", one for Clients and the third to join
them: each record in the third table represents the relationship between
one Name and one Client.

 

[Guest]

Thanks again.

Basically this is a db of all companies in a particular industry and all
their clients. A client's business may be split between 2 companies, but it
is quite rare and can be accomodated by adding a letter or number to the
duplicates so therefore I believe it will be a one to many relationship. I
was planning a second table. What is the best way to set up the data in
excel to import into access? (If my client insists, I may have to set up a
many to many, but I'm hoping not, unless it is easy. However, I don't think
that will be required)

There will also be 3 other fields (branch offices, divisions and contacts)
where each record may have more than one entry, but they will be all unique.
Also, each entry will have more than one bit of information. E.G., Record 1
may have 4 contacts with 4 bits of info (i.e., name, title, tel no, email).
Do I understand that I will therefore need a separate table for each "field"
in a one to many relationship as you described for clients?

I'll be loading up office pro shortly, and will then be able to actually
enter all this info and probably have a better understanding once I'm working
in it.

So if all of the above makes sense, right now I'm looking for the best way
to set up the info for the one-to-many relationships which is all in excel.
or it is now that it's been transferred from word.

Thanks again.
Rita

 

[Guest]

ignore previous message.
I think I got it.

I now will be setting up 4 more tables. Thanks for that direction.
Hopefully it won't be many-to-many.

Rita

 

[John Nurick]

If one Client can do business with more than one Company and one Company
can do business with more than one Client, you have a many to many
relationship. Full stop.

In designing a relational database it's very important to start by
getting the structure right. If you don't, you risk major complications
in future as the users or owners of the database ask for new
capabilities (as they almost always do).

The best way to approach database design is to think in terms of the
various real-world "entities" you are modelling and the relationships
between them. Usually you'll end up with one table per entity plus maybe
a number of tables for ancillary information. You seem to have at least
the following entities:

Company

Client

The Company-Client relationship (embodying the M:M
relationship betweeen Companies and Clients).

Branch office (with, presumably, a 1:M relationship
between Companies and BranchOffices)

Divisions (are these related directly to Companies,
or indirectly via BranchOffices?)

Contacts (does a Contact belong to a Company,
BranchOffice, Division or a Client?)

And that's not the end of the questions. For example,

*Is it possible for a Company also to be the Client of another company?
If so, could it be relevant? If the answer is yes, then a Client is just
a Company that has a Company-Client relationship with another Company.
You'd lose the Clients table and have the CompaniesClients table doubly
related to Companies.

*Likewise, if a Contact can belong to either a Company or a Client, this
is another reason to consider Clients as just Companies.

Once the structure is right, we can think about importing the data.

I've cross-posted this message to the Database Design newsgroup
(microsoft.public.access.tablesdbdesign) which is a better place for
this sort of discussion. You may also like to look up this article on
normalisation: http://support.microsoft.com/kb/283878/EN-US/

 

[Guest]

Thanks for all this info. have printed it out for reference but have some
comments back.

This has the following components:

- company/client is M:M

- branch office and divisions are 1:M and the only difference is that the
branch office has a different address but the same company name. Divisions
are listed by name only. Some of these divisions are also companies. not
sure yet if this information is relevant

- contacts are solely employees of the companies - no overlap.

- as for importing the data, I've cleaned the first batch of records up - so
far there are only 119 companies (with another 100-200 on their way) and i've
set up the 5 basic tables:

T1 Company - general info (119)
T2 Company / Clients (2300)
T3 Company / Branch Offices (147)
T4 Company / Divisions (196)
T5 Company / Contacts (384)

So it's a very small db and my client will look at the data in excel and
will let me know what her requirements are as priorities.

Thanks for the cross-post and the link.
Rita