Table stuctures in Access

[Joshmoshoshkosh]

I am creating a database for my company that holds different seminars, often done by different people. those are my 2 variables, but also, each seminar is rated with a sheet, that rates the seminar in various ways (1 to 5) as well as the presenter. how can i relate all of this data in Access, how many tables should i be using, and how can i set it up so i can take averages of the presenter's ratings, as well as take avearges of the specific seminar's ratings? I kind of understand how to query things, but the basic setup of the database is my problem. Right now i have a Presenter Table, which only has a name and a presenter ID#, a Seminar table, which has only a seminar name and seminar id #, and an evaluations table, which has an Evaluation id#, as well as all of the evaluations for both the presenter and the seminar. Is this set up correctly, do you need a more specific example? any help i can get would be greatly appreciated.
-joshmoshoshkosh

[tdw]

I am creating a database for my company that holds different seminars, often done by different people. those are my 2 variables, but also, each seminar is rated with a sheet, that rates the seminar in various ways (1 to 5) as well as the presenter. how can i relate all of this data in Access, how many tables should i be using, and how can i set it up so i can take averages of the presenter's ratings, as well as take avearges of the specific seminar's ratings? I kind of understand how to query things, but the basic setup of the database is my problem. Right now i have a Presenter Table, which only has a name and a presenter ID#, a Seminar table, which has only a seminar name and seminar id #, and an evaluations table, which has an Evaluation id#, as well as all of the evaluations for both the presenter and the seminar. Is this set up correctly, do you need a more specific example? any help i can get would be greatly appreciated.
-joshmoshoshkosh

Would I be correct in understanding that any particular presenter may be associated with more than one seminar, and vise versa? If so I would have the evaluation field as a third field in the Seminar and Presenter tables.

I would only have two fields in the Evaluations table. For example:
Field one would be called EvaluationID and be a number or autonumber field, and field two would be called EvalDescription (or something like that).
If the EvaluationID field has the number 1, the description might be "Excellent", or for 2, "Pretty good", for 3, "Just okay" etc.

Then in the Evaluation fields in the Seminar and Presenter tables I would just use 1, 2, or 3 etc. Then you can create a form that will display the EvalDescription (i.e. "Excellent" instead of "1") for the seminar and/or presenter. You can add a field to the form that calculates the average evaluation based on the numbers that are in the Evaluation field of your Seminar and/or Presenter tables.

If you need more detailed instructions, please let me know.

[MMcCarthy]

Have a look at this tutorial

Database Normalisation

[Joshmoshoshkosh]

Would I be correct in understanding that any particular presenter may be associated with more than one seminar, and vise versa? If so I would have the evaluation field as a third field in the Seminar and Presenter tables.

I would only have two fields in the Evaluations table. For example:
Field one would be called EvaluationID and be a number or autonumber field, and field two would be called EvalDescription (or something like that).
If the EvaluationID field has the number 1, the description might be "Excellent", or for 2, "Pretty good", for 3, "Just okay" etc.

Then in the Evaluation fields in the Seminar and Presenter tables I would just use 1, 2, or 3 etc. Then you can create a form that will display the EvalDescription (i.e. "Excellent" instead of "1") for the seminar and/or presenter. You can add a field to the form that calculates the average evaluation based on the numbers that are in the Evaluation field of your Seminar and/or Presenter tables.

If you need more detailed instructions, please let me know.

