Excel to Access

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

LOST - 30 Jul 2007 18:36 GMT

I am working with someone who has all her data in Excel. Each worksheet is
like a table in Access. She has been taking that data and exporting into
Word for making reports. I mentioned that maybe Access would be the way to
go. I can help her copy and paste data into Access from Excel, but I do not
know how to get all the correlated data linked. I know about Primary Keys.
Should I have created the blank tables with Primary keys and then pasted the
data in? Any help would be great.

Thank you.

Reply to this Message

Arvin Meyer [MVP] - 30 Jul 2007 19:15 GMT

Try importing the data directly into Access. There are wizards that make it
almost foolproof. You are prompted to add the Primary Key as you import the
data. Read a good book on relational databases. I like:

Database Design for Mere Mortals by Michael J. Hernandez

to learn some good database design and build your reports in Access. By
using the wizards you can build more powerful reports than even experienced
users of more specialized reporting software.

Signature

Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

>I am working with someone who has all her data in Excel. Each worksheet is
> like a table in Access. She has been taking that data and exporting into
[quoted text clipped - 9 lines]
>
> Thank you.

Reply to this Message

LOST - 30 Jul 2007 20:16 GMT

Thank you for the help. That was a really easy way to get the data in, but I
still am not sure how to get the tables to be linked to eachother.

> Try importing the data directly into Access. There are wizards that make it
> almost foolproof. You are prompted to add the Primary Key as you import the
[quoted text clipped - 18 lines]
> >
> > Thank you.

Reply to this Message

Arvin Meyer [MVP] - 30 Jul 2007 20:37 GMT

You cannot simply link tables. There must be a reason to link them. The
physical process is easy:

Tools >>> Relationships

Then drag the Primary Key in 1 table to the many side (Foreign Key) in the
other table.

Signature

Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

> Thank you for the help. That was a really easy way to get the data in,
> but I
[quoted text clipped - 30 lines]
>> >
>> > Thank you.

Reply to this Message

John W. Vinson - 30 Jul 2007 21:30 GMT

>Thank you for the help. That was a really easy way to get the data in, but I
>still am not sure how to get the tables to be linked to eachother.

What is in the tables? How - logically - should they be linked? What do you
*expect* from linking the tables?

John W. Vinson [MVP]

Reply to this Message

LOST - 30 Jul 2007 22:04 GMT

She has 6 tables: Course, Instructor, Advisor, Location, Contact, Center.

Each Course has and Instructor and each Course has a Location. Each
Location has a Center with an Advisor and Contact person. Does that make
sense?

> >Thank you for the help. That was a really easy way to get the data in, but I
> >still am not sure how to get the tables to be linked to eachother.
[quoted text clipped - 3 lines]
>
> John W. Vinson [MVP]

Reply to this Message

John W. Vinson - 30 Jul 2007 23:00 GMT

>She has 6 tables: Course, Instructor, Advisor, Location, Contact, Center.
>
>Each Course has and Instructor and each Course has a Location. Each
>Location has a Center with an Advisor and Contact person. Does that make
>sense?

Ok, you need a Primary Key in each table - CourseNo, InstructorID, AdvisorID,
etc. There may or may not already be fields in the Excel spreadsheets which
can serve this purpose; to qualify the field must be unique (only occur once
in the table), stable (not something like a person's name which is subject to
change), and (preferably) short, such as a number field.

For each one-to-many relationship you need a "Foreign Key" field as a link;
for example, the Course table should contain an AdvisorID (a Long Integer if
you use an autonumber as the primary key of the Advisors table, or matching
the primary key of the Advisors table if you already have a unique
identifier). You would use the Relationships window to specify relationships,
referential integrity enforced, so that you won't be able to assign a
nonexistant advisor to a course or the like. Just go through all your
relationships in the same manner.

John W. Vinson [MVP]

Reply to this Message

LOST - 31 Jul 2007 00:26 GMT

Thank you. I really appreciate all your help. I just have one more question
(I hope). When you say: For each one-to-many relationship you need a
"Foreign Key" field as a link. How so I do it as a link?

Here is what I have done. I have imported all the data from excel into
access (letting the wizard create my primary keys). So all of my Primary
Keys are autonumbers. At this point I have all separate tables. Then do I
go into the appropriate tables and create a field for the foreign key? For
example the Course Table needs the Instructor ID as a foreign key, do I just
go into Design View in the Course Table and create a Field named Instructor
ID? Then go to the Relationships Window and link the appropriate IDs? I
have tried this and in the tables the Foreign Key field remains blank.
Something is just not clicking for me. I know that that was more than one
question, but thank you again for all your help.

