criteria = null
WannaChevy03
form is the criteria for the query. However, some of the drop-down boxes and
text boxes are null (no data selected on the form). When I run the query, it
looks for fields that ARE in fact null. If an asterisk is put into the form,
it will look for all fields that are "*". Any suggestions?
KARL DEWEY
null? If the latter then use this for critieria --
[Your criteria] or Is Null
Wayne Morgan
If you cheat and set the criteria to True, then all records will be returned
because the result of the criteria is always True.
Example criteria:
=[Forms]![MyForm]![MyControl] Or [Forms]![MyForm]![MyControl] Is Null
--
Wayne Morgan
MS Access MVP
"WannaChevy03" <WannaC...@discussions.microsoft.com> wrote in message
news:F53208D7-2C32-4D28...@microsoft.com...
KARL DEWEY
Like [Forms]![YourForm]![YourDropBox] & "*"
If there is no selection for the box (assuming there is no default) there
will be a null concantenated with the asterick.
"WannaChevy03" wrote:
> Sorry, I don't really know how to explain this very well. The data that I am
> querying has no Null's. All fields have some sort of data in them. But on
> my form, I have 6 different drop-down boxes. Some users may only wish to
> pick 3 criteria's, instead of 6. If they leave the other 3 drop-down boxes
> blank, it looks for the corresponding data to be null, which it is not, so it
> doesn't bring it up as a result. Let me know if you would like screen shots
> or something?
>
> Thank You!
> Jessica
WannaChevy03
querying has no Null's. All fields have some sort of data in them. But on
my form, I have 6 different drop-down boxes. Some users may only wish to
pick 3 criteria's, instead of 6. If they leave the other 3 drop-down boxes
blank, it looks for the corresponding data to be null, which it is not, so it
doesn't bring it up as a result. Let me know if you would like screen shots
or something?
Thank You!
Jessica
WannaChevy03
Sincerely,
Jessica
Nurse Nancy
i have a form linked to a product table with a lookup field called 'rank''
and and a multi-selection lookup field called 'location'
i am using the data selected in those fields as criteria in a query.
If i leave either of the combo boxes blank (don't select anything) my query
returns no matches.
If I use your suggestion in this post ...
=[Forms]![MyForm]![MyControl] Or [Forms]![MyForm]![MyControl] Is Null
where do i put the first part, and where do i put the 'OR' criteria??
Right now I don't have any criteria because I am using the join on the table
as the matching criteria,,, hope this makes sense....pls help asap!!!
--
Nurse Nancy
KenSheridan via AccessMonster.com
in query design view, so in the Rank column's first 'criteria' row you'd put:
=[Forms]![MyForm]![Rank] Or [Forms]![MyForm]![Rank] Is Null
and in the Location column's first 'criteria' row:
=[Forms]![MyForm]![Location] Or [Forms]![MyForm]![Location] Is Null
where Rank and Location are the names of the combo box controls in the form
as well as being the names of their underlying fields. If the control names
on the form differ from those of the fields, e.g. cboLocation the use the
control name in the expression in the query.
You'll find that if you save the query and open it again in design view
Access will have moved things around a bit. Don't worry, the underlying
logic remains the same and the query will still work. If the query is saved
in SQL view the underlying logic is more evident and the SQL statement
doesn't change when the query is saved, e.g.
SELECT *
FROM MyTable
WHERE (Rank =[Forms]![MyForm]![Rank]
OR [Forms]![MyForm]![Rank] IS NULL)
AND (Location =[Forms]![MyForm]![Location]
OR [Forms]![MyForm]![Location] IS NULL);
Note how each Boolean OR operation is contained in parentheses to force it to
evaluate independently of the Boolean AND operation.
When 'optionalizing' parameters like this you should test for NULL in the
above way, not use the LIKE operator with the * wildcard character. NULL
LIKE "*" evaluates to NULL, not TRUE, so rows with NULL in the column in
question would not be returned as they would when testing the parameter for
NULL.
Ken Sheridan
Stafford, England
Nurse Nancy wrote:
>I know this is an old post, but i think i have the same issue, with a twist.
>
>i have a form linked to a product table with a lookup field called 'rank''
>and and a multi-selection lookup field called 'location'
>
>i am using the data selected in those fields as criteria in a query.
>
>If i leave either of the combo boxes blank (don't select anything) my query
>returns no matches.
>
>If I use your suggestion in this post ...
>=[Forms]![MyForm]![MyControl] Or [Forms]![MyForm]![MyControl] Is Null
>
>where do i put the first part, and where do i put the 'OR' criteria??
>
>Right now I don't have any criteria because I am using the join on the table
>as the matching criteria,,, hope this makes sense....pls help asap!!!
>
>> The query looks for results that will give a value of True in the criteria.
>> If you cheat and set the criteria to True, then all records will be returned
>[quoted text clipped - 11 lines]
>> > form,
>> > it will look for all fields that are "*". Any suggestions?
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access/200907/1
Nurse Nancy
help, I must be doing something wrong,, here is the code.
SELECT [PRODUCT TABLE].[Customer ID], [PRODUCT TABLE].ProductName, [RADIO
STATIONS TABLE].[Station Call Letters], [PRODUCT TABLE].[Target Audience
ID].Value, [RADIO STATIONS TABLE].[Market ID], [RADIO STATIONS TABLE].Rank,
[Rank Table].Rank, [PRODUCT TABLE].Rank, [PRODUCT TABLE].Rank
FROM [MARKETS TABLE] INNER JOIN ((([FORMATS TABLE] INNER JOIN [RADIO
STATIONS TABLE] ON [FORMATS TABLE].Format = [RADIO STATIONS TABLE].[Format
ID]) INNER JOIN [TARGET AUDIENCE TABLE] ON [FORMATS TABLE].Format = [TARGET
AUDIENCE TABLE].Format.Value) INNER JOIN ([Rank Table] INNER JOIN [PRODUCT
TABLE] ON [Rank Table].ID = [PRODUCT TABLE].Rank) ON [TARGET AUDIENCE
TABLE].[Target Audience ID] = [PRODUCT TABLE].[Target Audience ID].Value) ON
([RADIO STATIONS TABLE].[Market ID] = [MARKETS TABLE].[Market ID]) AND
([MARKETS TABLE].[Market ID] = [PRODUCT TABLE].[Market ID].Value)
WHERE ((([PRODUCT TABLE].ProductName) Like "*" & [Enter Product] & "*") AND
(([RADIO STATIONS TABLE].Rank)<=[RANK TABLE]![Rank]) AND (([PRODUCT
TABLE].Rank)=[Forms]![Maintain Products Form]![Rank])) OR ((([PRODUCT
TABLE].ProductName) Like "*" & [Enter Product] & "*") AND (([RADIO STATIONS
TABLE].Rank)<=[RANK TABLE]![Rank]) AND (([Forms]![Maintain Products
Form]![Rank]) Is Null))
ORDER BY [PRODUCT TABLE].[Customer ID], [PRODUCT TABLE].ProductName, [RADIO
STATIONS TABLE].[Station Call Letters];
I told you the lookup fields I had were ‘rank’ and ‘location’,,, but
location is actually called Market ID in my db,, I just didn’t want to
confuse you,,,
And here is the just of what I am trying to do:
This DB is for a company that is a broker for advertisers and radio
stations. Based on the target audience and other critieria of the
Advertiser’s Product, this query tries to find Radio Stations that match the
criteria to run the ads
Tables:
Product Table - contains the product info being advertised and criteria to
find the most suitable Radio Stations
Radio Station Table – list of radio stations with formats, ranks, markets
Criteria Tables:
Format Table – list of Radio Station formats. (Each Radio Station has 1
format, such as Rock, News, Sports, Hip Hop, Jazz, etc)
Target Audience Table – each target audience has a multiselect lookup to the
Format Table, ie if the Product is Burger King Whopper, the Target Audiences
might be Spanish, and Teens.
Teens in the target audience table might have the following formats: ‘hip
hop’, ‘rock’, Top 40.
Spanish Target Audience might have formats: Hip Hop, Hot Urban, Span CHR,
Urban, etc
Rank Table – can be Top 5, Top 10, Top 50, Top 100, the advertiser may only
want to go with Radio stations that are ranked in the Top 10, Top 20, Top 100
etc.
Market Table – Each Radio Station has 1 market, ie LA,r NY, Boston, etc
The query matches Target Audience -> Format -> Radio Station and this works
fine.
For Rank I check that the Radio Station Rank is less than or eq the drop
down selection from the Product Table Rank, which is a lookup field to the
Rank Table.
This also works fine
Except if they don’t select anything in the Rank Drop Down, then no radio
stations are returned. When I added the code you gave me, I get the same
results when nothing is selected.
I have not tried adding the code for the Target Audience or Market yet, I
think I will wait for your feedback before I go any further. Thanks in
advance for any help you can provide!!!
Nancy
--
Nancy
KenSheridan via AccessMonster.com
Lets see if we can simplify the query a bit:
SELECT
[PRODUCT TABLE].[Customer ID],
[PRODUCT TABLE].ProductName,
[RADIO STATIONS TABLE].[Station Call Letters],
[PRODUCT TABLE].[Target Audience ID],
[RADIO STATIONS TABLE].[Market ID],
[RADIO STATIONS TABLE].Rank,
[Rank Table].Rank,
[PRODUCT TABLE].Rank
FROM [MARKETS TABLE] INNER JOIN ((([FORMATS TABLE]
INNER JOIN [RADIO STATIONS TABLE]
ON [FORMATS TABLE].Format = [RADIO STATIONS TABLE].[Format ID])
INNER JOIN [TARGET AUDIENCE TABLE]
ON [FORMATS TABLE].Format = [TARGET AUDIENCE TABLE].Format)
INNER JOIN ([Rank Table] INNER JOIN [PRODUCT TABLE]
ON [Rank Table].ID = [PRODUCT TABLE].Rank)
ON [TARGET AUDIENCE TABLE].[Target Audience ID] =
[PRODUCTABLE].[Target Audience ID])
ON ([RADIO STATIONS TABLE].[Market ID] = [MARKETS TABLE].[Market ID])
AND ([MARKETS TABLE].[Market ID] = [PRODUCT TABLE].[Market ID])
WHERE [PRODUCT TABLE].ProductName Like "*" & [Enter Product] & "*"
AND [RADIO STATIONS TABLE].Rank <= [RANK TABLE].[Rank]
AND ([PRODUCT TABLE].Rank = [Forms]![Maintain Products Form]![Rank]
OR [Forms]![Maintain Products Form]![Rank] IS NULL)
ORDER BY [PRODUCT TABLE].[Customer ID],
[PRODUCT TABLE].ProductName,
[RADIO STATIONS TABLE].[Station Call Letters];
When you saved the query in design view Access rearranged things, so it was
not so easy to see the underlying logic. I've simplified the WHERE clause so
that the OR operation is evaluated independently of the AND operations (by
enclosing it in parentheses), making the underlying logic more easily
apparent. If the [Forms]![Maintain Products Form]![Rank] control is left
Null then the expression within the parentheses will evaluate to True for
every row, so the values in the [PRODUCT TABLE].Rank column would then have
no bearing on which rows are returned by the query.
Ken Sheridan
Stafford, England
>> The whole expression goes in the first 'criteria' row of the relevant column
>> in query design view, so in the Rank column's first 'criteria' row you'd put:
>[quoted text clipped - 58 lines]
>> >> > form,
>> >> > it will look for all fields that are "*". Any suggestions?
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access/200908/1
Nurse Nancy
I so appreciate your quick response bc i can't move forward till i figure
this out.
Forgive me but I am a newbie to Access, and I don't know what to do with the
info you gave me.
i tried copying your sql into my query and replacing mine, but it won't
save,, it says Syntax Error on Join operations and highlights the product
table on the 10th line from the bottom.
What should I be doing????
--
Nancy
KenSheridan via AccessMonster.com
It somehow got telescoped when I tidied it up. It should be:
[PRODUCT TABLE].[Target Audience ID])
Ken Sheridan
Stafford, England
Nurse Nancy wrote:
>Ken,
>I so appreciate your quick response bc i can't move forward till i figure
>this out.
>
>Forgive me but I am a newbie to Access, and I don't know what to do with the
>info you gave me.
>
>i tried copying your sql into my query and replacing mine, but it won't
>save,, it says Syntax Error on Join operations and highlights the product
>table on the 10th line from the bottom.
>
>What should I be doing????
>> Nancy:
>>
>[quoted text clipped - 122 lines]
>> >> >> > form,
>> >> >> > it will look for all fields that are "*". Any suggestions?
--
Message posted via http://www.accessmonster.com
Hans Up
> i tried copying your sql into my query and replacing mine, but it won't
> save,, it says Syntax Error on Join operations and highlights the product
> table on the 10th line from the bottom.
You're missing a couple character in [PRODUCTABLE]. Change it to
[PRODUCT TABLE].
Nurse Nancy
STATIONS TABLE].[Station Call Letters], [PRODUCT TABLE].[Target Audience ID],
[RADIO STATIONS TABLE].[Market ID], [RADIO STATIONS TABLE].Rank, [Rank
Table].Rank, [PRODUCT TABLE].Rank
TABLE].[Market ID].Value) INNER JOIN [TARGET AUDIENCE TABLE] ON ([FORMATS
TABLE].Format = [TARGET AUDIENCE TABLE].Format.Value) AND ([PRODUCT
TABLE].[Target Audience ID].Value = [TARGET AUDIENCE TABLE].[Target Audience
ID])
WHERE ((([PRODUCT TABLE].ProductName) Like "*" & [Enter Product] & "*") AND
TABLE].ProductName) Like "*" & [Enter Product] & "*") AND (([RADIO STATIONS
ORDER BY [PRODUCT TABLE].[Customer ID], [PRODUCT TABLE].ProductName, [RADIO
STATIONS TABLE].[Station Call Letters];
thanks but it still brings back no results if i leave the drop down blank.
And Now i have another problem, If I do select a rank and if I leave the
Product Name Paramater empty It no longer returns all products,, it seems to
retain previous selections that I entered.
The work around that I am able to use is,,, i added a rank to the rank table
- |7|999|All Ranks|
ID|Rank|Descrip|
So in the drop down on the form it shows as 'All Ranks' and it has a value
of 999.
Now I had to change the rankings of any radio stations that don't have a
rank, from null to 999 in the Radio Stations Table,, which really isn't
realistic, bc the radio station table typically will have the top 100
stations with a ranking, and the remaining 5000 stations will have no rank!!!!
And instead of leaving the Rank in the dropdown of Maintain Products Form
empty when I don't want to filter on Rank, I have to select the 'All Ranks'
from the drop down.
This will return all radio stations with a rank of 999 or less.
I can live with this work around for ranks,,, but i have the same issue with
Markets and there are over 130 market choices in the Market ID dropdown.
And users are allowed to select multiple markets,
So I either need to make the null selection work or figure out a way to
match on all selections without making them select 130, 1 at a time.
Can i send you the db by any chance, or is that asking way too much?
HELP!!
--
Nancy
KenSheridan via AccessMonster.com
You should test for the [Enter Product] parameter being Null if you are
going to leave it blank. As Null is never equal to anything, not even to
another Null, any rows with a Null product name would not be returned:
Its important to get the parentheses in the right places in the WHERE clause
to force the OR operations to evaluate before the AND operations.
SELECT
[PRODUCT TABLE].[Customer ID],
[PRODUCT TABLE].ProductName,
[RADIO STATIONS TABLE].[Station Call Letters],
[PRODUCT TABLE].[Target Audience ID],
[RADIO STATIONS TABLE].[Market ID],
[RADIO STATIONS TABLE].Rank,
[Rank Table].Rank,
[PRODUCT TABLE].Rank
FROM (([MARKETS TABLE] INNER JOIN ([FORMATS TABLE]
INNER JOIN [RADIO STATIONS TABLE]
ON [FORMATS TABLE].Format = [RADIO STATIONS TABLE].[Format ID])
ON [MARKETS TABLE].[Market ID] = [RADIO STATIONS TABLE].[Market ID])
INNER JOIN ([Rank Table] INNER JOIN [PRODUCT TABLE]
ON [Rank Table].ID = [PRODUCT TABLE].Rank)
ON [MARKETS TABLE].[Market ID] = [PRODUCT TABLE].[Market ID])
INNER JOIN [TARGET AUDIENCE TABLE]
ON ([FORMATS TABLE].Format = [TARGET AUDIENCE TABLE].Format)
AND ([PRODUCT TABLE].[Target Audience ID] =
[TARGET AUDIENCE TABLE].[Target Audience ID])
WHERE
([PRODUCT TABLE].ProductName LIKE "*" & [Enter Product] & "*"
OR [Enter Product] IS NULL)
AND [RADIO STATIONS TABLE].Rank<=[RANK TABLE].[Rank]
AND ([PRODUCT TABLE].Rank=[Forms]![Maintain Products Form]![Rank]
OR [Forms]![Maintain Products Form]![Rank] IS NULL)
ORDER BY
[PRODUCT TABLE].[Customer ID],
[PRODUCT TABLE].ProductName,
[RADIO STATIONS TABLE].[Station Call Letters];
Save this as a new query and make any amendments in SQL view, not design view.
If you switch to design view you'll find it much harder to understand the
underlying logic of the WHERE clause and make any amendments as Access will
move things around and put in a lot of unnecessary parentheses. Once you are
happy that its working you can copy and paste the SQL into your original
query.
Instead of using your dummy 999 rank for 'All Ranks' you don't need to add a
row to the Ranks table; you can return one by using a UNION operation as the
RowSource of the combo box, e.g.
SELECT ID, Rank, Descrip, 1 AS SortColumn
FROM [Rank Table]
UNION
SELECT NULL, NULL, "All Ranks", 0
FROM [Rank Table]
ORDER BY SortColumn, Descrip;
The value of the combo box will be Null if you select the All Ranks item, or
if you leave the combo box empty, so testing for NULL as in the above query
will work in both cases.
As a majority of the stations have Null rank, the expression:
[RADIO STATIONS TABLE].Rank<=[RANK TABLE].[Rank]
will evaluate to Null for those stations and exclude them from the results.
Is that what you want? If not you can use the Nz function to return a zero
in place of the Nulls (assuming its a numeric data type column:
NZ([RADIO STATIONS TABLE].Rank,0)<=[RANK TABLE].[Rank]
Or to return a zero-length string if its a text data type:
NZ([RADIO STATIONS TABLE].Rank,"")<=[RANK TABLE].[Rank]
Ken Sheridan
Stafford, England
>> > i tried copying your sql into my query and replacing mine, but it won't
>> > save,, it says Syntax Error on Join operations and highlights the product
>> > table on the 10th line from the bottom.
>>
>> You're missing a couple character in [PRODUCTABLE]. Change it to
>> [PRODUCT TABLE].
--
Hans Up
> SELECT [PRODUCT TABLE].[Customer ID], [PRODUCT TABLE].ProductName, [RADIO
> STATIONS TABLE].[Station Call Letters], [PRODUCT TABLE].[Target Audience ID],
> [RADIO STATIONS TABLE].[Market ID], [RADIO STATIONS TABLE].Rank, [Rank
> Table].Rank, [PRODUCT TABLE].Rank
> FROM (([MARKETS TABLE] INNER JOIN ([FORMATS TABLE] INNER JOIN [RADIO
> STATIONS TABLE] ON [FORMATS TABLE].Format = [RADIO STATIONS TABLE].[Format
> ID]) ON [MARKETS TABLE].[Market ID] = [RADIO STATIONS TABLE].[Market ID])
> INNER JOIN ([Rank Table] INNER JOIN [PRODUCT TABLE] ON [Rank Table].ID =
> [PRODUCT TABLE].Rank) ON [MARKETS TABLE].[Market ID] = [PRODUCT
> TABLE].[Market ID].Value) INNER JOIN [TARGET AUDIENCE TABLE] ON ([FORMATS
> TABLE].Format = [TARGET AUDIENCE TABLE].Format.Value) AND ([PRODUCT
> TABLE].[Target Audience ID].Value = [TARGET AUDIENCE TABLE].[Target Audience
> ID])
> WHERE ((([PRODUCT TABLE].ProductName) Like "*" & [Enter Product] & "*") AND
> (([RADIO STATIONS TABLE].Rank)<=[RANK TABLE].[Rank]) AND (([PRODUCT
> TABLE].Rank)=[Forms]![Maintain Products Form]![Rank])) OR ((([PRODUCT
> TABLE].ProductName) Like "*" & [Enter Product] & "*") AND (([RADIO STATIONS
> TABLE].Rank)<=[RANK TABLE].[Rank]) AND (([Forms]![Maintain Products
> Form]![Rank]) Is Null))
> ORDER BY [PRODUCT TABLE].[Customer ID], [PRODUCT TABLE].ProductName, [RADIO
> STATIONS TABLE].[Station Call Letters];
>
>
> thanks but it still brings back no results if i leave the drop down blank.
Your SQL is difficult for my brain to parse. But I think you may have
other problems besides the blank drop down issue. Consider this
fragment in your "FROM" clause:
[FORMATS TABLE].Format = [TARGET AUDIENCE TABLE].Format.Value
You have a field named "Format" in both tables. But "Format" is a
reserved word.
Please see http://allenbrowne.com/AppIssueBadWord.html#F
Perhaps Access doesn't choke with "Format" as a field name, but I have
no idea how "Format.Value" will be evaluated in this context. That just
blows my mind, Nancy!
You're using ".Value" in three places:
SELECT
p.[Customer ID],
p.ProductName,
s.[Station Call Letters],
p.[Target Audience ID],
s.[Market ID],
s.Rank,
r.Rank,
p.Rank
FROM
(([MARKETS TABLE] AS m
INNER JOIN ([FORMATS TABLE] AS f
INNER JOIN [RADIO STATIONS TABLE] AS s
ON f.Format = s.[Format ID])
ON m.[Market ID] = s.[Market ID])
INNER JOIN ([Rank Table] AS r
INNER JOIN [PRODUCT TABLE] AS p
ON r.ID = p.Rank)
ON m.[Market ID] = p.[Market ID].Value) <--*
INNER JOIN [TARGET AUDIENCE TABLE] AS a
ON (f.Format = a.Format.Value) <--*
AND (p.[Target Audience ID].Value = a.[Target Audience ID]) <--*
WHERE
(((p.ProductName) Like "*" & [Enter Product] & "*")
AND ((s.Rank)<=r.[Rank])
AND ((p.Rank)=[Forms]![Maintain Products Form]![Rank]))
OR (((p.ProductName) Like "*" & [Enter Product] & "*")
AND ((s.Rank)<=r.[Rank])
AND (([Forms]![Maintain Products Form]![Rank]) Is Null))
ORDER BY
p.[Customer ID],
p.ProductName,
s.[Station Call Letters];
> Can i send you the db by any chance, or is that asking way too much?
Did you intend that question for me? Or Ken?
If you really want to send me a copy of your db, I will look at it. But
no guarantee I can fix anything! I may conclude the amount of effort is
unreasonable.
With that caveat in mind, you're welcome to send me a zip of your
database. My first name is Hans. Last name is Updyke. You can get it
to me at first...@gmail.com. (I wrote it that way as a spam
deflector; you can make the substitutions for first and last.) Discard
any sensitive/confidential data from your db, but leave me at least a
few records of real or fake data in each table to help me figure out how
the data is used. And please perform a "Compact and Repair" on your db
before creating the zip archive file.
Hans
James A. Fortune
> Instead of using your dummy 999 rank for 'All Ranks' you don't need to add a
> row to the Ranks table; you can return one by using a UNION operation as the
> RowSource of the combo box, e.g.
>
> SELECT ID, Rank, Descrip, 1 AS SortColumn
> FROM [Rank Table]
> UNION
> SELECT NULL, NULL, "All Ranks", 0
> FROM [Rank Table]
> ORDER BY SortColumn, Descrip;
Ken,
Without presuming to tell a SQL expert what to write, may I suggest a
variation for your consideration. Something like:
SELECT ID, Rank, Descrip, 1 AS SortColumn
FROM [Rank Table]
UNION
SELECT NULL, NULL, "All Ranks", 0
FROM [Rank Table] WHERE ID = 1
ORDER BY SortColumn, Descrip;
The reason for the inclusion of WHERE ID = <some existing ID> is that,
if I understand the UNION operation correctly, for large tables you
don't want the UNION to take the time to include, then eliminate so many
duplicates. It's not a big issue in typical cases because, according to
the OP, the results are going into a "drop box," implying that the
result set is likely to be small enough that such considerations won't
produce a noticeable difference in retrieval time.
James A. Fortune
MPAP...@FortuneJames.com
KenSheridan via AccessMonster.com
SQL expert? Your must be thinking of someone else. I'd never consider
myself that! The most I can claim is that I've picked up a few things along
the way by using Access in my own field of work, and if it can be useful to
anyone else I'm happy to pass it on.
Your point is a very valid one, and the solution a simple and effective one.
Ken Sheridan
Stafford, England
James A. Fortune wrote:
>> Instead of using your dummy 999 rank for 'All Ranks' you don't need to add a
>> row to the Ranks table; you can return one by using a UNION operation as the
>[quoted text clipped - 6 lines]
>> FROM [Rank Table]
>> ORDER BY SortColumn, Descrip;
>
>Ken,
>
>Without presuming to tell a SQL expert what to write, may I suggest a
>variation for your consideration. Something like:
>
>SELECT ID, Rank, Descrip, 1 AS SortColumn
>FROM [Rank Table]
>UNION
>SELECT NULL, NULL, "All Ranks", 0
>FROM [Rank Table] WHERE ID = 1
>ORDER BY SortColumn, Descrip;
>
>The reason for the inclusion of WHERE ID = <some existing ID> is that,
>if I understand the UNION operation correctly, for large tables you
>don't want the UNION to take the time to include, then eliminate so many
>duplicates. It's not a big issue in typical cases because, according to
>the OP, the results are going into a "drop box," implying that the
>result set is likely to be small enough that such considerations won't
>produce a noticeable difference in retrieval time.
>
>James A. Fortune
>MPAP...@FortuneJames.com
--