Relationship question

[Anne]

I have a one on one relationship from TblEmployeelist JodescIDFK to
TblJobDesc JobdescIDPK.

When trying to create a query with employee information, access creates a
table TblJobDesc_1. Why?

 

[Piet Linden]

I have a one on one relationship from TblEmployeelist JodescIDFK to
TblJobDesc JobdescIDPK.

When trying to create a query with employee information, access creates a
table TblJobDesc_1. Why?

it must have been added to the query more than once.

 

[Anne]

No, it must have something to do with the relationships. If I delete the
table and put it back from the table list, it come back with an error message
that there are ambiguous outer joins. Force one of the joins to be performed
first.

JobdescID in tbljobdesc has a one on many relationship to JobdescID on the
employee table.
I also have a Tblburden where the BurdenID = JobDescID as well. This is
creating the problem. The TblJobDesc just add descriptive names to the ID,
where tblburden has the rates for Wc, Social Security, etc.

Do I attach the tblBurden JobDescID to the TblJobDesc or the
TblEmployeelist. I would think it would have to be TblJobDesc. But I think
this might be were my problem is, because the BurdenID is a primary key and
really the same as the JobDescID. Perhaps I don't know what I am doing?

 

[John W. Vinson]

I have a one on one relationship from TblEmployeelist JodescIDFK to
TblJobDesc JobdescIDPK.

When trying to create a query with employee information, access creates a
table TblJobDesc_1. Why?

It certainly shouldn't, unless you're adding TblJobDesc to the query twice. Is
it literally TblJobDesc_1? Could you post the SQL of the query?

 

[Anne]

Unfortunately, making many changes in field names I turned autocorrect, which
I am not sure had something to do with the crash, but it crashed and I need
to start all over. No I did not back it up, since I was not anywhere near a
partial satisfactory stage. I will backup now, regularly.

The problem is that these tables are in a backend and the autocorrect does
not recognize changes in the tables, which creates a lot of problems.

I really need to find out about the relationships I asked about.
Thanks,
Anne

 

[John W. Vinson]

Unfortunately, making many changes in field names I turned autocorrect, which
I am not sure had something to do with the crash, but it crashed and I need
to start all over. No I did not back it up, since I was not anywhere near a
partial satisfactory stage. I will backup now, regularly.

The problem is that these tables are in a backend and the autocorrect does
not recognize changes in the tables, which creates a lot of problems.

I've found that if you make changes to the design or releationships of tables
in a backend, it's safest to DELETE all the table links in the frontend and
use File... Get External Data... link to create fresh links.

Obviously if the table structure changes affect your queries or forms, you'll
need to modify them appropriately.

I'll take a look at your relationship questions and post a separate reply.

 

[John W. Vinson]

I have a one on one relationship from TblEmployeelist JodescIDFK to
TblJobDesc JobdescIDPK.

Why?

One to one relationships are VERY RARE. If you're not doing "subclassing" or
"table based field level security" it's likely that you don't need a one to
one relationship at all. You can just put a job description field in the
Employeelist table, for one thing.

Could you describe the structures of the two tables, and why you feel that
each employee must have one and only one job (which is reasonable) and why
each job description can apply to one and only one employee (which seems
unusual)?

 

[Anne]

I thought about that and already realized that I nned to import the tables
instead of linking them. I am working on that now. The autocorrection feature
does not catch everything, but the majority of changes.

 

[John W. Vinson]

I thought about that and already realized that I nned to import the tables
instead of linking them. I am working on that now. The autocorrection feature
does not catch everything, but the majority of changes.

Just a warning: it is *NOT* a joke (or at best a very sour joke) that the
"Name Autocorrect" feature is very commonly called "Name Autocorrupt".

It is buggy, unreliable, and VERY risky. If you ever use it at all, *make a
backup of your database before EVERY change*.