Ok I'm not sure if my title makes much sense or not. To start off with I would consider myself an beginner/intermediate programmer. I have wrote a lot in VB, and I have a pretty extensive background in PHP script and a good understanding of C++. So VBA isn't much of a problem for me, it just takes me awhile to lookup built-in functions and figuring out way things work in access. (I also have a good amount of experience in database, but mostly in mysql)
What I want to accomplish is to create a report that can change it's results based on a user selection from the report. The report shows the sum of the number of unique hunts on each property for that year. (This shows hunts for all game in the database) Ideally I would like to add a drop-down box on the report, so they can change the default game from all to something like duck, and then the report would recalculate the report for each unique hunt for duck on each property for that year.
I've been looking around in other forums but so far all anyone says is to just change the query for each report. I really don't want 14 different reports if I could just have one that could change dynamically.
My line of thinking was that there should be someway to change the query based on the option-box selection and then force the report to repopulate its data. So my query:
SELECT TopCardData.HuntDate, PropertyList.PublicProperty, Year(TopCardData.HuntDate) AS [Year], MonthName(Month(TopCardData.HuntDate)) AS [Month], Game.game
FROM Game LEFT JOIN (((PropertyList INNER JOIN TopCardData ON PropertyList.id = TopCardData.Area) INNER JOIN BottomCardData ON (TopCardData.[Permit #] = BottomCardData.[Permit#]) AND (PropertyList.id = BottomCardData.Area)) INNER JOIN Harvest ON BottomCardData.id = Harvest.bcid) ON Game.id = Harvest.gid
WHERE (((TopCardData.HuntDate) Is Not Null) AND ((Year(TopCardData.HuntDate))<>112));
would get changed to something like
SELECT TopCardData.HuntDate, PropertyList.PublicProperty, Year(TopCardData.HuntDate) AS [Year], MonthName(Month(TopCardData.HuntDate)) AS [Month], Game.game
FROM Game LEFT JOIN (((PropertyList INNER JOIN TopCardData ON PropertyList.id = TopCardData.Area) INNER JOIN BottomCardData ON (TopCardData.[Permit #] = BottomCardData.[Permit#]) AND (PropertyList.id = BottomCardData.Area)) INNER JOIN Harvest ON BottomCardData.id = Harvest.bcid) ON Game.id = Harvest.gid
WHERE (((TopCardData.HuntDate) Is Not Null) AND ((Year(TopCardData.HuntDate))<>112) AND (Game.name = {selected game}));
where {selected game} would be the game selected by the user.
Any suggestions would be greatly appreciated.
What I want to accomplish is to create a report that can change it's results based on a user selection from the report. The report shows the sum of the number of unique hunts on each property for that year. (This shows hunts for all game in the database) Ideally I would like to add a drop-down box on the report, so they can change the default game from all to something like duck, and then the report would recalculate the report for each unique hunt for duck on each property for that year.
I've been looking around in other forums but so far all anyone says is to just change the query for each report. I really don't want 14 different reports if I could just have one that could change dynamically.
My line of thinking was that there should be someway to change the query based on the option-box selection and then force the report to repopulate its data. So my query:
SELECT TopCardData.HuntDate, PropertyList.PublicProperty, Year(TopCardData.HuntDate) AS [Year], MonthName(Month(TopCardData.HuntDate)) AS [Month], Game.game
FROM Game LEFT JOIN (((PropertyList INNER JOIN TopCardData ON PropertyList.id = TopCardData.Area) INNER JOIN BottomCardData ON (TopCardData.[Permit #] = BottomCardData.[Permit#]) AND (PropertyList.id = BottomCardData.Area)) INNER JOIN Harvest ON BottomCardData.id = Harvest.bcid) ON Game.id = Harvest.gid
WHERE (((TopCardData.HuntDate) Is Not Null) AND ((Year(TopCardData.HuntDate))<>112));
would get changed to something like
SELECT TopCardData.HuntDate, PropertyList.PublicProperty, Year(TopCardData.HuntDate) AS [Year], MonthName(Month(TopCardData.HuntDate)) AS [Month], Game.game
FROM Game LEFT JOIN (((PropertyList INNER JOIN TopCardData ON PropertyList.id = TopCardData.Area) INNER JOIN BottomCardData ON (TopCardData.[Permit #] = BottomCardData.[Permit#]) AND (PropertyList.id = BottomCardData.Area)) INNER JOIN Harvest ON BottomCardData.id = Harvest.bcid) ON Game.id = Harvest.gid
WHERE (((TopCardData.HuntDate) Is Not Null) AND ((Year(TopCardData.HuntDate))<>112) AND (Game.name = {selected game}));
where {selected game} would be the game selected by the user.
Any suggestions would be greatly appreciated.