One to many and many to one (1 Viewer)

[access2010]

Could I please receive a suggestion on how to populate the form = Investment_AnalystEquities_F = from the two linked tables.

We would like to Click on the;
Symbol_Stock_AY and populate the Stock_Name_AY

Then click on the;
Analyst_Name and populate;
Analyst_Pos
PhoneNum
Analyst_Company

One Symbol_Stock_AY, can be covered by many Analysts

One Analysts can also cover many different Symbol_Stock_AY

Your suggestion on how to do this in Access 2003, will be appreciated.

Thank you,
Nicole

 

[arnelgp]

you must test the Symbol_stock table is not included on your sample mdb.

 

[access2010]

Thank you, Arnelgp, for your note.

I had thought that we could use the Investments01_Appendix, table.

Please advise me if I have done something wrong.
Nicole

 

[Pat Hartman]

Your relationships are not defined correctly. PresID should be the PK in investment_Analyst_T and you should have a unique index on Analyst_Name although this will be problematic since you may run into people with the same names. So probably a compound unique index of Analyst_Name plus PhoneNum would be better since tht would ensure uniqueness. Then, instead of Analyst_Name in investment_AnalystEquities_T you would have PersID and the relationship would be defined between the two PersID fields.

The mystery is why investment_AnalystEquities_T and Investments01_Appendex both have the same PK and why it is defined as a number.

Since I can't work out how to solve your problem without knowing the relationships, I'm posting a sample database that is a m-m relationship which I think is what you are working with. The two forms show two different ways of looking at the data and two ways of showing the related data. Hope this will help you get your relationships working.

 

[access2010]

Thank you, Pat for your reply.

 

[access2010]

Thank you Pat for your suggestions, which I have corrected.
Please look at our Attached Access 2003 Data Base.

PK as a number.
The person in our office created this database MANY years ago (1999) and I am now trying to get it to co-operate with the correct data.

Please advise, what you would suggest to use for the PK instead of an auto number format?

Looking through the Analyst_Names, there does not seem to be any duplication.

===
I think that the files that you had uploaded are in a format other than Access 2003

Thank you
Crystal

 

[Pat Hartman]

My best guess is that Investment_AnalystEquities_T is supposed to be a junction table that connects the other two tables. Please review the sample I uploaded earlier so you can see how many-many relationships work.

Investment_AnalystEquities_T
AnalystEquitiesID (autonumber PK)
PersID (FK to analyst)
InvestmentID (FK to investments)

All the other columns are redundant since they exist in either analyst or investment. If you want to see them, use a query that joins this table to one or both of the others.

Poke around the example.

Don't forget to define relationships between the tables and enforce RI.