1 one 2 M relation WORKS; another DOES NOT !?!?!

[Bob]

running access 2k;

I have the following 3 tables:
employee: ssn (key; no dups)
complaints: ssn (key; dups ok), id (key; no dups)
issues: ssn (key; dups ok), id (key; dups ok), seq (key,
autonumber; no dups)

I have employee joined to complaints by [ssn] in a 1 to many relation.
When I open employee for browsing, I see a "+" sign under each record,
and when I click on it, I see the related records in complaints.

NOW -
I have complaints joined to issues by [ssn] and [id] in a 1 to many
relation. BUT - when I browse complaints - I DO NOT see the "+" sign
showing the relation to issues !!!???

I've tried removing the join btn emp & comp.
I've tried variations on the join btn comp. & iss. to no avail !!

could somone PLEASE tell me what the heck is going on here?

(all tables are in the same db; and are access tables)

tia - Bob

 

[Tim Ferguson]

could somone PLEASE tell me what the heck is going on here?

Yes: you are making the mistake of trying to do anything useful in the
table datasheets.

Use forms for interacting with data, and reports for reporting on them.
That is what forms and reports are for.


Tim F

 

[Bob]

Yes: you are making the mistake of trying to do anything useful in the
table datasheets.

Use forms for interacting with data, and reports for reporting on them.
That is what forms and reports are for.


Tim F

hi Tim;
tx for your reply!

The ONLY reason I'm looking @ the datasheet view IS because my forms
aren't working either.

It was only after digging into my forms, and finding nothing wrong;
that I happened to look in the datasheet view. There I discovered that
table A (which is related to table B) shows related records under each
record in table A from table B.

Now - I have table B related to table C; and when I open table B; I
don't see ANY related records from table C.

I therefore must assume that something is seriously wrong here.

any other thoughts?

tia - Bob

 

[Bob]

Yes: you are making the mistake of trying to do anything useful in the
table datasheets.

Use forms for interacting with data, and reports for reporting on them.
That is what forms and reports are for.


Tim F

hi Tim;

tx for your reply.

The ONLY reason I tried doing this in the datasheet view, was as an
excercise because my forms are not working either.

I've spent alot of time digging into the forms, and could find nothing
wrong. It was only after I noticed that the relationship doesn't seem
to be working in the datasheet view either, that I came to the
conclusion that something's seriously wrong here.

so the fact that the datasheet view doesn't show the relationship - I
believe - IS the problem with my forms....

ne other thoughts?

tia - Bob

 

[Jeff Boyce]

Bob

As you've found, trying to use the drill-down 'feature' of the tables is
fraught with headaches.

First, I'd look at the Relationships window, to determine if Access even
"knows" the tables are to be related. Next, after establishing the
relationships (my hunch is they are not), I would use queries to collect the
data my forms needed. Finally, I'd work on the forms, pointing at the
queries as data sources.

I'll echo Tim's comment -- just say NO! Don't try to work in tables --
forms give you much more control.

Good luck!

Jeff Boyce
<Access MVP>

 

[Bob]

Bob

As you've found, trying to use the drill-down 'feature' of the tables is
fraught with headaches.

First, I'd look at the Relationships window, to determine if Access even
"knows" the tables are to be related. Next, after establishing the
relationships (my hunch is they are not), I would use queries to collect the
data my forms needed. Finally, I'd work on the forms, pointing at the
queries as data sources.

I'll echo Tim's comment -- just say NO! Don't try to work in tables --
forms give you much more control.

Good luck!

Jeff Boyce
<Access MVP>

Jeff;
tx for your reply.

In fact I had already done all of your suggestions before I even tried
playing w/ the table datasheet. Everything looked fine.

I still don't know why this isn't working; but I managed a
work-around...

tx again for your help.
Bob

 

[Tim Ferguson]

It was only after digging into my forms, and finding nothing wrong;
that I happened to look in the datasheet view. There I discovered that
table A (which is related to table B) shows related records under each
record in table A from table B.

OK: fair cop -- table sheets are for debugging! I also think that Jeff is
right and the place to start looking is the relationships window. There
should be TWO lines connecting the Complaints and Issues tables -- one for
the SSN fields and one for the ID fields. Access is not very elegant at
displaying what you and I both know is one relationship: it insists on
showing how each field relates to each foreign key.

If it looks wrong in there, post back and we can talk through getting it
set up right.



Employee Complaints Issues
======== ========== ======
*SSN -------< *SSN ---------< SSN
Name *ID ----------< ID
Etc Description *Seq
IssDate


Hope that helps


Tim F

 

[Bob]

It was only after digging into my forms, and finding nothing wrong;
that I happened to look in the datasheet view. There I discovered that
table A (which is related to table B) shows related records under each
record in table A from table B.

OK: fair cop -- table sheets are for debugging! I also think that Jeff is
right and the place to start looking is the relationships window. There
should be TWO lines connecting the Complaints and Issues tables -- one for
the SSN fields and one for the ID fields. Access is not very elegant at
displaying what you and I both know is one relationship: it insists on
showing how each field relates to each foreign key.
[snip]

Hope that helps


Tim F

hi Tim;

thanks very much for your reply;

In fact my relationships are correct. I probably should also mention
that I'm working with a split database; tables in the backend; my
stuff in the front end.

I tried doing the relationships in both the front end only, and back
end. Still had the same problem.

Generally speaking - isn't it better to put relations in the front
end?

tia (again) - Bob

 

[John Vinson]

Generally speaking - isn't it better to put relations in the front
end?

No. Relationships with relational integrity enforced can ONLY be put
in the backend where the tables are. Logically this is pretty clear -
rules defined in A.MDB on tables in Backend.MDB cannot be enforced if
someone opens Backend.MDB directly, or by linking from C.MDB.

 

[Bob]

No. Relationships with relational integrity enforced can ONLY be put
in the backend where the tables are. Logically this is pretty clear -
rules defined in A.MDB on tables in Backend.MDB cannot be enforced if
someone opens Backend.MDB directly, or by linking from C.MDB.

true enough - it does make sense after really thinking about it.

tx again.

Bob