Reversing a Relationship 3

[CloudNein]

Hi,

I have created a relationship in Access which from left to right has made a 'many to one' relationship.

However, this was done by Access when I joined the fields. I actually need this relationship as a 'one to many' (again from left to right).

Can anybody advise how to turn this around?

Many thanks,

Nia Phillips

 

[BNPMike]

Relationships are not necessary to make relationships or make joins or indeed to maintain actual relationships. Relationships are maintained by data values and joins are made by SQL.

If you load appropriate data into your tables and generate a logical query, the one-to-many ot many-to-one relationship of the data will magically emerge whatever you previously told Access.

Remember you can always add, delete and amend any default joins lines Access puts in the QBE Query Screen.

 

[PHV]

Anyway you may modify a join type in the Relationships windows by right clicking the joining arrow.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[HevanNHell]

i think you should start again if this doesnt work. Try and right click th ei cond o try and move the relationships to some where else

 

[CloudNein]

Thank You all for your replies, but unfortunately they did not work if anyone else has any idea on how to turn this relationship around please reply.

Thank You very much
CloudNein

 

[RoyVidar]

Access creates the relationship type based on the indexes of the fields you drag and drop.

Having the field "ID" in two tables, in the first table it is indexed with Yes - no dupes, in the second table it is either not indexed or with Yes - dupes allowed, then it will create a relationship where the first table is the parent (one side) and the second table is the child table (many side).

Roy-Vidar

 

[BNPMike]

Cloudnein

Clearly I didn't explain myself. Delete the relationship. Now generate your query and you won't have a problem. If Access still tries to default a join then just erase it. If all else fails remember you can edit the raw SQL. No relationships can upset that.

Note the only aspect of relationships which does have an impact is optional referential integrity. I don't think this is your problem. I suspect you don't appreciate that relationships in a relational database are solely implemented as data values. The fact that Access has a relationships screen is I think one of the most misleading features it has.

 

[DanJR]

CloudNein,

If PHV's response didn't 'help' then did you really understand RoyVidar response? If not, then i guess the assumption is that you'll purposely not wanting to use access as a RDBMS? Just checking that this is the case.

cheers,
dan.

 

[CloudNein]

Thank You to everyone who replied. RoyVidar your reply worked brilliantly thank you ever so much.

Thanks Again
CloudNein