I think i've got too many queries

[waipahunet]

My database is now up to 37MB and I think i'm creating unnecessary queries (174 total and climbing) for what i'm trying to do. I've got 21 different manufacturers that I wanted to show their data relating to their claims and other spending. Got one main query/form to enter claims information. I'm modifying the main query and creating one for each manufacturer by specifying the manfucaturer code in each query. Is there a simpler way to do this without having to do a query for each one?

mainscreen.gif - Main switchboard
screen3.gif - The specific info for each manufacturer
screen2.gif - Form contents

 

[FoFa]

You could create a parameter query that asks for OR pulls the Mfg. code from somewhere and would then run using that data as it's criteria.

 

[Mile-O]

If you want to run a report for a specific manufacturer then allow the user to select any manufacturer from a combobox on a form.

In the query, instead of saying the manufacturers name (and creating one query for each manufacturer) reference the combobox.

i.e.

[Forms]![MyForm]![MyCombo]


This will now use the value in the combobox as the criteria - in this case, a manufacturer.

 

[waipahunet]

Thanks for the suggestions guys! I created a main form using a query that asked for the client code and fiscal year and used those two values to form relationships with all the subform data. Worked like a charm and was able to delete a TON of queries and forms. Since not everyone knows all the client codes, I wanted to have it so that you could select the variables from dropdown menus.