Add row to combo box

[John]

I have a comb box with the following select statement in
the "Row Source" property,

SELECT [Diagnosis].[Diagnostic_group] FROM [Diagnosis]
WHERE [Diagnosis].[Diagnostic_group] <> "Edit_list"
GROUP BY [Diagnosis].[Diagnostic_group]

This works great except for reports I want to allow the
user to be able to select "All". The Diagnosis table does
not include an "All" record. Is there a way to add
an "All" to the combox while still using my select
statement above, without adding "All" to the Diagnosis
table?

Thank you for your assistance. John

 

[Graham Mandeno]

Hi John

Add in the "All" using a UNION query. Also, there is no need to use a GROUP
BY clause here - the DISTINCT predicate will ensure only one instance of
each value:

SELECT DISTINCT [Diagnosis].[Diagnostic_group] FROM [Diagnosis]
WHERE [Diagnosis].[Diagnostic_group] <> "Edit_list"
UNION
SELECT "All" FROM [Diagnosis]

Note that the last FROM [Diagnosis] could be any table - it's just there to
satisfy the syntax.

 

[Ron Weiner]

Yep! Use a union query to supply the [All] like this:

Select Distinct Diagnostic_group From Diagnosis Where Diagnostic_group <>
'Edit_list'
Union Select '[All]' From Diagnosis Order by 1

Ron W