A bit more help would be great... here's a more detailed description.
Say i have this seminar... ABC... and ABC was done by Joe. Abc has a evaluation sheet, with numbers 1 to 5*(bad to excellent) rating 6 different aspects of the Abc seminar, as well as 4 spots to rate Joe. Others could be doing this seminar as well, and joe could be doing other seminars including Abc. (i hope that makes sense).. My problem is, i want to be able to add these evaluation sheets as i go, but i'm having problems normalizing all of it. I read your tutorial, but i'm not sure if i should have a separate table for each question? ... Right now i have a table for Presenters with a Presenter ID (auto #, primary), First name, and Last name. Then I have a table for Seminars, which is only seminar id(auto #, primary) and seminar description. My third table is an evaluation table. This has an Evaluation ID (auto #, primary) as well as a spot for each one of the 10 questions. THis spot is only a number field, but how can i make this so all of these are related, and how can i get it so i can look at ABC's rating averages, Joe's ratings, etc... i'm just having problems getting them all to work cohesively, i think i understand reports, but im just having problems organizing and relating the data. Do i need the presenter ID in both the Presenter table as well as in the Evaluation table? And same with the Seminar ID, does that need to also be part of the evaluation table?
and to top this off, sometimes i get sheets, that are just summaries of a specific person at a specific seminar, for example: Mary at seminar XYZ has a totals sheet with 8 5's(excellent) 13 4's(above average) 4 3's(average).... and this is just the data for one question.... is there going to be problems with sheets like this? I don't know if im making this whole project harder than it should be, or if i'm just not relating the data correctly, or maybe i'm approaching it all wrong. Any help would be greatly appreicated. Thanks so much!!!
-Joshmoshoshkosh

[MMcCarthy]

If I understand you correctly ...

A presenter (Joe) can present more than one seminar and a seminar (ABC) can have more than one presenter. This is a many to many relationship so a join table is needed.

tblPresenter
PresenterID (Primary Key, AutoNumber)
PresenterName

tblSeminar
SeminarID (Primary Key, AutoNumber)
SeminarName

tblPresentedSeminar (Join table)
PresenterID (Composite Primary Key, Foreign key referencing tblPresenter)
SeminarID (Composite Primary Key, Foreign key referencing tblSiminar)

Now is the data is relevant to the seminar regardless of who presented it then you add the field to tblSeminar.

If the data is relevant to the presenter regardless of the seminar then you add the field to tblPresenter

Any data that is specifically relevant Joe's presentation of ABC seminar would be a field in tblPresentedSeminar

You don't have to add all fields to the tables, if you follow the normalisation rules then you can add relevant tables for the data and just add a foreign key to the table.

[MMcCarthy]

Now presuming evaluations are relevant to Joes presentation of the ABC seminar then you can have a foreign key in tblPresentedSeminar called EvaluationID but it would not be part of the composite primary key. This foreign key would reference the following table.

tblEvaluation
EvaluationID (Primary Key, Autonumber)
Question1
Question2
>
>
>
Question10

However, if there is more than one evaluation per seminar then it is different.

Don't put the EvaluationID in tblPresentedSeminar

If the evaluation is based on the seminar then add a foreign key SeminarID to tblEvaluation and if the evaluation is based on the presenter then add a foreign key PresenterID to tblEvaluation. If it is tied to both then add both foreign keys.

Getting the idea ?

[Joshmoshoshkosh]

Yes there can be many evaluation sheets for any given seminar given by any person.... anyone at the seminar can fill out an evaluation, so really there could be hundreds for each person, each time they do a seminar, but the sheets evaluate the seminar as well as the person doing the seminar at the time... that's why i'm having so many problems...And also, what's different about doing a Join table, compared to a regular table, can i just enter the same names that i had for my other fields, and still select autonumber for the data type? Thanks for all the help, i'm learning a lot from you!

Now presuming evaluations are relevant to Joes presentation of the ABC seminar then you can have a foreign key in tblPresentedSeminar called EvaluationID but it would not be part of the composite primary key. This foreign key would reference the following table.

tblEvaluation
EvaluationID (Primary Key, Autonumber)
Question1
Question2
>
>
>
Question10

However, if there is more than one evaluation per seminar then it is different.

Don't put the EvaluationID in tblPresentedSeminar

If the evaluation is based on the seminar then add a foreign key SeminarID to tblEvaluation and if the evaluation is based on the presenter then add a foreign key PresenterID to tblEvaluation. If it is tied to both then add both foreign keys.

Getting the idea ?

[Joshmoshoshkosh]

Yes there can be many evaluation sheets for any given seminar given by any person.... anyone at the seminar can fill out an evaluation, so really there could be hundreds for each person, each time they do a seminar, but the sheets evaluate the seminar as well as the person doing the seminar at the time... that's why i'm having so many problems...And also, what's different about doing a Join table, compared to a regular table, can i just enter the same names that i had for my other fields, and still select autonumber for the data type? Thanks for all the help, i'm learning a lot from you!

this is what an eval sheet looksl like:
Seminar Name: ABC

Seminar ratings: Excellent Poor
1) Overall rating of the seminar [ ] [ ] [ ] [ ] [ ]
(above are just my 1-5 checkboxes, rated excellent to poor)***
Excellent Poor
2) Organization of the content [ ] [ ] [ ] [ ] [ ]
Excellent Poor
3) Ability to apply information [ ] [ ] [ ] [ ] [ ]
Excellent Poor
4) Effectiveness of Visuals [ ] [ ] [ ] [ ] [ ]
Excellent Poor
5) Before seminar knowledge [ ] [ ] [ ] [ ] [ ]
Excellent Poor
6) After seminar knowledge [ ] [ ] [ ] [ ] [ ]


Presenter Name: Joe
Presenter Ratings:
Excellent Poor
7) Interest level [ ] [ ] [ ] [ ] [ ]
Excellent Poor
8) Demonstration of knowledge[ ] [ ] [ ] [ ] [ ]
Excellent Poor
9) Communication skills [ ] [ ] [ ] [ ] [ ]
Excellent Poor
10) Relevant Examples [ ] [ ] [ ] [ ] [ ]
Excellent Poor


so for any time someoene presents, each audience member gets one of these, and we're trying to make sense of it using a database. Like for instance, how well did Joe do on this project, or Sue's numbers for project XYZ are better than Joe's, or saying to a client, "our seminar ABC has been getting 4's and 5's, it's one of our best seminars" etc.... so we want to be able to total the number of 1's 2's 3's 4's and 5's for each area and be able to call them up for easy reference. THanks again for everything I really appreciate it!

[MMcCarthy]

