database matching

[Harrild]

i am creating a CD Database for a school assessment (last year high school) and i got one table which is my main table, and each cd has it's own table (all same format) but want to display info from the main table and the others tables at the same time. How?

[Chris]

Properly you can use LEFT JOIN statement. But can you just post your database structure, so that I can have a better understand on your database structure before I work out some sample code for you?

[Harrild]

ok my database structue is like so

Main Table contains

Band Name - field 1
Album Name - field 2
Album/Single - field 3
Released - field 4
Company - field 5
Serial - field 6

and the individual cd tables are named by album name so i can use that to link but the sturcture is as follows

Track No - field 1
Track title - field 2
Track time - field 3


So far the front end displays 1 record from the main table and i want to display the relative table. oh and how do you write SQL in VB?

[Chris]

Before you can use the LEFT JOIN syntax, you need to add one more reference into your individual CD table (Here I named it as TrackTable) and the code just like below:


"SELECT * FROM MainTable LEFT JOIN TrackTable ON MainTable.AlbumName = TrackTable.AlbumName WHERE MainTable.AlbumName='" & <Your Choice> & "';"


Without the relationship between the MainTable and the TrackTable (AlbumName Field) I don't think you can retrieve the individual CD information.



[Edited by Chris on 06-10-2000 at 02:11 PM]