Help needed

[longbo43]

Hi folks,

I have an access database (obviously) that has grown into something quite hideous, and now, possibly unmanageable by me. I need help big time.

The database takes entries from an online php form that allows requests to be made for meetings and also allows entries such as required attendees, dates, location, meeting topic and numerous other things. This is then fed back to a My SQL daatbase and then accessed with an access 2007 database which is the main UI.

I have been coping fine up till now.

I need to find a way to filter the records so that the user can select an Executives name from a combo box and this will filter all the meetings that the user is involved in.

My problem is that as the required attendees (the Execs) can be requested in any of 8 entry points I can't see how I can create a filter that will look at all the possible positions that name may be in.

e.g.

John Smith has been requested to attend 4 meetings.


He is Exec1 on meeting 1
He is Exec2 on meeting 2
He is Exec3 on meeting 3
He is Exec4 on meeting 4

At each event the database is used on can generate over 100-200 meetings I need a way to filter the 100-200 meetings so that only the 4 meetings with John Smiths name in will appear on screen.

I have neem toying with subforms and queries with manual filters and am really struggling.

I am not looking for the solution (although that would be good with the little amount of info I have provided) but some help or suggestions on hpw to get started would be great.

Cheers

Dan

[NTC]

ultimately a form/report is just presentation; and so your challenge is to capture the correct records via a query. (that query can ultimately be the record source to the form/report that the user sees)

in query design have the table/fields brought in.....and then you should indeed be able to search for 'Joe' in 8 fields - simply by putting in that criteria in each field but be sure to put each on a separate line (which will be an OR statement) If you put them all on the same line they will be an AND statement.

next you need to call 'Joe' from a user form. let's say you create a popup form called 'LookFor' - have a combobox here that is looking at the table of executive names....

in your query then instead of hard typing 'Joe' - replace that with the call syntax:
Forms!LookFor.ComboBoxName

A word of caution on comboboxes is to be sure that the 'bound' value is what is truly being searched in the query.

hope this helps.

[pbaldy]

It sounds like you have some design issues. You should generally not have repeating fields, rather a related table with a one-to-many relationship. If it's too late to change, in a query or filter you would use OR:

SELECT...WHERE Exec1 = 'John Smith' OR Exec2 = 'John Smith' OR...

[HiTechCoach]

Dan,

to help you we really need to have more information about the table structure.

Please post the table structures. If possible, a sample database with the tables and some sample data.