This is a SQL statement query (so may be in the wrong forum). Any help much appreciated!
I have 3 tables in an Access DB (I know!):
IndexData is main table with title, description, date, fileid
Category table has categories (Cat1) associated to fileid in IndexData i.e. a fileid could be listed 5 times(rows) with different categories associated to it.
Subcat table has category (Cat2) associated to fileid in IndexData
I can do a search for a search term (strtxt) within a specific category (strcat1) and get correct results using:
strcat1=request.querystring("cat1") strtxt=request.querystring("searchtext") SELECT DISTINCT id.[Title],id.[Description],id.[LastModified], c1.[Cat1], c2.[Cat2] FROM IndexData id, Category c1, Subcat c2 WHERE c1.[FileID]=id.[FileID] AND c2.[FileID]=id.[FileID] AND c1.[Cat1] = '" & strcat1& "' AND id.[Title] LIKE '%" & strtxt &"%'"
I then loop through the results to display them.
However, when I try and see results for a search term in all Categories, in my results I see Title, Description, LastModified repeated for each category. I can't get those fields to show uniquely (I would then have a list of Categories listed next to each row).
I'm thinking it must be possible to put a sub-query within the main SELECT statement, but cannot work out how.
I hope that makes sense and someone can advise me please.