What my dataset looks like:
It's a bunch of fire drills. A field called ID is the key, and associated with each is the Program which performed the drill, the day, etc. Every program runs multiple drills throughout the year
I want the report to show the name of each program and a calculation of the number of drills they have performed.
The problem is the continuous report seems to show each program name multiple times according to the number of drills they have run. For example if Program 1 ran 4 drills, it will repetitively list Program 1 four times before listing the next program. I don't want this, I want each program listed only once.
Here's my SQL for the report's record source. It has a lot of info on other fields contained within it, which I intend to list on the continuous report. SELECT DISTINCT at the start instead of just SELECT doesn't solve my issue.
SELECT Table.ProgramID, Table.ID, Table.[Drill Type], Table.Day, Table.[Drill Format], Table.Shift, Table.Unannounced, Table.Success, ProgramRequirements.FDRequirement, ProgramListMaster.Program, ProgramListMaster.Center, ProgramListMaster.[Division Name]
FROM (
INNER JOIN ProgramRequirements ON Table.ProgramID = ProgramRequirements.ProgramID) INNER JOIN ProgramListMaster ON ProgramRequirements.ProgramID = ProgramListMaster.ProgramID;
It's a bunch of fire drills. A field called ID is the key, and associated with each is the Program which performed the drill, the day, etc. Every program runs multiple drills throughout the year
I want the report to show the name of each program and a calculation of the number of drills they have performed.
The problem is the continuous report seems to show each program name multiple times according to the number of drills they have run. For example if Program 1 ran 4 drills, it will repetitively list Program 1 four times before listing the next program. I don't want this, I want each program listed only once.
Here's my SQL for the report's record source. It has a lot of info on other fields contained within it, which I intend to list on the continuous report. SELECT DISTINCT at the start instead of just SELECT doesn't solve my issue.
SELECT Table.ProgramID, Table.ID, Table.[Drill Type], Table.Day, Table.[Drill Format], Table.Shift, Table.Unannounced, Table.Success, ProgramRequirements.FDRequirement, ProgramListMaster.Program, ProgramListMaster.Center, ProgramListMaster.[Division Name]
FROM (