Relationships - I have the wrong one

D

Dan Pickard

Hi,

I am re-creating an Approach database in Access and I am having problems
with my relationships. In Approach the Join that is created is ; -

Insert - "If no records match the "Otherset" record, typing into a blank
"Locations" field inserts a new record.
Insert - "If no records match the "Location" record, typing into a blank
"Otherset" field inserts a new record.

The relationship is created on the ID fields of both tables but when I am
trying to replicate this in Access I end up with the ID numbers being shown
for the "Otherset" table so lots of duplicate numbers and not the ID numbers
for the "Locations" table as I wanted.

I thought the relationship in Access was a one to many (one being
"Location", many being "otherset") with a Join Property of option 2 (include
ALL records from 'tblLocation' and only those records from 'tblOtherset'
where the joined fields are equal). But obviously I am mistaken.

Any advice or ideas would be greatly appreciated.

--
Dan Pickard
-----------------------------
Powerful Windows 2003 web hosting, design and training.
http://www.hosting.fast-trak.net
http://www.design.fast-trak.net
http://www.training.fast-trak.net
http://www.fast-trak.net
 
A

Arvin Meyer [MVP]

As near as I can figure, the relationship should be 1 to many with Locations
being on the 1 side. Leave the Join property alone, (except when writing a
query), and enforce referential integrity. The many side can have as many
duplicates as necessary. You can make one or more fields on the many side
unique so that there will only be duplicates allowed in the foreign key
field.

The only way to insert new records is through forms. There are no triggers
in Access, so there is no mechanism to automatically insert a record. You
can automatically delete records from the many side by setting cascade
deletes.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How do you get rid of the forms vertical bar? 3
Convert from Excel and change field to Autonumber 1
Query multiple table relationships subquery? 1
Table Relationships: An Understanding is necessary... 8
Wrong message with cascading deletes 13
Relationships Headache 5
Show several values within one query cell 2
How do I remove parent-child relationships in the parent table? 3

Top