Query by form groups

[Dazza666]

Hi,

I have a load of queries set up and a form that accepts building name, start date and end date, all queries get criteria from the form.

I need a way of using the drop down box for more than one building i.e. an option of 'CANADA' meaning all buildings in canada. I figure i could do this if i could add some additional items to the drop down list. The problem is the drop down list is populated via a table "bulidlings" and I need the value of the drop down selection to say something like =building 1 or building2 to or building 3 etc.

Is there a way i can do this, sorry if this makes no sense i'm struggling to articulate.

thanks

 

[Dazza666]

Ok I might be on to something,

I've disabled the limit to list property and creeated an option group that depending on the option selected changes the value of the drop down to something that resembles a where statement i.e. "'building 1' or 'building 2'"


the code works but the query by form doesn't. For some reason it doesn't accept it as multiple criteria.

Any help is much appreciated

 

[Scooterbug]

Sounds like you might have a normalization problem. The reason why I say this is because of this:

table "bulidlings" and I need the value of the drop down selection to say something like =building 1 or building2 to or building 3 etc.

If you have some kind of record that has multiple fields like Building1, Building2, Building3, etc then you have a normalization issue.

This kind of relationship is called a One (One main record) to Many (Many different building names). To set this up properly, you need to create a table that houses at least the following:

tblBuildingNames
BuildingNameID (Primary Key)
MainTableID (Foreign Key - Stores the PK of the record in the main table that references the buildings)
BuildingName

You can then create a combo box based off of the table. You can then tie the data you want to retreive via the PK/FK relationship.

How exactly is the table setup?

 

[Dazza666]

Sounds like you might have a normalization problem. The reason why I say this is because of this:

If you have some kind of record that has multiple fields like Building1, Building2, Building3, etc then you have a normalization issue.

This kind of relationship is called a One (One main record) to Many (Many different building names). To set this up properly, you need to create a table that houses at least the following:

tblBuildingNames
BuildingNameID (Primary Key)
MainTableID (Foreign Key - Stores the PK of the record in the main table that references the buildings)
BuildingName

You can then create a combo box based off of the table. You can then tie the data you want to retreive via the PK/FK relationship.

How exactly is the table setup?

Hi thanks for responding,

the building table is only a used like a lookup table and can be used by multiple clients at once. it is only used to provide rent information and the building name for drop down lists so it only has 1 record for each building the table looks like:

ID
BuildingName
Rent

the main table (client) has a building field which is linked via a one to many relationship. We use queries and reports to generate statistics on clients and by building. This all works fine.

The problem is we now need to generate stats on all buildings in a certain area this means when using QBF selecting a building name from a drop down box is not enough. We could pull the stats for all buildings individually but thats quite long winded. If i enter criteria directly in to the query i.e. =building1 or building2 it works fine. But entering manually is not practical for staff so I need the query form to put the criteria in for me.

The criteria in each query is currently set to [FORMS]![STATISTICS]![cboBuilding] which is the combo box on the QBF form


thanks

 

[Scooterbug]

I think I get it....

You want to use criteria for multiple buildings and want to be able to choose them with a combo box. Something like this:

WHERE Building = 'Canada' OR 'North America'

I can think of a couple of ways to do this. One, you could have a temp table that stores the data for you. You can use the combo box to select the building, then a command button that appends the data to the table. You can then use the table in the query. When you are done, use a delete query to clear out the table.

Second thing off the top of my head is to have an unbound text box on the form. Have the combo box with the buildings and a button. Select the building, click a button and the building is copied to the text box. Select a second building, click the button and it adds that building to the end of the text in the text box. You can then use that as the criteria for the query.

 

[DCrake]

You can also use the In() command as well. The only problen you have is selecting more than one building type from a combo box. If you change your combo to a list box and make it multi select then you can parse through the selected items and en up with a string like..

In("Canada","North America","Iceland","Mars","Etc")

David