If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Running a parameter query from a form- further filtering
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! |
#2
|
|||
|
|||
Running a parameter query from a form- further filtering
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? "Eric" wrote: 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! |
Thread Tools | |
Display Modes | |
|
|