i got a main table Staff details and 5 other tables namely face details, bronze details, silver details, gold details and platinum details. The 5 other tables mentioned above are course tables.
The things is i want to create a report that show the latest course date attended by staff. I want to the report to compare the 5 tables dates and then displayed out the lastest date. how can i do that?
Well you could do a union query that will make the five tables appear as if they are in a single table, then use the TOP sql option to get the "top" date (or use DMAX or similar if you want the date to appear in a report or form).
However, I suspect your data structure is poorly designed. I think you should put the five tables you mention into a single table (say called "CoursesAttended") and include a colunm that designates the type; gold, silver etc. This will make answering such questions much easier.
On the other hand I may be misunderstanding what you are presenting.