I'm having some trouble here, I need to be able to sort and search by a list of 40 "Trades" that will be multiple selections. in version 2008, i was able to select multiple items, but not effectively create a search function. How do I set this up so I can actualy add records with multiple values from a list in a seperate table, AND have it ready for reports that get requested that are sorted by variouse catergories "I want the reports in these counties that are listing theses trades and no older than this date"
as a start, it would make things easier on you if your tables had an autonumber primary key (the same way you made primary key in your Employee table).
second, don't use table-level lookups. make the lookups at form-level. this, however, works best if all relevant tables have autonumber primary keys. and the database would work best with form-level lookups. best not to piss off access, coz you'll lose your hair fast.
then, i think you'd need to make some adjustment in your city/county/state tables. maybe a few junction tables would fix that, i'm not sure how your regional data fits together... i dunno, maybe it's ok the way it is.
aside from all that. i see you have made your report based on a query which asks for the user to enter a parameter value.
the best way to do this sort of filtering is actually to have a 'filter form'. that is, an unbound form with the controls (fields) that you want to filter.
so, you would add a combo for date, a combo for trade and a combo for county. make these combos lookup the appropriate tables i.e., the user can only select trades which exist, or dates which aren't in the past/future, or counties that aren't in the states you deal with.
then, instead of using [enter parameter] in the query, you would make the criteria something like this:
Code:
like forms!frmFilter.txtTrade
where frmFilter is whatever you called your form; and
where txtTrade is whatever you called that control.
each field that you want to filter would usually have it's own control on the form. so the date control feeds the criteria in the date field of the query.
you would then put a button on that form which opens the report for the user. the query automatically gets the data from the form and the user doesn't have to guess what kind of data the query will accept.
the power of this filter form is that you can make the combo's queries exclude any superfluous data (like if a county has not done trade at all).
