Dynamic Query on a Report

melted349

New member
Local time
Today, 10:03
Joined
Feb 24, 2009
Messages
6
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.
 
Here's a sample where I have a generic report but can open it based on what criteria I want:
http://downloads.btabdevelopment.com/Samples/reports/Sample-GenericReport.zip


Thank you, that looks like that setup would work perfect for what I want to accomplish. I hadn't considered using a form to change up the criteria. This actually will work better than I had thought because I will be implementing this on several reports, so I can have them select the report they would like to open as well as the game that would like to see data for.

Thanks a bunch!
 
Yeah that would probably work out pretty well too, this database that I'm working on started off as a small project for the local wildlife dept, but it's gotten a lot of attention state-wide and may be modified to be used state-wide in the near future. So setting up a form that keeps a dynamic list of reports would be helpful.
 
So setting up a form that keeps a dynamic list of reports would be helpful.

I've found that it is a nice time-saver if you don't have special requirements for the display of the report names. I can keep reports out of production just by not naming them with the rpt_ and then when I want them to show in the list I rename them with the rpt_ and they are there.
 
Hey BobLarson

And sorry to interrupt in the question at hand, but I'm having the same kind of troubles as melted349 had. This report example based on what criteria you choose from a form is brilliant, I just have one problem. What if there are two or more orders from the same Company, and I just want one of them (chosen by date) in my report?
 
So everything works great, and what a time saver this has been. I've been able to create a new report with very basic information and define all of the query in my form I named query builder. I combined the report list and wrote my own definition selections, it works out pretty nicely.

Users are able to select the report they want to view, define the parameters such as date range, what property(ies) , and which game to run the report on. Literally giving them the ability of having 1000s of reports at their fingertips.

The only thing I'd love to be able to add and trying to figure out how, is being able to show the current restrictions on the reports. So for example if they choose to run Harvest Totals from 1/1/2009 to 1/30/2009 on Cedar Bluff and Lovewell for Ducks and Rabbits, then the report header might show something like this:
Harvest Totals
Date: 1/1/2009 - 1/30/2009
Property: Cedar Bluff, Lovewell
Game: Ducks, Rabbits

Any suggestions on how to go about making this possible? I was reading that with the docmd.openreport you have the option to pass OpenArgs, but I'm not sure how to make that work or if it would work for me.
 

Users who are viewing this thread

Back
Top Bottom