Prev: Query Help
Next: Update Queries
From: sassie_blueeyes on 22 Mar 2010 13:40
I have four fields in a table that contain an identifying number. I want to
write a query with an expression that looks for the first field that contains
a value and return that value. If the first field is null, I want the next
field to be looked at, if it has a value, I want that value returned, if it
is null, I want the next field to be looked at and so on.

ma_case_num varchar2(9byte)
ap_case_num varchar2(9byte)
fs_case_num varchar2(9byte)
ss_case_num varchar2(9byte)

From: Jeff Boyce on 22 Mar 2010 14:25
That data layout sounds a lot like ... a spreadsheet! Whenever I see
'repeating fields' in Access, I wonder if the "table" is simply a copy of an
Excel spreadsheet. Nothing against Excel, mind you, what it does it does
well.

But Access is NOT a spreadsheet on steroids. If you need a relational
database (e.g., Access), then you need to give it data it expects and is
optimized for, not feed it 'sheet data.

Here's an alternate table structure that would allow you to use a simple
query ... (untested):

tblYourTable
YourTableID (a primary key)
Case_Num (your varchar/9byte case numbers)
CaseType (your "ma", "ap", "fs", "ss" prefixes)

Note that if you might ever need more/fewer "case types", using this table
structure plus another table that holds valid CaseTypes gives you much more
flexibility AND requires no rewriting of queries, redesign of forms &
reports, no maintenance of tables and code, etc.

Good Luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"sassie_blueeyes" <u58912(a)uwe> wrote in message news:a5660f9820dfc(a)uwe...
>I have four fields in a table that contain an identifying number. I want to
> write a query with an expression that looks for the first field that
> contains
> a value and return that value. If the first field is null, I want the
> next
> field to be looked at, if it has a value, I want that value returned, if
> it
> is null, I want the next field to be looked at and so on.
>
> ma_case_num varchar2(9byte)
> ap_case_num varchar2(9byte)
> fs_case_num varchar2(9byte)
> ss_case_num varchar2(9byte)
>


From: Jerry Whittle on 22 Mar 2010 14:34
SELECT
IIf(IsNull([ma_case_num])=False,[ma_case_num],IIf(IsNull([ap_case_num])=False,[ap_case_num],IIf(IsNull([fs_case_num])=False,[fs_case_num],[ss_case_num])))
AS TheCases
FROM tblSassie;

Watch out for word wrapping.

BUT as you can see by the strange SQL statement with nested IIf statement,
the real problem is having the four fields like this in your table. What
happens to your queries, forms, and reports if you have to add a fifth Case?
It will be a problem.
Instead of going across with the field, you should have another table that
might look something like this:

CaseNumber CaseType
123456789 AP
987654321 FS
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"sassie_blueeyes" wrote:

> I have four fields in a table that contain an identifying number. I want to
> write a query with an expression that looks for the first field that contains
> a value and return that value. If the first field is null, I want the next
> field to be looked at, if it has a value, I want that value returned, if it
> is null, I want the next field to be looked at and so on.
>
> ma_case_num varchar2(9byte)
> ap_case_num varchar2(9byte)
> fs_case_num varchar2(9byte)
> ss_case_num varchar2(9byte)
>
> .
>
From: Krzysztof Naworyta on 23 Mar 2010 07:07
Jerry Whittle wrote:
| SELECT
|
IIf(IsNull([ma_case_num])=False,[ma_case_num],IIf(IsNull([ap_case_num])=False,[ap_case_num],IIf(IsNull([fs_case_num])=False,[fs_case_num],[ss_case_num])))
| AS TheCases
| FROM tblSassie;


SELECT
Nz([ma_case_num], Nz([ap_case_num], Nz([fs_case_num], [ss_case_num])))
AS TheCases
FROM tblSassie;


--
KN

From: Jerry Whittle on 23 Mar 2010 09:53
Very, very nice indeed! Much cleaner than my solution.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

"Krzysztof Naworyta" wrote:

> Jerry Whittle wrote:
> | SELECT
> |
> IIf(IsNull([ma_case_num])=False,[ma_case_num],IIf(IsNull([ap_case_num])=False,[ap_case_num],IIf(IsNull([fs_case_num])=False,[fs_case_num],[ss_case_num])))
> | AS TheCases
> | FROM tblSassie;
>
>
> SELECT
> Nz([ma_case_num], Nz([ap_case_num], Nz([fs_case_num], [ss_case_num])))
> AS TheCases
> FROM tblSassie;
>
>
> --
> KN
 | 
Pages: 1
Prev: Query Help
Next: Update Queries