query of a query

[Bladerunner]

I have a combo named cbogroup. I have a tblGroup with several records (active, non-active, nursery, etc.). One of the records is *ALL*.

Using the CboGroup the user can pick any of the records. Howeverr, if they pick the *ALL* record, I want the query to pull up animalID based on all records in the TblGroup. If another record is picked (i.e. nursery), then the query will pull up only animalIDs that are in the 'Nursery'. Can I put a (iff then) in a query in order to differentiate a query based on all group records or a query based on only one record?

This can be done in code but not sure about SQL.!

Thanks for the help

Blade

 

[smig]

You can use iif()
Its a function replacing the If Then

 

[Bladerunner]

Yeah, been playing with it since I wrote that post. Trying to use the expression builder a little more than just using code. It saves some time and helps get the syntax right.

I think I can get by without using the IIF statement. maybe.

Thanks for the info.

Blade

 

[Simon_MT]

If your Groups are the same length then Group like cboGroup & "*". If no Group then all Groups or partial or full search on Group.

Simon

 

[Bladerunner]

thanks Simon for your input. Will see if it fits what I am trying to do.

Thanks

Blader

 

[essaytee]

I have a combo named cbogroup. I have a tblGroup with several records (active, non-active, nursery, etc.). One of the records is *ALL*.

Using the CboGroup the user can pick any of the records. Howeverr, if they pick the *ALL* record, I want the query to pull up animalID based on all records in the TblGroup. If another record is picked (i.e. nursery), then the query will pull up only animalIDs that are in the 'Nursery'. Can I put a (iff then) in a query in order to differentiate a query based on all group records or a query based on only one record?

This can be done in code but not sure about SQL.!

Thanks for the help

Blade

Just throwing this out there, and it's all about options and alternatives.

Have you considered two queries. Within the click event of the Combo Box, check whether the criteria is "All" or something else.

If "all" run query1 (no references to any parameters) or run query2 (what you have now).

Take this one step further, if you don't want to have two queries, the query could be dynamically created on the fly, again you check whether it's all or none and the SQL statement is created. Using this particular method, the body of the SQL statement is more or less fixed, it's only the WHERE clause that is manipulated. I'm not sure whether this is what your referred to as, this can be done in code but not sure about SQL.

Just a thought (option).

 

[Galaxiom]

WHERE animalID = Forms!formname.cboGroup OR Forms!formname.cboGroup = "ALL"

 

[Bladerunner]

Ok, I see we are all over the place here. Sorry, it is my fault. My ability to explain what I need is wanting to say the least. Let me give you the whole story so it will make sense.

SQL that Works:
SELECT [tblAnimal Setup].AnimalSetupID, [tblAnimal Setup].AnimalID, [tblAnimal Setup].AnimalType, [tblAnimal Setup].Group, [tblAnimal Setup].location FROM [tblAnimal Setup] WHERE ((([tblAnimal Setup].AnimalType)=[cboanimaltype])) and ((([tblAnimal Setup].Group)=[cbogroup])) and ((([tblAnimal Setup].Location)=[cbolocation])) ORDER BY (animalID)

Have 4 CBOs Animaltype, Group, Location and AnimalID.

Now based upon the info from the first three cbos, the AnimalID pulls up all animals that equal those parameters. (i.e. Beef Cattle, Active and Pen513). AnimalID will pull every animal of or from the type (Beef Cattle) that are in the group (Active) and are located at (Pen513). This part works well. see the SQL above.

In the Tables Group and Location among other tables, there is a *ALL* record in each. The '*' serves the purpose of placing the *ALL* at the top (ascending order) ONLY when used in list,cbo,etc.. While each animalID has a group and location, It cannot be *ALL*. This will be relegated to controls that pull up these tables (i.e group, location, etc.) ONLY!

Effectively by using the *ALL*, the Group and/or Location CBOs would be 'Skipped'. Thereby, the AnimalID for every Beef Cattle would be found reguardless of what group or what location it would happen to be attached to. A total Inventory of Beef Cattle so-to-speak on the main Navigation Page at anytime.

With that in mind:

Someone mentioned having different SQLs which is all fine a good if I was using IIF statements as you would in VBA. How would you add a IIF statement to the AnimalID cbo without adding another cbo or diminishing the task it already does. Keep in mind that if this works other shortcuts (i.e *New* ..pull up the tbl form for on the fly additions to table followed by requery, etc.) could also be used either in addition or in anothers' place.

**Adding new cbo or other type buttons,etc. is not an option .***

Thansk for your help and hope you all have a safe and prosperous NEW YEAR!.
Blade

 

