Filtering table depending on criteria

[Guest]

Hi,

I have a table with Country, City, and xxxx info. I would like to create a
subset of this table using the Make Query. If the user chooses a Country,
then it will make a subset for that country, but if the user chooses a city,
it will then make a subset of that city. At the end I should only result in
one filtered table (same name).

How can this be done?

Thanks,
Carmen

 

[Jeff Boyce]

Carmen

You've described a HOW (make a subset with a Make Table query), but not a
WHAT/WHY.

Why do you think a Make Table query is the solution, and what are you trying
to solve?

I ask because there may be other solutions...

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[Guest]

Hi Jeff,

Country and City fields are just an example. I'm creating a generic
database that can be shared across the country. So I'm thinking of a
"initial setup" section that allows user to specify the Province or City.
This is then used to filter and make a table of related info. (The source
data applies for the whole country).

The table (filtered data) is made and then other queries are performed on it.

Initially, I had a table for Province and another one for City. I would
allow user to either populate this one. Then every time when new data comes
if, "Filtered" table will have Province specific or City specific data.

I've tried a conditional macro, but I don't know how to make it check the
Province/City tables. The first field to see if it's populated and then run
the specific make table query.

Any help on this would be appreciated.

Thanks,
Carmen

 

[Jeff Boyce]

Carmen

You can build a query on a query. If your initial query filters to a
specific Province and City, then a second query, based on the first, lets
you do further filtering.

Another approach would be to create a form you use to gather the user's
filter requirements, and you then dynamically build a SQL statement in code
behind the form. That SQL does the filtering.

Or even using the Access "Filter by Form" capabilities, if that suffices.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[Guest]

Hi Jeff,

My concern is that the city may or may not be populated. Is there a way to
set the criteria so if the city table is blank, then don't filter for
anything?

Thanks,

Carmen

 

[Jeff Boyce]

Carmen

"blank" and "null" could be two different conditions, depending on whether
zero-length strings are allowed.

A query that uses, as a criterion for City, something like:

... (your criteria) Or Is Null Or ""

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/