linking tables

  • Thread starter Thread starter dclem
  • Start date Start date
D

dclem

I currently work in excel in multiple spreadsheets and we are looking at
moving to a database program. I know a little bit about access but not much.
Is there a way to link tables together that contain the same information,
where I only have to input the information in one table and it will show up
in the other table?
 
Hi

Access is different from excel in many ways. The most obvious is that way
data is stored and who it is linked together.

Say you have a table that contains details of a student
StudentID
Student1stName
Student2ndName
StudentAddress1
StudentAddress2
StudentAddress3
StudentAddressPostCode
StudentDateOfBirth
etc
etc

And you have a table contain details of class's
ClassID
ClassSubject
etc
etc

In excel you would have all this date in cells that refer to each other via
furmulas and you could update the Student-Class relationship if you wanted
and this would update each cell containing these details.

In access you only store the details "once" and "only" once and you link the
tables together so that you can reference each table with another.

So (in the case of students and class's)
You would have the StudentID linked to a field in the Class table (in a
field formated the same)
So you would have in your class table
ClassID
StudentID
ClassSubject
etc
etc

This would work fine if each student / Class was only one-to-one (meaning
each student only ever took one class

But in reality each class can have many students and each student can take
many class's (many-to-many)

So you need so way to link them all together
You create another (linking) table like this
TableNameID
StudentID
ClassID

Now as some class's are the same subject but they have a number of repeats
(there can be a mths class at 1pm and another at 2pm with different students
in each) you could add to this table the specific details of this Table.
TableNameID
StudentID
ClassID
Time
Location
TeacherID (note you need another table for teachers)
etc
etc

This way Student1 can do a class at 1pm and Student2 can take the same class
at 2pm

The main difference (from my point of view) is the end result. With excel
you add data and then get it to do something. With access it is the other
way around. You work out basically what you want to application to do then
you create the tables, querys, form, reports, etc to get it to do this for
you.

So you need to work out from your excel books which "bits" of data will go
into each table. It may be an idea to make a copy of your workbook and use
the access wizard to import the data and "see what happens".

After you have you tables create then post back with lots more question and
I am sure someone will be able to answer most things.

Oh yes there are many "laren to use access" website on the web. It may be a
good idea to have a look at these before jumping right in.

Good luck
 
May I suggest that you may be a little more help if you ran your answers
though a spell checker prior to pressing the send button.

??
 
Scusate Johnathon, ma il mio Inglese fa schiffo, percio se non ci siamo
capiti, mi mandate un messagio e provero di spiegarmi meglio.
 
May I suggest that you may be a little more help if you ran your answers
though a spell checker prior to pressing the send button.
Click to expand...

I'm sorry that you're disappointed with the service here; I'll see to it that
your admission fee is promptly refunded.
 
dclem said:
I currently work in excel in multiple spreadsheets and we are looking at
moving to a database program. I know a little bit about access but not much.
Is there a way to link tables together that contain the same information,
where I only have to input the information in one table and it will show up
in the other table?
Click to expand...

It's quite a challenge to answer this concisely!

In a way, yes. In a relational database you (classically) store
information just once. There is an art ("normalisation") to dividing
information up cleanly into a number of tables. Records in tables are
uniquely identified by a range of codes ("Primary Key") and tables can
be linked together by storing the primary keys of records from one table
as "foreign keys" in another. You then write "queries" to extract the
linked information you need - these queries, when run, look a lot like
tables themselves.

Access builds upon this by providing forms and reports, and an
underlying programming system (Visual Basic for Applications). It's a
very powerful system and well worth learning - you won't go back to
spreadsheets for storing information.

It's quite a long and steep learning curve. There are plenty of good
books - I liked the Access 2003 bible (haven't got round to installing
my copy of 2007 yet). You might also like to try the very good online
training at lyda.com - see
http://movielibrary.lynda.com/html/modPage.asp?ID=98
or
http://movielibrary.lynda.com/html/modPage.asp?ID=481
- the first few chapters are free to view, and will get you started nicely.

Phil, London
 
Back
Top