Excel to Access

[Guest]

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.

 

[Arvin Meyer [MVP]]

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.

 

[Guest]

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.

 

[Arvin Meyer [MVP]]

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.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[John W. Vinson]

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]

 

[Guest]

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?

 

[John W. Vinson]

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]

 

[Guest]

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.

 

[John W. Vinson]

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?
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]

 

[Guest]

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.

 

[John W. Vinson]

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

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