Here is a question I posted to the unofficial forum - many views, no replies...
I have a database of my music CD collection which I am migrating from MS Access to Base. I have moved the tables into Base and am now building the relationships. The database has a table ARTISTS which which as the name implies contains the artist associated with the CD or Track (same as CD artist except on a various artist CD). Herein lies the rub. The same ARTISTS table is used to lookup the name of the artist associated with a CD or a TRACK.
I need to add the ARTIST table to the relationship design twice. Once as a One-to-many relation into CDS and separately as a One-to-many relation into TRACKS. If I try to add ARTISTS to the relationship design a second time nothing happens. Base simply shifts focus from the Add Tables dialog to the ARTISTS table which is already in the relationship design window.
I created a query (view) of the ARTISTS table but the relationship designer does not seem to allow views. It is not appropriate to physically duplicate the ARTISTS table under another name just to add it to the relationship. And no, it is not possible to relate TRACKS and CDS to the same ARTISTS table due to the "VARIOUS" artists situation.
Any ideas, suggestions???
TIA,
Ken
p.s. If anyone is interested I can make a copy of the database available on-line
p.p.s. I can add the table a second time when building a query and the results are correct. Here is some sample SQL:
SELECT "ARTISTS"."AR_NAME", "CDS"."CD_TITLE", "TITLES"."TI_TITLE", "ARTISTS_1"."AR_NAME" FROM "CDS" "CDS", "ARTISTS" "ARTISTS", "TRACKS" "TRACKS", "ARTISTS" "ARTISTS_1", "TITLES" "TITLES" WHERE ( "CDS"."CD_AR_KEY" = "ARTISTS"."AR_KEY" AND "TRACKS"."TR_CD_KEY" = "CDS"."CD_KEY" AND "ARTISTS_1"."AR_KEY" = "TRACKS"."TR_AR_KEY" AND "TRACKS"."TR_TI_KEY" = "TITLES"."TI_KEY" ) AND ( ( "ARTISTS"."AR_NAME" = 'VARIOUS' ) )