Yes there can be many evaluation sheets for any given seminar given by any person.... anyone at the seminar can fill out an evaluation, so really there could be hundreds for each person, each time they do a seminar, but the sheets evaluate the seminar as well as the person doing the seminar at the time... that's why i'm having so many problems...

tblEvaluation
EvaluationID (Primary Key, Autonumber)
SeminarID (Foreign key, referencing tblSeminar)
PresenterID (Foreign key, referencing tblPresenter)
Question1 (Number, Integer - 1 to 5)
Question2
>
>
>
Question10

This way each evaluation is tied to the seminar and to the Presenter. So if for example you wanted to retrieve all the evaluations for Joe from the ABC seminar then:

SELECT tblEvaluation.*
FROM ((tblEvaluation INNER JOIN tblPresenter
ON tblEvaluation.PresenterID = tblPresenter.PresenterID)
INNER JOIN tblSeminar
ON tblEvaluation.SeminarID=tblSeminar.SeminarID)
WHERE tblPresenter.PresenterName LIKE 'Joe'
AND tblSeminar.SeminarName LIKE 'ABC';
 

And also, what's different about doing a Join table, compared to a regular table, can i just enter the same names that i had for my other fields, and still select autonumber for the data type? Thanks for all the help, i'm learning a lot from you!

tblPresenter
PresenterID (Primary Key, AutoNumber)
PresenterName

tblSeminar
SeminarID (Primary Key, AutoNumber)
SeminarName

tblPresentedSeminar (Join table)
PresenterID (Composite Primary Key, Foreign key referencing tblPresenter)
SeminarID (Composite Primary Key, Foreign key referencing tblSiminar)

tblPresentedSeminar is the Join table. It is simply there to break the many to many relationship between tblPresenter and tblSeminar.

You don't use autonumbers, Your primary key in this case is made up of two foreign keys which reference the primary keys of the two tables being joined. You set the datatype to Number, Long Integer.

Mary

[Joshmoshoshkosh]

How do i set a "composite primary key?" it wouldn't let me set multiple primary keys.
-Josh

tblEvaluation
EvaluationID (Primary Key, Autonumber)
SeminarID (Foreign key, referencing tblSeminar)
PresenterID (Foreign key, referencing tblPresenter)
Question1 (Number, Integer - 1 to 5)
Question2
>
>
>
Question10

This way each evaluation is tied to the seminar and to the Presenter. So if for example you wanted to retrieve all the evaluations for Joe from the ABC seminar then:

SELECT tblEvaluation.*
FROM ((tblEvaluation INNER JOIN tblPresenter
ON tblEvaluation.PresenterID = tblPresenter.PresenterID)
INNER JOIN tblSeminar
ON tblEvaluation.SeminarID=tblSeminar.SeminarID)
WHERE tblPresenter.PresenterName LIKE 'Joe'
AND tblSeminar.SeminarName LIKE 'ABC';
 

tblPresenter
PresenterID (Primary Key, AutoNumber)
PresenterName

tblSeminar
SeminarID (Primary Key, AutoNumber)
SeminarName

tblPresentedSeminar (Join table)
PresenterID (Composite Primary Key, Foreign key referencing tblPresenter)
SeminarID (Composite Primary Key, Foreign key referencing tblSiminar)

tblPresentedSeminar is the Join table. It is simply there to break the many to many relationship between tblPresenter and tblSeminar.

You don't use autonumbers, Your primary key in this case is made up of two foreign keys which reference the primary keys of the two tables being joined. You set the datatype to Number, Long Integer.

Mary

[Joshmoshoshkosh]

Do i do a foreign key just by linking the presenter ID from tblPresenter to presenter ID in the Evaluation table in the relationships window? Also, where would I implement taht SQL code, or could i do the same type of commands with the report wizard? Sorry for all the questions, i really appreciate all of the help!
-Josh

How do i set a "composite primary key?" it wouldn't let me set multiple primary keys.
-Josh

[MMcCarthy]

How do i set a "composite primary key?" it wouldn't let me set multiple primary keys.
-Josh

It won't let you select each individually but if you select both of them together and then set the primary key

[MMcCarthy]

Do i do a foreign key just by linking the presenter ID from tblPresenter to presenter ID in the Evaluation table in the relationships window?

To make a foreign key relationship.

Firstly, in tblPresentedSeminar go to the fields PresenterID and SeminarID and set the Indexed Property to Yes (Duplicates OK). It may already be set if you have set the composite primary key.

Then, open the relationships window and create a relationship between PresenterID in tblPresenter and PresenterID in tblPresented Seminar. Now double click on the line to open the relationship properties window. Tick all three check boxes. Now do the same for SeminarID.

Also, where would I implement taht SQL code,

If you mean the last query I gave you then open a new query in design view (don't bother adding any tables) and then go up and change the view to sql view. Just copy in the code and save.

or could i do the same type of commands with the report wizard? Sorry for all the questions, i really appreciate all of the help!
-Josh

I'm not sure what you mean by this but lets leave reports alone for the moment. It's imporant to get the structure right first.