Forms that filter

[Mackem Nat]

Hi to anyone and everyone

I am pretty new to Access and am quickly losing patience with my "dummies" book.

I have a set up a database for someone else and they don't like using the filter form function in the database and have asked for form that does the same job and mentioned "query builder" ?!?!?!

Ideally I want to create a new form, you enter the search profile for all the fields (some yes/no's, some drop down lists) hit a command button, and hey presto up pops a report with the total number of records that match your profile.

Basically just like a filter. The reason for not saving a specific filter is 'cos the profile has loads of combinations and can change.

Can anyone give me a few tips on how to start please

 

[ajetrumpet]

you enter the search profile for all the fields

When you say "search profile", do you mean to say "search criteria"??

hit a command button, and hey presto up pops a report with the total number of records that match your profile.

You need to create a simple criteria query and link your command button to this. Using criteria is the substitute for a "fixed" filtered data query, and is good when you need to filter out different records at different times.

Do you know how to go about doing this?? If you play around with the query grid for awhile, you'll get it.
If you need helping setting up, I could point you in a direction or two.

By the way, WELCOME.

 

[Mackem Nat]

Cheers the welcome and for the quick reply but.................

I have already added a few queries with parameters so I have an idea how this works but this seems more complicated for 2 reasons.

1/ The criteria can chenge for each of the fields (and I have over 30). Some are just yes/no changes, but for some fields I have added up to 12 options in a drop down menu. Therefore the possible number of combinations across all the fields is huge. I used the phrase "profile" to describe one of the many combinations.

2/ I am not the end user of the database so I need to set up a very user freindly form that they select the options they need for each field and then the hey presto bit.

I have considered user a form which addes the chosen profile to a 2nd table, then using a macro to copy the data from the table to criteria sections to query the main table, or even useing the macro to copy selections to the filter some how ? Either way seems very complicated.

Any clues welcome, and if I mis-interpreted your answer I obviously need a bigger hint !!

 

[ajetrumpet]

I have considered user a form which addes the chosen profile to a 2nd table, then using a macro to copy the data from the table to criteria sections to query the main table, or even useing the macro to copy selections to the filter some how

First of all, this sentence is contradicting itself.
"the chosen profile" refers to a record that satisfies criteria (per your previous post). Once criteria is chosen, there is nothing else that needs to happen, other than linking your report to the query.

The criteria can chenge for each of the fields (and I have over 30). Some are just yes/no changes, but for some fields

I have added up to 12 options in a drop down menu

. Therefore the possible number of combinations across all the fields is huge

The number of combinations you can have as far as criteria is concerned has nothing to do with how you write a query to filter data - it is written the same way (it may slow the retrieval process down a bit though). If you have that many fields to get data from, no form is going to be user friendly.

Here is the SQL I would use to choose records in a table that only had 3 fields to choose from (lets say for simplicity, the choices are being made from parameter box popups, )....

[color=darkorchid]SELECT [field1], [field2], [field3]
FROM [table]
WHERE [field1]=[parameter1] AND [field2]=[parameter2] AND [field3]=[parameter3]

[/color]

Sounds like you have more than one table...how many??
Are they properly related??
Do the users need to use all of them to get the data they need??

If the tables are properly related, a single query can retrieve a million records if you want it to, but the user has to tell the program what is needed, Access can't read peoples' minds.

I think this is much simpler than you think...

 

[Mackem Nat]

Thanks for your patience so far, I think I understand but it isn't crystal yet.

At the moment I have one form which lets say is asking:
Name
Age
Gender
Date the form was completed
Address (Country & Town)
And Sports they like to play.

Some answers like gender are obvious male/female (yes/no)
Others like County and Town have a drop down menu but the "person" is only going to select one answer.
Sports has a lot of answers, ie football cricket etc. I have set up each possible as a Yes/no tick box and the "Person" can tick as many as they want.

Some key data is easy to query and report on. However the end user of the database would like a search function which he can open up as a form and ask "How many men, live in Birmingham and play netball ?" by selecting options (previously I called this generating a profile). 10 minutes later he may change his mind and want to know how many women and men play netball in the West Midlands for example.

As the end user needs this flexibility, and can't generate queries with criteria I need something flexible. From looking at other posts I have started to think about a query with criteria something like below for the gender bit and so on for each field:

Like "*" & [Forms]![Database Search].[Gender] & "*"

Does this better expain the problem ? Have you already answered it ??
Oh yes, and all the data is stored in one table

Please tell me this is simpler than I think again ?!?!

 

[ajetrumpet]

the end user of the database would like a search function which he can open up as a form and ask "How many men, live in Birmingham and play netball ?"

Questions like this are answered by one of two methods...
1) Writing separate queries for each question.
2) Writing one query that has the CAPABILITY to answer every possible question (every criteria combination possible)

The syntax you have listed above is the one to use (Like "*" and....etc....)

I will get you going on it...below is the simple syntax for a user to select their choice or combination of choices for criteria based on the query (using the first 3 fields from your last post)...

SELECT [NAME], [AGE], [GENDER]
FROM [TABLE]
WHERE ([NAME]=Nz(Forms![Database Search]![Name])
AND [AGE]=Nz(Forms![Database Search]![Age])
AND [GENDER]=Nz(Forms![Database Search][Gender]))

If the end users will change their minds frequently about what question that want to ask the program, you should include every single field that you have in your table on your form. And yes, to give the user every option they want, all you have to do is multiply the above code for 3 fields * the 30 fields that you have.

Trust me, it could be worse....

 

[Mackem Nat]

Prince among men (or princess and women)

Cheers

I have had a play and been able to get both methods working with a limited number of fields. Still a few bugs to iron out on the full blown version but definately on the right track.

Thanks for your help