SQL Query Headache

Hi,

I'm working with a Student Marks database.

I have one table that stores information about the subjects that is unique to each learner, example the serial number of the workbook associated with that subject for that learner and that learner's subject mark, however, all generic data is stored in another table to improve normalisation, eg the subjects name and total score.

I also have a table that stores all the minor results related to the subject such as all the practical points and all the exam points. There is no relation between the two results tables as the software does calculations based on the first table and updates the second table.

I now need a query that can give me the Subject name and mark as well as the exam mark and date.

If anybody thinks they can help out please give it a bash, I'm sure it's possible, just not good enough yet...

It sounds like you are retro fitting a database for a new requirement. the best way of course is to directly relate the records in the tables with a UID I would suggest adding a UID to each table that will serve as a 'connector' between the two tables... so in the generic information table have an ID like Student_id... this will serve as a primary key... then in your exams table add the column for student ID... when you go to retreive the data from both tables you can then implement a loop that will retreive the students names... then loop over the student ID for every record that exists in the exam table and uotpur accordingly.

if you are unable to add columns to the existing tables you can always add another table that will serve as a matrix... in other words a series of columns that will serve as a relationship for data between the two tables...

Hope this helps... retrofitting is never an easy task :-)

Larry D
MS Architect Evangelist