[Galaxiom]

The conditon similar to what I posted will do as you describe will bypass the other conditions if you add it to the end.

OR [cboGroup] = "*ALL*"

If the combo = "*ALL*" then it doesn't matter what is in the other conditions.

You can also have ALL in the other combos. Simply use the appropriate And/OR logic and bracketing to make it do what you want.

BTW You appear be using the unqualified references to the objects on the form. Not a good practice as it will make Access guess and you don't want that.

 

[Bladerunner]

The conditon similar to what I posted will do as you describe will bypass the other conditions if you add it to the end.

OR [cboGroup] = "*ALL*"

If the combo = "*ALL*" then it doesn't matter what is in the other conditions.

You can also have ALL in the other combos. Simply use the appropriate And/OR logic and bracketing to make it do what you want.

BTW You appear be using the unqualified references to the objects on the form. Not a good practice as it will make Access guess and you don't want that.

OK, did not understand that was what you where pointing at. apologies. However, the last BTW about the 'unqualified" references.. Care to expand here. I am missing this one as well.....I would like to correct it if I can?

Thanks for your help

Blade

 

[Bladerunner]

Galaxiom:


I have looked at that and don't really understand how that would work. If no animal has a group of ALL then it would throw them out. I think?

SELECT [tblAnimal Setup].AnimalSetupID, [tblAnimal Setup].AnimalID, [tblAnimal Setup].AnimalType, [tblAnimal Setup].Group, [tblAnimal Setup].location FROM [tblAnimal Setup] WHERE ((([tblAnimal Setup].AnimalType)=[cboanimaltype])) and ((([tblAnimal Setup].Group)=[cbogroup])) or ((([tblAnimal Setup].Group)= "ALL")) and ((([tblAnimal Setup].Location)=[cbolocation])) or ((([tblAnimal Setup].Location)= "ALL")) ORDER BY (animalID)

Thanks again for your time

Blade

 

[Galaxiom]

the last BTW about the 'unqualified" references

I didn't look close enough and probably jumped to the wrong conclusion

I assume your SQL is in the RowSource of a combo or listbox or the RecordSource of the form where it will use the control name straight from the form.

It was unfamiliar because I have never done it that way. I generally use the Filter property. Now that I have seen it I might try it your way too.

 

[Galaxiom]

I have looked at that and don't really understand how that would work. If no animal has a group of ALL then it would throw them out. I think?

The condition usually tests the fields in the recordsource.

The catch ALL tests the content of the combo itself without regard to the recordsource.

Each part of the Where clause is tested separately and returns True or False for each record. Regardless of the vaule in the field the combo= "*ALL*" will return True and making the whole Where condition True because of the OR.

When that part returns False the condition reverts to the selection by combo match.

 

[smig]

this is not what Galaxiom wrote:

[tblAnimal Setup].[Group]=[cbogroup] or [tblAnimal Setup].[Group]= "ALL"

he wrote:

[tblAnimal Setup].[Group]=[cbogroup] or [B][cbogroup][/B]= "ALL"

You check the field against the ComboBox but you also check the Comboox itself.

As all fields are from the same table you can remove the [tblAnimal Setup] from most parts of the code. You only need it in the Where clues.
You can also remove most of the brackets.
This will code much more readable.

 

[Bladerunner]

this is not what Galaxiom wrote:

[tblAnimal Setup].[Group]=[cbogroup] or [tblAnimal Setup].[Group]= "ALL"

he wrote:

[tblAnimal Setup].[Group]=[cbogroup] or [B][cbogroup][/B]= "ALL"

You check the field against the ComboBox but you also check the Comboox itself.

As all fields are from the same table you can remove the [tblAnimal Setup] from most parts of the code. You only need it in the Where clues.
You can also remove most of the brackets.
This will code much more readable.

Hi smig. First, Thanks for your advice and Happy New Year! I constructed the SQL directly from the design view. Access actually put in the brackets and TBLAnimal Setup. I however, agree with you that it would indeed make it easier to read without them.

I did not pick up on the checking of the [CBOGroup] = "ALL" control itself although this is what is needed. I will try it ands see if it works.

Blade

 

[Bladerunner]

The condition usually tests the fields in the recordsource.

The catch ALL tests the content of the combo itself without regard to the recordsource.

Each part of the Where clause is tested separately and returns True or False for each record. Regardless of the vaule in the field the combo= "*ALL*" will return True and making the whole Where condition True because of the OR.

When that part returns False the condition reverts to the selection by combo match.

