Hi all I have a question, In my view I have two tables linked together for the users, my issue is that I want them to see one record so they can get the proper total amount of records. The main table is reports and the child table is subjects. How do I make it so that they see one record from the reports table, because if there is more then one subject linked to a reports number which is the primary key, they will see more then one report number. Does that make sense??
SELECT TOP 100 PERCENT dbo.Report.[Incident Report No], MIN(dbo.Report.Date) AS Date, MIN(dbo.Report.TypeOfIncident) AS Type,
MIN(dbo.Subjects.SubjLastName) AS [Last Name], MIN(dbo.Subjects.SubjFirstName) AS [First Name]
FROM dbo.Report LEFT OUTER JOIN
dbo.Subjects ON dbo.Report.[Incident Report No] = dbo.Subjects.[Incident number]
GROUP BY dbo.Report.[Incident Report No]
HAVING (MIN(dbo.Report.Date) BETWEEN CONVERT(DATETIME, '2006-01-01 00:00:00', 102) AND CONVERT(DATETIME, '2006-12-31 00:00:00', 102))