It's a Many to Many problem. Here is how it goes:
1. Create as many basic tables as they are criteria you need for performing your searches (Sonds, Artists, Albums, Genres, etc.).
Each table will have the following structure:
SysCounter, Autonumber, Primary key
Name, Text (n), Indexed, Duplicates ok
xyz... ' any other info you want to associate.
Ex. Table Songs
----------------
SysCounter, Autonumber, Primary key
Name, Text 100, Indexed Duplicates ok
Date_Of_Creation, Date/Time
etc...
2. Create a table of Associations having also a primary key (ex. SysCounter) and as many columns of they are tables in 1. Each column being of type Number Long, Indexed, Duplicates ok.
note: FK stands for Foreign Key i.e., some data, (here a Long Integer) that is
a Primary Key in another table.
Ex. Table Associations
---------------------
SysCounter, AutoNumber, Primary key
FK_Songs, Number Long, Indexed Duplicates ok
FK_Performers, Number Long, Indexed Duplicates ok
FK Albums, Number Long, Indexed Duplicates ok
FK_Genres, Number Long, Indexed Duplicates ok
FK_Authors, Number Long, Indexed Duplicates ok
etc...
Now you can build a serie of queries that from a basic table will retrieve all pertinent information using the Association table, like this:
Let's suppose that I start from the Songs table.
1. I need all the primary keys of the Table Associations where the row I"m busy with is present in the FK_Songs column, so:
Code:
SELECT Associations.SysCounter
FROM Associations
WHERE (((Associations.FK_Songs)=<current Songs.SysCounter>));
2. From there, I will retrieve every primary key of every table which is linked with the current row in the Songs table:
Code:
SELECT Associations.FK_Albums
FROM Associations
WHERE (((Associations.SysCounter)=<every SysCounter from the list I retrieved in 1.>));
or:
Code:
SELECT Associations.FK_Performers
FROM Associations
WHERE (((Associations.SysCounter)=<one SysCounter from the list I retrieved in 1.>));
3. Now I can revieve all the rows linked with my song:
Code:
SELECT Albums.*
FROM Albums
WHERE (((Albums.SysCounter)=<every FK_Albums from the list I retrieved in 2.>));
The same principle is valid whatever can be the table you start with (the "root" table).
Those queries can be dynamically built and become the data source of a listbox, a combobox, a subform etc.
Have a nice day!
dBforums
>
Music Collection relationships?
08-12-09, 03:48
Linear Mode