Galaxiom: Thanks and Happy New Year. Would it be prudent to place the '[cbogroup]="ALL" ' first in the sql. ' [cbogroup] = "ALL" or tblanimalgroup.group = cbogroup '

This get confusing. I will work on this ans see how I can get it to fit. I seem to have a similar problem in that if I pick a group in the cbogroup where there are no animals, then the cboanimalID shows nothing. While this is correct, an error box here telling the user there are no animals in this Group, resetting itself and going back to cbogroup to get another would help. I still may have to reference the rowsource of [cboanimalID] and simply code this in instead of using an sql?

Thanks Again

Blade

 

[Bladerunner]

Have gone back and tested this addition to the SQL. It did its job. It forced the final cboAnimalID to pull up NO animals available. Exactly the opposite of what is needed.

and ((([tblAnimal Setup].Group)=[cbogroup])) or ((([cbogroup])= "ALL")) and

Because the sql cannot test itself and change its needed value, there will almost certainly have to be a IIF statement with multiple sqls. example: IIF [cbogroup]="ALL" then its sql would only check the animaltype and then the cbolocation. Thus all animals in this location would be shown in the cboanimalID control. ELSE cbogroup control would use the present sql.

This has to happen for cbolocation as well so you would have multiple IIF statements back-to-back.

Is this possible to write in the rowsource/other areas of the combo control?


Happy New Year!
Blade

 

[Bladerunner]

GAlaxiom, I was thinking more on the idea of the following. I tried it but keep getting 'no recordsource' error probably because of bad syntax.

IF [cboGroup]="*ALL*" then

SELECT [tblAnimal Setup].AnimalSetupID, [tblAnimal Setup].AnimalID, [tblAnimal Setup].AnimalType, [tblAnimal Setup].Group, [tblAnimal Setup].location FROM [tblAnimal Setup], WHERE ((([tblAnimal Setup].AnimalType)=[cboanimaltype])) and ((([tblAnimal Setup].Location)=cbolocation)) ORDER BY (animalID)

else

SELECT [tblAnimal Setup].AnimalSetupID, [tblAnimal Setup].AnimalID, [tblAnimal Setup].AnimalType, [tblAnimal Setup].Group, [tblAnimal Setup].location FROM [tblAnimal Setup], WHERE ((([tblAnimal Setup].AnimalType) = cboanimaltype)) and ((([tblAnimal Setup].Group)= cbogroup)) and ((([tblAnimal Setup].Location)=cbolocation)) ORDER BY (animalID)

endif

Blade

 

[essaytee]

GAlaxiom, I was thinking more on the idea of the following. I tried it but keep getting 'no recordsource' error probably because of bad syntax.

IF [cboGroup]="*ALL*" then

SELECT [tblAnimal Setup].AnimalSetupID, [tblAnimal Setup].AnimalID, [tblAnimal Setup].AnimalType, [tblAnimal Setup].Group, [tblAnimal Setup].location FROM [tblAnimal Setup], WHERE ((([tblAnimal Setup].AnimalType)=[cboanimaltype])) and ((([tblAnimal Setup].Location)=cbolocation)) ORDER BY (animalID)

else

SELECT [tblAnimal Setup].AnimalSetupID, [tblAnimal Setup].AnimalID, [tblAnimal Setup].AnimalType, [tblAnimal Setup].Group, [tblAnimal Setup].location FROM [tblAnimal Setup], WHERE ((([tblAnimal Setup].AnimalType) = cboanimaltype)) and ((([tblAnimal Setup].Group)= cbogroup)) and ((([tblAnimal Setup].Location)=cbolocation)) ORDER BY (animalID)

endif

Blade

Hi Bladerunner, firstly, why aren't you out for New Year's Eve? Mine's over and done with, now 1.20 pm here in Australia 1 Jan 2015.

Anyway, not directly answering your question regarding the recordsource but I'm confused regarding your SQL if the [cboGroup] = All. If you are returning all records I can't see the point in applying any WHERE criteria. Select your fields and then apply your ORDER BY clause.

The SQL is returning two AnimalID's in both statements and no ending semi-colon. Other than that I haven't dissected or tested a similar SQL on my machine.

As regards the returning error, is that occurring for both SQL statements, therefore regardless of whether ALL is selected or not.

Anyway, turn the computer off, go out and have a great night.

 

[Bladerunner]

Thanks essaytee for the advice.

These days I prefer watching others having a good time. Lets just say it is easier to get up the next morning.

You have some great fireworks down there.

I am trying to force the query of three cbos into one cbo along with all the Xs and Os. If you will read back a little further, you will understand, I think?.....

Blade