records not appearing in linked table

[Guest]

I have an Access 2003 database with a table that contains, among other
things, location names (called Tracks). It is linked to a table that
contains all the information associated with that name (coordinates, state,
etc.; called Locations). It is a one(Locations)-to-many(Tracks)
relationship, without referential integrity. Both tables contain the field
Location Name and Location Name is the primary key in the Locations table.
It works fine: when I enter a location into Tracks, it shows up in Locations.

Problem: I created a new table (TPL) where I also enter Location Name. I
linked this to the Locations table. I entered a bunch of new locations into
this new table, but only some of them are showing up in the Locations table.
I entered the data using a form with Location Name from the TPL table and a
sub-form with the associated info from the Locations table. When I entered
it, all the data showed up on the form and all the locations are in the TPL
table. Why are only some locations showing up on the Locaitons form? Where
did the associated info I entered into the form go?

Can anyone explain what I may be doing wrong?

Cathy

 

[Guest]

I'm thinking that the record source for the Locations table joins both the
Locations and Tracks tables. If you have an inner join between those two
tables AND no matching records in one or the other, for example no Tracks for
a Location, the Location will not show up.

You need to modify the query for that form's recordsource to a left join.
That's where it will show all the Locations but only the matching Tracks. You
get to this by double-clicking the line between the two tables in the QBE
grid while in design view.

 

[Tim Ferguson]

I have an Access 2003 database with a table that contains, among other
things, location names (called Tracks). It is linked to a table that
contains all the information associated with that name (coordinates,
state, etc.; called Locations). It is a
one(Locations)-to-many(Tracks) relationship, without referential
integrity.

Huh? What is the point of _not_ having RI?

Both tables contain the field Location Name and Location
Name is the primary key in the Locations table. It works fine: when I
enter a location into Tracks, it shows up in Locations.

I don't see what this means. The Locations table will only display
records from the Locations table... putting a record into the Tracks
table should not affect the Locations table at all.

Problem: I created a new table (TPL) where I also enter Location Name.
I linked this to the Locations table.

If you mean "linked" like above, without RI, then there is no meaningful
link...

I entered a bunch of new
locations into this new table, but only some of them are showing up in
the Locations table. I entered the data using a form with Location
Name from the TPL table and a sub-form with the associated info from
the Locations table.

Okay: this makes a bit more sense. You can make a subform filter data
without a relationship, just be joining the tables. I am guessing that
what you are seeing is that when you enter "behind the bike shed" in the
TPL.LocationName field, sometimes the appropriate Locations record will
show up in the Locations subform and sometimes it won't.

If that is the case, then I guess you are suffering from the lack of RI
in your tables design. Remember that "Behind bike shed" and "Bhind the
bike shed" and "behind the bike shet" are all different and none of them
will match the record you intended "behind the bike shed". And Access
will not complain, because you never asked it to: that is what RI means.

Hope that helps


Tim F