Relationships and reports

[Guest]

I have 7 tables keeping track of staff information. I have made a
relationship from table to table. How can I set my tables up so when I enter
a new staff on one table it will automatically add it to the other tables? I
thought I could fix this with the relationships, but it doesn't work.

Also, I am trying to design my own report and I am pretty handy at walking
thru a program, but keep hitting a wall. I want the report to pull up a name
from the name column in a table, but when I assign that value to the text box
control source it doesn't work. Help?

 

[tina]

that's not how relationships work, and it's also not the purpose of
relationships in a relational database. in a relational data model, you
store each piece of data only once, in only one table. you use relationships
to link the records in one table to related record(s) in another table.

suggest you read up on relational data modeling, so you'll understand how to
define and build your tables/relationships correctly. see
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html for
many links to helpful information, beginning with Starting Out and Database
Design 101 links. there are also books on relational data modeling, such as
Database Design for Mere Mortals by Michael Hernandez.

also note, don't use Lookup fields in your tables. see
http://www.mvps.org/access/lookupfields.htm for reasons why.

hth

 

[Guest]

Hi QRATRAINER,
I will provide a very basic explanation of what works for me and although I
am not an expert, I am pretty sure it satisfies the rules of normalization.

The first thing you want to think about is avoiding duplicate entries. Lets
say that your staff are trainers and each trainer has 2 classes. Then
trainer and classes need to go into different tables because for each class,
you do not want to input the trainer name (2 times) - that is what I mean by
duplicate entries.

The next thing to think about is how to link the Trainer Table with the
Classes Table. Are you familiar with a Primary Key and Foreign Key? In
Access, I create an Autonumber field in EVERY table so that each row of data
in the table has a unique number. I use the Autonumber field as my Primary
Key for that table. It will uniquely identify Trainer1 from Trainer2. Lets
call that AutoNumber field in your Trainer table pryTrainerID. That is your
Trainer table.

In the Classes table, you create a field called, for example fgnTrainerID.
This is called a Foreign Key. You join [pryTrainerID] in Trainer Table to
[fgnTrainerID] in Classes Table. In this case, it is a one-to-many
relationship.

So Trainer Table will have the following rows of data

pryTrainerID1 Joe
pryTrainerID2 Sally
pryTrainerID3 Geoff

Joe teaches Aerobics and Math. Sally teaches Science and Art. Geoff teaches
GumChewing and Physics.

The Classes Table will have rows of data like the following:

fgnTrainerID1 Aerobics
fgnTrainerID1 Math
fgnTrainerID2 Science
fgnTrainerID2 Art
fgnTrainerID3 GumChewing
fgnTrainerID3 Physics

It gets more complicated if more than one Trainer teaches the same Classes.
For example, if both Sally and Joe taught Math. That is a many-to-many
relationship and requires an additional table to link them.

That is a basic explanation of database design and relationships.

Hope this gets you started.
Capt OhNo