How to Force a One-to-many relationship

[MsLady]

I am using Access 2003.
Access front end, sybase backend. I am building the queries in the design mode in Access.
Here is my question...

I am looking for a way to force a one-to-many relationship between two tables. I am connecting the primary key in table1 to a field (foreign key) in table 2. But it keeps going to one-to-one.
It should be doing one-to-many (the "1" and the "8"-sideways infinity symbol).

What am i doing wrong? Please help.

 

[raydan]

open the relationships sceen and right click on the connecting line between the 2 tables. Clk edit relationship and when box appears choose join types. There you can select the type of relationship,where the 2nd and 3rd choices are 1-to-many. Good luck.

 

[QTNM]

Do you have any sample data in the tables ?

QT = )

 

[MsLady]

Do you have any sample data in the tables ?

QT = )

Yes i do. alot of data... at least 10 tables (sybase end). it's kindda huge

 

[MsLady]

open the relationships sceen and right click on the connecting line between the 2 tables. Clk edit relationship and when box appears choose join types. There you can select the type of relationship,where the 2nd and 3rd choices are 1-to-many. Good luck.

I do this in the relationship window and then it forces itself back to one-one when i go back to my queries
and i saved

 

[boblarson]

Sounds like in the second table, where you should be getting the many, the primary key is set so that only one item can relate to the other table.

What is your primary key in your second table, and are you sure that the way it is set up that the table will allow more than one entry for the primary key in the first table? Your 2nd table isn't set up to use the foreign key as part of the primary key for the second table, is it?

 

[Pat Hartman]

YOU do not specify the cardinality of the relationship. Access determines it by analyzing the fields used in the relationship. If you are seeing a 1-1 relationship, then the relationship is primary key to primary key or primary key to unique index. If you want a 1-m relationship, it must be primary key to non-primary key. The many-side field or fields may be part of the primary key but they must not be the entire primary key. You may also not have a unique index on the foreign key.

 

[MsLady]

Thank so much.. Bob and Pat,
I haven't ignored your posts. I have just put the project aside for now since i have huge priorities on my hands these past week. I should come back to this soon.

thanks alot for your time
I'll definately take ur advice and take a closer look at my tables once i get back on it. I appreciate you help and everyone else that has taken the time.

*hugs

 

[MsLady]

If you want a 1-m relationship, it must be primary key to non-primary key.

Thanks guys!
But my 1-m relationship is pk ---> non-pk as said above.
I still have this issue, and i think it is mainly because my tables are ODBC linked SYBASE TABLES. Cos i don't have this issue when i copied and pasted the structure and data of these sybase tables as access local tables.
I guess i might have to look for a sybase tables expert to tell me what's going on

Happy new year to everyone

 

[ejstefl]

If you are in the design mode in queries, i don't think the fact that you don't have a 1 and an infinity sign mean that you're not one to one - it simply means that you're not enforcing relational integrity.

Have you looked at the resulting data for the query to determine that it really was a one-to-one relationship??

As Pat states, you don't determine if its 1-1 or 1-m. Access does.

 

[Pat Hartman]

When the tables are linked, ALL RI, including relationship definition is defined in the source database. So, for your Sybase tables, relationships and RI MUST be defined in the Sybase database. Access will show any existing relationships but you cannot define them there.

 

[MsLady]

When the tables are linked, ALL RI, including relationship definition is defined in the source database. So, for your Sybase tables, relationships and RI MUST be defined in the Sybase database. Access will show any existing relationships but you cannot define them there.

So the table relationships should actually be defined in sybase...?
aaahh I see!!
maybe this is the root of all my problems
i've had so much trouble with this application that i normally don't have in access. subforms coming up blank, reports acting funny etc. Lawd have mercy!

Thanks guys
I'll go find my sybase guru now

 

[MsLady]

If you are in the design mode in queries, i don't think the fact that you don't have a 1 and an infinity sign mean that you're not one to one - it simply means that you're not enforcing relational integrity.

Have you looked at the resulting data for the query to determine that it really was a one-to-one relationship??

As Pat states, you don't determine if its 1-1 or 1-m. Access does.

The thing is, I am not even given the option to enforce ref. int.
I guess sybase is the culprit!

You learn everyday...I'll look at my resulting data and consult with my sybase wizard. I'll be sure to let you know how it goes... thanks all!!

and I wish everyone a happy and prosperous new year!

 

[Tommo]

if you double click on the relationship line that joins the two fields, you can click 'enforce referential integrety', 'Cascade Update Related Fields' and 'Cascade Delete Related fields'. I had a problem with my college work n clicked these n i got the relationship i wanted. You might not need to click all three though, but i did.

Regards, Tommo

 

[MsLady]

if you double click on the relationship line that joins the two fields, you can click 'enforce referential integrety', 'Cascade Update Related Fields' and 'Cascade Delete Related fields'. I had a problem with my college work n clicked these n i got the relationship i wanted. You might not need to click all three though, but i did.

Regards, Tommo

Hi Tommo,
You can read Pat Hartman's reply above. My tables are sybase tables.
When i doubleclick on the join line, all three options (RI, CU, CD) are not enabled.

 

[Pat Hartman]

Tommo,
When your tables are linked, even if the be is Access, all relationships and RI are defined and enforced by the be.