I'm sure I'm not the first to ask this:
I have a query that is based on two tables: tblMeasures and tblMeasureSetJunction. The latter is a junction table between tblMeasures and a third table that that has a many to many relationship with tblMeasues. There is a form, frmAddMeasure, that uses tblMeasures as its recordsource and has a subtable that uses tblMeasureSetJunction as a recordsource.
By programatically changing a few properties of the form, I can use the same form to allow the user to either add, edit, or review the information in the database.
I have just spent many hours (days actually, since I'm new at this and teaching myself as I go) developing another form frmSearchMeasures, that allows a user to specify criteria to filter frmAddMeasure for reviewing purposes. The code under frmSearchMeasures creates RecordSource and Filter strings, feeds them to frmAddMeasure, and then viola, the frmAddMeasure is filtered by all the criteria on the main and subforms. Mostly. There are about 18 criteria between the two tables, and they come together in some very interesting ways. The filter is a little buggy but it's getting there.
Anyway. I happen to have a query that runs on the two tables that frmAddMeasure is based on, and outputs similar results to excel. After a user uses frmSearchMeasures to filter frmAddMeasure, I would like to user to be able to click a button to export all the results to excel. I think the easiest way to do this would be to run my query on the filtered results.
By now you are asking, why not just use the fields from frmSearchMeasures to narrow the query?
I have two problems. The first is that I heard (don't know if it's true) you can only use a max of six fields to do this, since access extrapolates all the possible combinations of filled in fields to create a SQL statement. With 18 fields, any of which can be null, that's 2^18, or over 250,000 possible combinations. I don't think access can handle that.
The other problem has to do with the nature of the data. The subject of the database is quality measures in healthcare, which are used by a number of different entities who either do, do not, or will, publicy report on these measures. This means that for each junction between measure and user, the user has three possible reporting statuses, but since each measure has many users, each measure can be "not reported and not pending," "reported by at least one and not pending by others," "reported by at least one and pending by others," "Not reported at all and Pending by at least one," etc. etc. The code under frmSearchMeasures figures out which measure falls into which category based on the reporting status of each of any number of measure users. Repeating this analysis with a query I think would be more complex and very time consuming.
So again the easiest thing, I think, would be to run a very simple query on the filtered table, but I don't know how to do this or even if it can be done. Any advice, at this point, would be extremely appreciated. I've been at this for quite a while now and I'm brand new to access and visual basic as of a few weeks ago.