Can I have the same field name in two tables

[Sarah]

I have 4 tables, courses, (name) students( avg. grade, % passed), disciplines
(names) and profiles(names). I want to display avg. grade, and % passed by
profile on the top (columns) and course grouped by discipline on the left in
rows. How do I do it, Please.

 

[Jeff Boyce]

Sarah

Are you storing (or trying to store) calculated values in your table(s)? If
so, reconsider.

Use a query to calculate values "on the fly". That way, you don't need to
worry about data integrity.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Sarah]

Hi Jeff,
I am using the following info., some of which has calculated values: course
numbers , discipline names, profile names, # students registerd, avg, grade
and % passed. I want to display the data ( # students registerd, avg, grade
and % passed), by profile ont the top, and by course and discipline on the
left side. I am not sure which tables to use. I know how to create the
relationships once I figure out the tables. Do I need to repeat the course #
in each table? Help please I am really confused.
Sarah

 

[Jeff Boyce]

Sarah

I'm also confused, and having trouble visualizing what you are able to look
at.

Please post a description of your table structure more along the lines of
the following example (an "enrollment" db):

tblClass
ClassID
Class Title

tblPerson
PersonID
LName
FName

trelEnrollment
EnrollmentID
PersonID
ClassID
EnrollmentDate

Thanks

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Sarah]

Hi Jeff,
The info is liker this:

Social Science Profile
course number
discipline
# students registered
avg, grade
% passed

Commerce Profile
course number
discipline
# students registered
avg, grade
% passed

etc.
I am not sure which tables I need to create.
I want to display the info by profile and group the courses by discipline
(econ, math etc). I know how to calculate totals. I can't get the table to
display the info correcly.

--Thanks,
Sarah

 

[Jeff Boyce]

Sarah

Whenever I see identical table structures for which it is only the table
names that distinguish among them, I think of ... spreadsheets!

Access is a relational database. If you use one table per category like
you've done, you are guaranteeing yourself considerable maintenance hassle
whenever you add (or remove) a category -- you'll need to deal with the
table, queries, forms, reports, code, etc.

Access offers excellent features/functions, but they "expect"
well-normalized data.

Here's a different approach:

tblProfile
ProfileID
CategoryID (this is a 'foreign key' field, pointing back to the
CategoryID in your tlkpCategory)
CourseID (foreign key, pointing to CourseID in tblCourse)
DisciplineID (...
StudentsRegistered (the number)
AvgGrade
PassingPercentage

tblCourse
CourseID
CourseTitle

tlkpCategory
CategoryID
Category (e.g., "Social Science", "Commerce", ...)

Now, that said, I still urge you to reconsider using Access to store
"calculated" values. Your [AvgGrade] and [PassingPercentage] fields are
both calculated values.

If you are (only) interested in comparing these for differing 'categories',
why bother using Access? MS Excel (or another spreadsheet) may do quite
nicely...

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP