Running a parameter query from a form- further filtering

[Eric]

I currently have a form set up that links to a vendor table list and
passes that parameter on to the parameter query to bring up all of the
products used by a vendor and the usage data for 2004-200X month over
month with YTD totals for each year. This is for the number of hits on
vendor sites of things we pay for.

The vendor list is it's own table with key, linked to the product list,
which is then linked to separate tables for 2004, 2005, 2006. Each of
the year tables includes the Jan-Dec monthly usage, and then YTD
totals.

I would like to add radio boxes that will allow the users to select
what year ranges to display since they don't always need to see every
year. I don't know what would be the best way to do this. The only
thing that I can think of is have different queries for each possible
year range, and somehow specify that when the radio boxes are checked
in specific combinations, that the query with those ranges should be
selected. There must be a better way to do this, any ideas? I'm
new-ish to access, but I would like to add this functionality to the db
if possible.

Thanks!

 

[Klatuu]

The first problem is having a separate table for each year. I know bean
counter types think like this, but from a database perspective it is wrong
and only makes life harder. The usage data should all be in one table with a
field that identifies the year.

But, that is not the answer to your question, only the reason you are having
the problem.

Rather than a bunch of radio buttons for each year (which means you have to
have a new button every year, just like now, you are going to have to modify
your query each year, and in a few years, it wont work any more), you can use
a list box to list all the years for which you have data.

Now, the question is, is how comfortable are you with VBA? To do this
elegantly, it will take some decent coding skill.

The aproach would be to loop through the tabledefs collection and look for
the year part of the table names so you can identify which years you have
data for. For each table name that matches a year, add an item to the list
box that would be just the year.

Then when the user selects years to include. Loop through the list box's
ItemsSelected collection property and build a Where condition that will
filter your report.

Think you can handle that?