> >She has 6 tables: Course, Instructor, Advisor, Location, Contact, Center.
> >
[quoted text clipped - 18 lines]
>
> John W. Vinson [MVP]

Reply to this Message

John W. Vinson - 31 Jul 2007 00:41 GMT

>Here is what I have done. I have imported all the data from excel into
>access (letting the wizard create my primary keys). So all of my Primary
[quoted text clipped - 3 lines]
>go into Design View in the Course Table and create a Field named Instructor
>ID? Then go to the Relationships Window and link the appropriate IDs?

Yes.

> I have tried this and in the tables the Foreign Key field remains blank.

Well... of course it will remain blank. YOU need to tell Access who the
instructor is for the course; how can it automagically know who's teaching the
course!?

Perhaps you could post the actual fieldnames and brief descriptions of these
tables. Maybe the information needed to create the link already exists in the
tables, but since we can't see them, it's hard to tell!

John W. Vinson [MVP]

Reply to this Message

LOST - 31 Jul 2007 15:48 GMT

I know I am probably making you pull your hair out :)

Here are the tables:

ADVISOR:        CENTER        INSTRUCTOR
Advisor ID        Center ID        Instructor ID
Advisor FN        Center Name    Instructor FN
Advisor LN        Center Cycle    Instructor LN
        Center Area    Type
        AdvisorID                            ContactID
        LocationID

CONTACT        COURSE        LOCATION
Contact ID        Course ID        Location ID
Contact FN        Semester        Location Name
Contact LN        Course NO        Location Address
Contact Address    CRN        Location City
Contact City    Section NO        Location State
Contact State    Course Title    Location Zip
Contact Zip    Dates1        Location Phone
Contact Phone    Dates2        Location Time Zone
Contact Email    Dates3
Contact SIN    Dates4
        Dates5
        Text 1
        Text 2
        Text 3
        InstructorID
        LocationID

I really appreciate your help. Thank you.

> >Here is what I have done. I have imported all the data from excel into
> >access (letting the wizard create my primary keys). So all of my Primary
[quoted text clipped - 17 lines]
>
> John W. Vinson [MVP]

Reply to this Message

John W. Vinson - 31 Jul 2007 19:23 GMT

>I know I am probably making you pull your hair out :)
>
[quoted text clipped - 25 lines]
> InstructorID
> LocationID

Ok... several comments.

Your Center table has AdvisorID and LocationID fields. Are these filled in, or
are they null? My guess is that they're null, and that the advisor and
location information were not in the Center spreadsheet. If so, you'll
probably want to create a Form based on CENTER, with combo boxes bound to
AdvisorID and LocationID; you can then just go through all the centers
selecting the correct values for these. You should conceal the
(human-meaningless) ID values; for instance, the rowsource of the Advisor
combo might be a query

SELECT [Advisor ID], [Advisor LN] & ", " & [Advisor FN]
FROM ADVISOR
ORDER BY [Advisor LN], [Advisor FN];

Set the combo's column count to 2, and column widths to 0;1 to store the ID
while displaying the name as, e.g., "Evans, Maryanne".

Similarly, you'ld have a Course form with combos for InstructorID and
LocationID.

A couple of suggestions though.... DON'T use blanks in fieldnames, they can
cause trouble down the road and will require that you always use [Square
brackets] around the name in queries and code. They'll work if you do so but
it's an extra hassle. I'd use "Camel Case" - e.g. CourseNo, CourseTitle.
Including the table name in the field name is a matter of style; some folks do
so routinely but I'd just use ID, LocName (not Name, that's a reserved word),
City, State, Zip, Phone, TimeZone as an example. Feel free to include the
name if that works better for you!

But the biggest suggestion is in your Course table. You're "committing
spreadsheet", not surpisingly. Dates1, Dates2, etc. and Text 1/2/3 is
*incorrect design*. Whenever you have repeating fields like this you need
*ANOTHER TABLE* related one to many to this table! If a given Course has five
sessions on five dates, you should have a Sessions table with fields SessionID
(autonumber primary key), CourseID (foreign key to COURSES), SessionDate, and
any information specific to that session. (For instance, I can imagine that a
course might meet at multiple locations for different sessions, in which case
the LocationID should be in the Sessions table rather than in the Course
table). I'm guessing that Text 1/2/3 are textbooks? If so, the same logic
would apply.

John W. Vinson [MVP]

If CourseNo is

Reply to this Message