Dynamic Query in a Form

[mchoukeir]

I'm wondering if any of you Access gurus can help me with a Form problem I have...

I'm trying to set up a form that allows users to filter data in a database and to show them some totals for that data in the footer. It'll be a bit like a query on a table, but the user can determine the field parameters in real time. What's the best way to do this?

Also, I know how to use functions to calculate totals from the form data, but is there a function that allows for the grouping of certain data to give a unique count...here's an example to help articulate that point a bit better.

Lets say the filter results give me the following output:

London
London
Milan
Paris


If I do a simple count, I'll get 4, but I want the unique count which should give me 3, and I'm not sure how best to do this in a function.

Hope you can shed some light into this...

thanks

 

[casey]

2nd ?...try select DISTINCT... in your query to give you unique values...

Your first ? can be achieved several ways depending on the complexity you're comfortable with. I personally would try using a datasheet form with totals on the bottom. Then the user can filter the form by clicking on the fields and then right-clicking giving them the filter options (depending on version of Access you're using). Once filtered, your totals on the bottom will update. It's not a great way, but it's simple, dynamic and user-driven.
Other wise you could create a query based on textboxes or comboboxes on your form giving the user specific options. This may require VBA and would be time consumming.

Just some thoughts...