Using a Query Based Form

[Access Fellows]

I'm working in Access 2003
I have a table of projects done by my company, which includes
-organization name
-project year
-service 1 provided
-service 2 provided
-service 3 provided
-service 4 provided
-project fee
-project consultant 1
-project consultant 2
-project consultant 3

I've created a Query Based Form where you can enter in any combination of search criteria (e.g. org name and btwn 2001-2004) and the button press runs a query that returns a table of projects that match all the criteria you've entered.

I've figured out (I have no formal Access or programming training) what to set the criteria as in the query so you can get info by entering org. name, project year (with start and end dates) and/or project fee (with bottom and top amts).

The issue that I'm having is that I want to be able to enter a consultant name into the Form, and have the query return any projects that have that consultant listed a either project consultant 1,2 or 3 - and the same idea for the service provided. Right now I can only get the query to look in the 'service 1 provided' and 'project consultant 1' columns.

I had a thought that I could run 3 different queries - 1 that will look at org, date and fee, a 2nd that will look at all consultants, and a 3rd that will look at all services - and then run a 4th that would give me a table that only showed projects that appeared in all 3 of the other queries. However I haven't been able to figure this out, nor do I know if it's the best way to achieve my desired result. Right now I have the 4th (using UNION) showing all projects that show up on ANY of the 3 queries, plus the button press makes all 4 query result tables pop up, and I'd like it if only the 4th (final) query produced a visible result.

Any ideas or help would be greatly appreciated. Thanks!

 

[dcobau]

first a few questions to you:
1) what happens if in a month's time you need to enter data for consultant4?
2) what happens if in 2 months time you need to enter data for service5?
3)etc.

Dave

 

[The_Doc_Man]

I strongly believe you have a denormalized database. If you have not done so yet, read up on normalization. Those "Service 1, service 2, etc." fields are what are sometimes called "Repeating Groups" - which is the first sign your database isn't even first-normal form. Google-Search for Normalization and ignore every article that didn't originate from a .EDU site or the .COM of a well-known database product vendor. I.e. well-known enough that YOU have even heard of them.

Once you normalize everything, you will find that searching and storing becomes a LOT simpler even when one organization only wants 4 services but another one wants 12. And when one organization has a single consultant while another has a brigade of consultants.

 

[Access Fellows]

After reading up on normalization, this database is definately not in 1st normal form. So now my question is this: in the info I read on normalizing, the examples were all based on the assumption that, with 2 bodies of info (e.g. Managers and Employers) one of them would only appear once on a table of the 2 items (it says things like "a manager can manage more than one employee, but an employee can only have 1 manager"). However in the case of my 'services' - each project can have more than 1 service, AND each service can be provided for multiple projects. SO what's the primary key? Based on my reading, it seems that I need to take out 'services' and 'consultants' and make a different table, which is fine, but I can't figure out what those tables should look like because of the problem I stated above.