ACCESS FEED: RE: [MS_AccessPros] Relationship Issue ?

Rabu, 26 November 2014

RE: [MS_AccessPros] Relationship Issue ?

Hi Jerry

Composers_1 is not another table. It is merely a second representation of Composers in the Relationships window. For some internal reason, the Relationships window does not allow two sub-windows with the same title text. It is quite legal (and fairly common) to have two or more foreign key fields in a table with links to the same table, so that is not causing the problem.

I suspect that the problem is to do with either lookup fields or subdatasheets (or possibly both). Experienced Access users never use lookup fields or subdatasheets, because the correct place for data entry and onscreen display is a form.

I'm not certain, but I think a table can have only one Subdatasheet, so when you click on the + sign you will only see the records from one relationship.

I suggest you create a simple continuous subform listing songs with its RecordSource based on the MasterSongIndex table – call it sbfSongList. Then create a form based on your Composers table. On this form, put two subform controls, with both containing sbfSongList as their SourceObject . Label one "Songs with music by:" and the other "Songs with lyrics by:". Set their LinkChildFields properties to "MusicBy" and "LyricsBy", respectively, and set the LinkMasterFields of both to ComposerID.

Now, when the form displays a composer, you will see two lists that automatically update with the related songs in the two categories.

Good luck!

_____

Graham Mandeno
Microsoft Access MVP 1996 - 2014

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Thursday, 27 November 2014 12:11
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Relationship Issue ?

Hi everyone. I am having an issue that I would appreciate some help with. I created a Composers table that I thought would be useful to when populating my song database where there are separate fields for LyricsBy and MusicBy. Normally, these fields are going to be identical, but not always. The relationships from TABLE - MasterSongIndex to TABLE - Composers were created automatically by Access when I set up the LyricsBy and MusicBy fields to look up the values in the Composers Table. I did not expect a table to be "sub-created" or whatever as TABLE - Composers_1, but my issue is that it appears whenever the LyricsBy and MusicBy values are not entered as identical names, when I click on the Composers Table and click the + sign on the composer's name, the songs do not show up if there is a difference in the two fields. Do I need to create separate tables for LyricsBy, MusicBy and for all composers? Thanks for any input or suggestions and Happy Thanksgiving to those of you in the United States. ---Jerry Boor, Rochester Hills, Michigan

[Non-text portions of this message have been removed]