Creating a flexible query

[Guest]

I'm using Microsoft Access 2003. I have a table of jobs done by my company.
It's linked to other tables that list the options for customer name, service
rendered, etc. I'm looking to create a query that can be regularly accessed
by my co-workers where they can open it, choose what info they want to limit
their results by using pull down menus that relate back to those other tabes,
and I want them to be able to pick 1 or more categories. SO, for example,
someone would open the query and a window would pop up with all the different
categories of info available for each job. They would go to 'customer name'
and use the pull down menu to pick 'AAA Consulting', then go to 'service
renedered' and use the pull down menu to pick 'accounting services'. They'd
then leave the rest of the fields blank, hit enter, and get a list of all the
jobs that match those two criteria. I know how to make a standard query, but
I can't figure out these more complicated aspects of drop down lists, promts
for info, etc. I also don't know if in this instance a query is even the
best way to do this, but I'm not familiar enough with the program to know.
So how do I create this easy way for my co-workers to be able to access info
just for the projects they're looking for?

 

[storrboy]

I'm using Microsoft Access 2003. I have a table of jobs done by my company.
It's linked to other tables that list the options for customer name, service
rendered, etc. I'm looking to create a query that can be regularly accessed
by my co-workers where they can open it, choose what info they want to limit
their results by using pull down menus that relate back to those other tabes,
and I want them to be able to pick 1 or more categories. SO, for example,
someone would open the query and a window would pop up with all the different
categories of info available for each job. They would go to 'customer name'
and use the pull down menu to pick 'AAA Consulting', then go to 'service
renedered' and use the pull down menu to pick 'accounting services'. They'd
then leave the rest of the fields blank, hit enter, and get a list of all the
jobs that match those two criteria. I know how to make a standard query, but
I can't figure out these more complicated aspects of drop down lists, promts
for info, etc. I also don't know if in this instance a query is even the
best way to do this, but I'm not familiar enough with the program to know.
So how do I create this easy way for my co-workers to be able to access info
just for the projects they're looking for?

It's not easy (relative to a regular pick a couple of values query).
There are a lot of factors to consider - did they choose a catagory
but not supply a value; is this a date, number string or something
else; does the criteria have to match or can it be greater than,
between, empty. I'll try to outline somethings that I have done, but
it may take a while. In the mean time, start by outlining what people
can search by, what operators (=,>,Between) you would allow, where the
searchable fields come from and their data types. This will help give
an idea of how flexible it needs to be.

 

[Guest]

Is this what you want?
http://img159.imagevenue.com/img.php?image=28703_InentoryFilter_122_261lo.jpg

Sry, it's in Czech but I hope you've got the idea. Also see Also see "Pick
list for report" - a post in this thread by ksr (March 5th 2007 1:40).

Vlado

 

[Guest]

P.S.
The form was designed to create an SQL for inventory - the "Zobrazit
záznamy"-button click runs a procedure which combines all selections &
values in additional fields... and then opens a form based on the SQL.

 

[Guest]

SQL = SQL-string (query)

P.S.
The form was designed to create an SQL for inventory - the "Zobrazit
záznamy"-button click runs a procedure which combines all selections &
values in additional fields... and then opens a form based on the SQL.

 

[Guest]

For every job entered the following fields can/will be filled in:
1. Organization name - they choose from a drop down list linked to an
'organization table' that has all the organization's info
2. Job Date - this is specified on the form to just be year, but the data
type is text
3. Service Type 1 - they choose from a drop down list linked to a 'service
type table' that has all the possible services offered
4. Service Type 2 - same as above - for when more than 1 service is renderede
5. Service Type 3 - same as above
6. Service Type 4 - same as above
7. Fee - data type is currency
8. Employee 1 - They choose the name of the person who did the job from a
drop down list of all our companies employees, linked to an 'employee table'
9. Employee 2 - Same as above

I want them to be able to search by
-organization name
-year (or to be able to search a span, ie 2002-2004)
-service type (and if they search for 'consulting', I'd like any project to
come up that had consulting as a service, whether it was in position 1,2,3 or
4)
-fee (I also want them to be able to search a range for this, even though
the fee's inputted are exact)
-employee (again, if they search for 'Jamie Smith' I want all jobs Jamie
worked on to show up, whether he was listed as employee 1 or 2)

I'd also like them to be able to choose multiple search requirements, like
all consulting projects Jamie worked on between 2002-2004

Lastly, I'm hoping that all the matching projects can appear in a table (as
opposed to in a form where they have to click to see the next result)

I hope this is the information that you requested.