i want to write a query that selects values in a table dependent on the value in a related table. i have a table that stores information about files and the info stored is a filename and a series of codes relating to data in other tables.
so for example i want to search for files in the fle table that has a certain composer but the composer information for the file is not stored in this table, it is stored under a song name column which is an id and has the composer id related to it.
so for the file i just pick a song name and this song name has an id as a link to song name table and within this song name table is a composer ID that links to the composer table. i hope this makes sense?
i want to search for a composer and bring up all data from the files table with this composer?
i have included code i have been trying but it does not work
i am running this query in vb 2008
SELECT File.AssetFileName, File.AssetSource, [Song Name].[Song Code]
FROM ((File INNER JOIN
[Song Name] ON File.[Song Code] = [Song or Tune].[Song Code]) INNER JOIN
Composer ON [Song name].[Composer Code] = Composer.[Composer Code])
WHERE (Composer.[Composer Code] = '1')