Tables and Relationships Help!

[Lostguy]

Hello!

I have three tables that aren't talking to each other!

tblEmployees: EmployeeID (pk/autonumber), FName, LName, StAddress,
etc. Also has JobID (fk) and PayID (fk)

tblJobs: JobID(pk/autonumber), JobTitle, JobCode, JobShortName,etc.

tblPay: PayID (pk/autonumber), PayGrade, OTRate,RegRate, etc.

Relationships:

One (JobID in tblJobs) to Many (JobID in tblEmployees). Enforce ref
int/ cascade (x2), join type 1
One (PayID in tblPay) to Many (PayID in tblEmployees). Enforce ref
int/ cascade (x2), join type 1

Problems:

1) When I run a query that uses LName (from tblEMployees) and
JobShortName (from tblJobs), the query comes back as blank. When I try
to run any query that uses 2 or more tables, it comes up blank. When I
run a query using the fields of just one table, it works fine.

2) I know that there should be a way that the tables "hold" the
relationships, so that if I look at a row in tblEmployees, there
should be a number in the JobID field (coming from tblJObs) and a
number in the PayID field (coming from tblPay). Yet, in the
tblEmployees, those columns are blank.

3) I even tried to set up an intermediate table to just show the
relationships (contains just PayID, JobID, and EmployeeID) and that
still didn't work to allow the queries in Question #1.

Alot of words, but essentially, I have 3 different tables that are
each autonumbering like crazy, but don't have
any table columns that are capturing those relations. I also can't do
queries on 2 or more tables.

This is a basic setup problem, but it is driving me bonkers! Hop
efully, there is just some box I need to check somewhere that will
clear all this up.

Any help appreciated!

Thanks!

VR/

Lost

 

[Dorian]

If your foreign keys are blank, obviously the joins wont work. Look at how
these tables are being populated.
The table definitions and relationships sound ok.

-Dorian

 

[OEH]

Hi

Apologies if I am insulting your intelligence, but this sounds like
something I screw up every now and again. When you have a foreign key in a
relationship with an autonumber, you need to use long integer, not
autonumber, as the data type for the foreign key.

OEH

 

[Lostguy]

Nope...I checked the Long Integer-thing, but that wasn't it.

I need some help Barney-style with this. I am missing something basic
and it is kicking my a--.

I have a new business. It has 10 different job positions. I know those
in advance so I design a tblJob.

The fields are JobID (autonumber/pk), and JobTitle (text).Using that
table, I type in the 10 job titles (Job1,Job2, etc.) into the JobTitle
field. The AutoNumber numbers those 1-10.

I know that I will have employees, so I create a tblEmployees with
fields EmployeeID(pk/autonumber), LastName (text), and JobID (long
integer number/FK from tblJob.)

I set up the relationship as one (JobID from tblJob) to many (JobID in
tblEMployees) because many employees could have the same one job. I
also enforce ref int (for every job, there has to be an employee who
has it), and cascade update (if a job title gets renamed, the rename
will be reflected by all the associated employees).

Now, I start bringing in the employees. What I would like to do is
have a form where I can a) type in the name of the employee (Smith)
and then b) select their job from a dropdown (Job 8). I then should be
able to go back a table and see that relationship (that Smith has Job
8,or at least the record numbers for those in 2 columns)

1) I can't seem to set up the form for this. Since I am typing data
into one table, and using a dropdown from another table, it doesn't
seem to work right.

2) When I type in "Smith", it goes in the tblEmployees in the LastName
field with Autonumber 1. When select Job 8, where does that value get
stored? Where does the Smith-to-Job8 relationship get stored?

3) Why would you ever base a form on a query? Don't you use forms to
display and input data INTO a table, and queries to extract/view (but
not input) data FROM a table???

I am using all the information I get! Thanks!

VR/

Lost