Access help needed

Factual Questions
1

I’m doing a small database for my job and can’t figure out how to link common data so I don’t have to re-enter it. I have the following tables

tblClassID- Class ID(primary key), Class Name, Class Date # of days the class is

tblClassRequest-ID(Primary key) SS#, Course Code
I need ss# to pull from the Employee ID Table, and Course Code to pull from Course Table

tblCourses-Course Code (primary key), Course Name, # of days

tblEmployee-Agent Index(Primary Key),First Name,Last Name, # of classes requested ,SS#

I want my supervisor who will be using this to schedule the classes based on what ones the employee asked for and what days are open.

2

What’s your question exactly? Are you asking how to write the SQL statement, how to design the tables better, or what?

3

A couple of years back, I wrote a small demo application (in Access 97 but it should run OK in later versions) - it was intended to demonstrate a technique for sending out reports customised for each individual recipient, but it just happens to be based on a very simple list of people, a list of events, and a third table that describes the events that each person is scheduled to attend - the possible values in the third table are picked up as lookups from the other two.

It is incredibly crude, but might be of some use in getting you started - you can download it by clicking [here](http://www.gurman.co.uk/mdb/Custom email reports.zip).

4

I should probably also mention that lookups defined in the table properties are not the only way to do it, they probably aren’t the most efficient or elegant either, but they work and they are easy to set up.

5

I wasn’t planning on writing any SQL. The problem was I originaly wanted certain data from different tables put into one table that the query would be based off. I ended up fogetting about doing a query for now and just pringing a report with the information that my manager needs. The only problem is. I have to manualy choose some items that should be linked. What I mean by that is I have the following data at one point

Course ID XXX Description YYY date ZZZ

When I add a person to the class list I have to choose what Course ID and what Date, to schedule them for while I want the date to stay locked with the ID.

I hope I made that understandable

6

I’ve added a couple of simple forms to the example above for management of the data; the ‘bookings’ form takes data from two different tables.

7

I´m not up to date with Access terminology, but I did some work with it a couple of years ago. If by “lookups” you mean foreign keys, then that´s not only efficient and elegant, but it´s also the basic idea behind relational databases. Other methods to link tables are basically hacks. Foreign keys are the only way to enforce referential integrity on a system level.

I know that the problem stated by the OP is more complicated, and I have no complete solution; but for a sub-problem of the OP:

Set up a table with SS# and CourseCode#, make sure both foreign keys reference the primary keys of their respective tables, and make both columns the combined primary key of “tblClassRequest”. There is no need to have a designated primary key (ClassRequestID) for this table.
This implies that every course is unique (i.e. an employee cannot attend the same course more than once); if that´s not what MannyL had in mind, then add a date to the table (as a part of the primary key) to make the association unique.

8

Yes, they are foreign key relationships, but by ‘lookups’ I specifically mean that the table designer allows you to make the field appear everywhere as a dropdown (even in the raw table view), selecting values from another table.

The stored value can be displayed, or one of the other fields in the related record can be shown.

9

Ahh, sorry, I misunderstood this.

I also realized by rereading the OP that the social security # is not the primary key in the “Employee” table, so my suggestion doesn´t work. But if both “Agent Index” and “SS#” are really unique for every employee, then they shouldn´t be stored together in the same table, as you could assign the same SS# for different agents. (IIRC, then the table is not in first or second normal form or something). The textbook solution is to store AgentIndex -> SS# in a separate table, with AgentIndex as the primary key, and use AgentIndex for every association in other tables.