thank you Want a cool sig for your very prompt reply.
ah, yes, quite right, I did mention only the two tables at the beginning, apologies.
maincourses has a one-to-one relationship with the course code - field contsupref on table contsupp
i.e. for the - hopefully one only - row returned from contsupp, the course code field=contsupref will be unique in maincourses
so I thought that if one row is returned from contsupp, it would be the course code (contsupref) on which main courses will be joined.
the purpose of the mc.ucrstart > '01/01/2010' in the maincourse is to return rows for only those courses that were taken after January 1st 2010;
the condition mc.code like 'trg%' is actually redundant, as the condition in the main query:
WHERE contsupp.contact = 'course name' and contsupp.contsupref like `
should return only course rows, those rows in contsupp in which the contact='course name' and the contsupref field is like 'trg%' on which the maincourses is joined.
a contact will never take a two courses with the same course code.
here is some simplified data to give you a better idea
table contact1:
accountno contact (contact name)
1234 sam
2345 geoffrey
2234 daniel
5513 julia
2115 abbey
table contsupp:
accountno contact contsupref country
1234 course name TRG2155 Introduction to HACCP Planning
1234 course name TRG2156 HACCP1
1234 course name TRG2157 HACCP2
1234 course name TRG2158 HACCP3
2345 course name TRG2201 Microbiology V
2345 course name TRG2318 Ingredient Labelling
2345 course name TRG2156 HACCP1
2234 course name TRG2201 Microbiology V
2234 course name TRG2202 Microbiology VI
5513 course name TRG2160 Microbiology I
5513 course name TRG2161 Microbiology II
5513 course name TRG2184 Microbiology III
2115 course name TRG1901 Allergens and
2115 course name TRG1902 Preparing for Audits
2115 course name TRG1903 SQF Certification Level 1
2115 course name TRG1904 SQF Certification Level 2
2115 course name TRG2001 SQF Certification Level 3
2115 course name TRG2002 BRC Certification
2115 course name TRG2003 Audits for Internal Auditors
table maincourses:
code start date
TRG1901 10/01/2009
TRG1902 10/08/2009
TRG1903 11/14/2009
TRG1904 12/10/2009
TRG2001 02/12/2010
TRG2002 04/21/2010
TRG2003 05/06/2010
TRG2155 01/01/2010
TRG2156 01/01/2010
TRG2157 01/01/2010
TRG2158 01/01/2010
TRG2160 01/01/2010
TRG2161 01/01/2010
TRG2184 01/01/2011
TRG2201 01/01/2012
TRG2202 01/01/2012
TRG2318 02/09/2008
the report I want the query to report is:
1234 sam TRG2155 01/01/2010
2345 geoffrey TRG2201 01/01/2012 (first row on file and course taken after January 2010)
2234 daniel TRG2201 01/01/2012 (first row on file and course taken after January 2010)
5513 julia TRG2160 01/01/2010
2115 abbey (no courses since the beginning of 2010)
I hope this makes it a little bit easier to see what it is I`m looking for.
thanks so much
kim