"All" option on a combo box, with Inner Join

[SteveC24]

Hello,

I have used various guides on here in the past to create an "(All)" option in combo boxes that I use to filter results displayed on continuous forms for many years.
Usually that's fine, but today, I am trying to do the same thing, but on a slightly more complex piece of SQL... and it's hurting my head.

I have this, which is working very nicely to filter my form:

SELECT tblClothingBrand.ClothingBrandID, tblClothingBrand.BrandName
FROM tblClothingBrand
    UNION Select "*" as AllChoice,"(All)" as Bogus
    FROM tblClothingBrand
ORDER BY tblClothingBrand.BrandName;

However, I have another SQL query which nicely displays a shorter, more useful list that I would like to use to filter by:

SELECT tblClothingBrand.BrandName 
FROM tblClothingItem 
   INNER JOIN tblClothingBrand 
   ON tblClothingItem.BrandID = tblClothingBrand.ClothingBrandID 
   WHERE (((tblClothingItem.CollectionID)=[Forms]![frmClothingCollection]![ClothingCollectionID])) 
GROUP BY tblClothingBrand.BrandName 
ORDER BY tblClothingBrand.BrandName;

How would I go about putting an "(All)" option, as works in the top SQL, into the bottom SQL? I have scratched my head, and tried various options, but all I can generate is errors!

Please help?!

 

[arnelgp]

why would you put "All" on a Filtered Query?

 

[Mike Krailo]

Your filtering a form and doing an aggregate at the same time. It's much better to open a main form based off of BrandName instead with a subform showing ClothingBrandID records. Then you get automatic form filtering based on the BrandName selected. Open another form that shows all the individual records as desired and has your original ability to filter those individual records or ALL records. No need for the All option on the separate form view based on BrandName.

 

[SteveC24]

These queries are the row source for a combo box, which sits in the Form Header for a continuous form. By default the form opens up and displays all records - but on occasion, it would be useful to display only some records from the underlying dataset.

The first piece of SQL lists all the "Brand Names" of clothing. The second piece of SQL lists only those Brands that are represented in the current data set (per client).

I'm not sure that makes a great deal of sense... but hopefully enough of the above does, to help?!

 

[Mike Krailo]

These queries are the row source for a combo box, which sits in the Form Header for a continuous form. By default the form opens up and displays all records - but on occasion, it would be useful to display only some records from the underlying dataset.

That makes sense, but it's better to handle those times when you need to filter by BrandName with another form altogether as I already described.

 

[SteveC24]

That makes sense, but it's better to handle those times when you need to filter by BrandName with another form altogether as I already described.

OK - that's a shame... but far from impossible.

Thanks!

 

[GPGeorge]

These queries are the row source for a combo box, which sits in the Form Header for a continuous form. By default the form opens up and displays all records - but on occasion, it would be useful to display only some records from the underlying dataset.

The first piece of SQL lists all the "Brand Names" of clothing. The second piece of SQL lists only those Brands that are represented in the current data set (per client).

I'm not sure that makes a great deal of sense... but hopefully enough of the above does, to help?!

For performance reasons, I think it's better to open the form with a filter in place and only let users see all of the records in the recordsource if they really, really, REALLY want to see them all. I run Access/SQL Server applications for the most part, where performance is critical and this is one way to avoid slowing down a form opening.

See this video, for an example.

At the risk of repeating one of the several errors you've already raised in testing (but not shown us ), here's one attempt at creating the union you need for the "All" option.

SELECT tblClothingBrand.BrandName As BrandNameSort
FROM tblClothingItem
INNER JOIN tblClothingBrand
ON tblClothingItem.BrandID = tblClothingBrand.ClothingBrandID
WHERE (((tblClothingItem.CollectionID)=[Forms]![frmClothingCollection]![ClothingCollectionID]))
GROUP BY tblClothingBrand.BrandName
UNION SELECT " All" AS BrandNameSort
FROM tblClothingBrand
ORDER BY BrandNameSort

Note that I put a space in front of the " All" option to ensure it sorts to the top of the list.

 

[SteveC24]

Thank you so much, @GPGeorge - that produced a "unioned" list, for sure, without errors - but it lost me my column containing "ClothingBrandID", which meant my form Recordsource, which was expecting a "*" for the All option stopped working. My RecordSource uses this as the criteria on the BrandID field:
Like Nz([Forms]![frmClothingCollection]![frmClothingItem].[Form]![comFilterByBrand],"*")

So I fiddled around with what you'd written, and adjusted it slightly to the below, which has got me my two columns back, and it's all working beautifully!

SELECT tblClothingBrand.ClothingBrandID, tblClothingBrand.BrandName As BrandNameSort 
FROM tblClothingItem 
INNER JOIN tblClothingBrand 
ON tblClothingItem.BrandID = tblClothingBrand.ClothingBrandID 
WHERE (((tblClothingItem.CollectionID)=[Forms]![frmClothingCollection]![ClothingCollectionID]))  
UNION SELECT "*" AS ClothingBrandID, "(All)" AS BrandNameSort 
FROM tblClothingBrand 
ORDER BY BrandNameSort;

Now I've done that - and just written it out, I realise that I could have just put the criteria on the BrandName field in the RecordSource instead... but hey ho - it was good to rifle through your SQL to understand it (a little) better!

It's only me who uses this database, and it's never going to hold vast amounts of data, so performance isn't really much of a concern. It's more important that it works in the way I want it to, to aid efficient use.

Thank you for your assistance, everyone.

 

[Gasman]

I would have used 0 as ClothingBrandID ?
You will never get 0 in an autonumber.

 

[GPGeorge]

I would have used 0 as ClothingBrandID ?
You will never get 0 in an autonumber.

Agreed, I use 0 as the default for "All" consistently.

 

[CJ_London]

I tend to use null for all so the criteria becomes

Somefield=nz(formcontrol,somefield)

 

[SteveC24]

I would have used 0 as ClothingBrandID ?
You will never get 0 in an autonumber.

That's very sensible, and not (clearly!) something I had thought about before. I will give that a go next time!

I tend to use null for all so the criteria becomes

Somefield=nz(formcontrol,somefield)

I stumbled across this on another field later in the evening, and found that worked quite nicely too.

Thanks again all!