Database functions should use criteria in formula, as 1-2-3 does

Excel's database formulas are primitive. They work like 1-2-3's database
functions did in its Release 3. Instead of making the user create a separate
criteria range, the database functions should allow criteria to be written
into the formula, as 1-2-3's database functions do. After using 1-2-3 for 15
years, in which time I became very proficient in the software, my agency has
joined the horde that use Excel. I rely heavily on database functions and
switching to Excel's presents big problems for me. I realize that I can
approximate a database function with criteria with arrays. However, this
requires creating a large number of ranges while creating a database that you
can use database functions with requires namng only one range.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...et.f unctions

123user wrote...
Excel's database formulas are primitive. They work like 1-2-3's
database functions did in its Release 3. Instead of making the
user create a separate criteria range, the database functions
should allow criteria to be written into the formula, as 1-2-3's
database functions do. After using 1-2-3 for 15 years, in which
time I became very proficient in the software, my agency has
joined the horde that use Excel. I rely heavily on database
functions and switching to Excel's presents big problems for me.
I realize that I can approximate a database function with
criteria with arrays. However, this requires creating a large
number of ranges while creating a database that you can use
database functions with requires namng only one range.
....

First, you're wrong about your 123 versions. Criteria in function
calls began in 123 Release 3.0. It's Release 2.x and earlier that
used creiteria ranges.

Next, if you think 123's @D... functions are good, SQL is even
better. While there are some problems with memory leaks in some
earlier versions, Excel's SQL.REQUEST add-on function is much more
powerful than 123's @D... functions. Granted Excel's current D...
function equivalents are mired in the mid-1980s, why settle for
just moving into the early 1990s?

And you don't even need to use SQL.REQUEST for simpler calcs. You
could use SUMPRODUCT for most conditional counting and summing,
and you could use single cell array formulas for most of the other
D... functions. Even including wild card text searching.

What are your 123 formulas? They should be simple enough to convert
into single cell Excel work-alikes.

Wow, what a memory about the history of spreadsheets! But then, I recognize
your name from other spreadsheet boards, so I’m not too surprised.. If there
is a SQL.REQUEST add-on that is easier to use than database functions, how do
I do get it? I would like to see what it does.

As I said, I can do most of what I do in 1-2-3 in Excel with arrays, but it
requires–at least as much as I have figured out–creating numerous ranges.
With 1-2-3 database functions, all I have to do is name the database with its
field headings. As for my use of database functions, I am not looking so
much for conditional counting and summing, although I do use them for that.
I use @DGET a lot to create crosstabs that automatically update when the
years in the crosstab change. Of course, I’m not really writing in criteria;
I am referring to cells that have the criteria in them. With the use of
relative and fixed addresses, I can then copy the database function very
quickly and create a crosstab. The following is an example of a cash
receipts crosstab on a model I run.

A B C D E F
1 SQLVAR: CRWT--VA
2 2000 2001 2002 2003 2004
3
4 QTR1 @DGET($CRDB,$A4,SQLVAR=$B$1#AND#$DBYEAR=B$2)
5 QTR2
6 QTR3
7 QTR4
8 ANNL

The database fields are arranged:

SQLVAR DBYEAR QTR1 QTR2 QTR3 QTR4 ANNL
CRWT--VA 2001 2521872 1524781 2389421 4827926 11714000

The first element of the function is the database name, which is fixed both
ways. The second element is the field. It refers to a row stub. This has
its horizontal reference fixed so that when it is copied to the right, it
will continue to point to the row stub but will change to the succeeding
QTR's and ANNL when copied down. The first criteria is the cash receipts
variable name in the SQL database that I have pulled the numbers from the
database on the spreadsheet. It is fixed both ways. The second criteria
obviously references the year from the database. It is fixed vertically so
that when the formula is copied down the year reference is kept the same but
changes as it is copied across.

Finally, Excel will not do anythig with these functions when I try to
convert a 1-2-3 file to Excel. What I get is: "REF!". This is a big reason
that I have resisted switching.

"Harlan Grove" wrote:

123user wrote...
Excel's database formulas are primitive. They work like 1-2-3's
database functions did in its Release 3. Instead of making the
user create a separate criteria range, the database functions
should allow criteria to be written into the formula, as 1-2-3's
database functions do. After using 1-2-3 for 15 years, in which
time I became very proficient in the software, my agency has
joined the horde that use Excel. I rely heavily on database
functions and switching to Excel's presents big problems for me.
I realize that I can approximate a database function with
criteria with arrays. However, this requires creating a large
number of ranges while creating a database that you can use
database functions with requires namng only one range.
....

First, you're wrong about your 123 versions. Criteria in function
calls began in 123 Release 3.0. It's Release 2.x and earlier that
used creiteria ranges.

Next, if you think 123's @D... functions are good, SQL is even
better. While there are some problems with memory leaks in some
earlier versions, Excel's SQL.REQUEST add-on function is much more
powerful than 123's @D... functions. Granted Excel's current D...
function equivalents are mired in the mid-1980s, why settle for
just moving into the early 1990s?

And you don't even need to use SQL.REQUEST for simpler calcs. You
could use SUMPRODUCT for most conditional counting and summing,
and you could use single cell array formulas for most of the other
D... functions. Even including wild card text searching.

What are your 123 formulas? They should be simple enough to convert
into single cell Excel work-alikes.

123user wrote...
Wow, what a memory about the history of spreadsheets! But then, I recognize
your name from other spreadsheet boards, so I'm not too surprised.. If there
is a SQL.REQUEST add-on that is easier to use than database functions, how do
I do get it? I would like to see what it does.

See Excel's online help for SQL.REQUEST.

As I said, I can do most of what I do in 1-2-3 in Excel with arrays, but it
requires-at least as much as I have figured out-creating numerous ranges.
With 1-2-3 database functions, all I have to do is name the database with its
field headings. As for my use of database functions, I am not looking so
much for conditional counting and summing, although I do use them for that.
I use @DGET a lot to create crosstabs that automatically update when the
years in the crosstab change. Of course, I'm not really writing in criteria;
I am referring to cells that have the criteria in them. With the use of
relative and fixed addresses, I can then copy the database function very
quickly and create a crosstab. The following is an example of a cash
receipts crosstab on a model I run.

A B C D E F
1 SQLVAR: CRWT--VA
2 2000 2001 2002 2003 2004
3
4 QTR1 @DGET($CRDB,$A4,SQLVAR=$B$1#AND#$DBYEAR=B$2)
5 QTR2
6 QTR3
7 QTR4
8 ANNL
....

I won't say this is as elegant as it is in 123, but an equivalent Excel
formula would be the array formula

=INDEX(CRDB,MATCH(1,(INDEX(CRDB,0,MATCH("SQLVAR",I NDEX(CRDB,1,0),0))=$B$1)
*(INDEX(CRDB,0,MATCH("DBYEAR",INDEX(CRDB,1,0),0))= B$2),0),
MATCH($A4,INDEX(CRDB,1,0),0))

This involves no hardcoding. With some hardcoding, namely, using
additional defined names for the ranges containing the SQLVAR and
DBYEAR fields, the array formula reduces to

=INDEX(CRDB,MATCH(1,(CRDB.SQLVAR=$B$1)*(CRDB.DBYEA R=B$2),0),2+ROWS(B$4:B4))

Finally, Excel will not do anythig with these functions when I try to
convert a 1-2-3 file to Excel. What I get is: "REF!". This is a big reason
that I have resisted switching.
....

Excel does nothing with them because it doesn't support the syntax. The
criteria expressions that 123 provides are interpretted as range
references in Excel, and since they're not valid range references, the
formulas return #REF!.

I am able to use arrays this way to get wat I want. The drawback, as I said,
is that I have to name the full column of each field of the database.

CRMUSAC--VA 2000 2001 2002 2003

QTR1 {=SUM((CRDB_VAR=$B$4)*(CRDB_YEAR=D$4)*(CRDB_QTR1)) }
QTR2
QTR3
QTR4
ANNL

I'll check out the online help on the add-in. Thanks for the suggestion.

"Harlan Grove" wrote:

123user wrote...
Wow, what a memory about the history of spreadsheets! But then, I recognize
your name from other spreadsheet boards, so I'm not too surprised.. If there
is a SQL.REQUEST add-on that is easier to use than database functions, how do
I do get it? I would like to see what it does.

See Excel's online help for SQL.REQUEST.

As I said, I can do most of what I do in 1-2-3 in Excel with arrays, but it
requires-at least as much as I have figured out-creating numerous ranges.
With 1-2-3 database functions, all I have to do is name the database with its
field headings. As for my use of database functions, I am not looking so
much for conditional counting and summing, although I do use them for that.
I use @DGET a lot to create crosstabs that automatically update when the
years in the crosstab change. Of course, I'm not really writing in criteria;
I am referring to cells that have the criteria in them. With the use of
relative and fixed addresses, I can then copy the database function very
quickly and create a crosstab. The following is an example of a cash
receipts crosstab on a model I run.

A B C D E F
1 SQLVAR: CRWT--VA
2 2000 2001 2002 2003 2004
3
4 QTR1 @DGET($CRDB,$A4,SQLVAR=$B$1#AND#$DBYEAR=B$2)
5 QTR2
6 QTR3
7 QTR4
8 ANNL
....

I won't say this is as elegant as it is in 123, but an equivalent Excel
formula would be the array formula

=INDEX(CRDB,MATCH(1,(INDEX(CRDB,0,MATCH("SQLVAR",I NDEX(CRDB,1,0),0))=$B$1)
*(INDEX(CRDB,0,MATCH("DBYEAR",INDEX(CRDB,1,0),0))= B$2),0),
MATCH($A4,INDEX(CRDB,1,0),0))

This involves no hardcoding. With some hardcoding, namely, using
additional defined names for the ranges containing the SQLVAR and
DBYEAR fields, the array formula reduces to

=INDEX(CRDB,MATCH(1,(CRDB.SQLVAR=$B$1)*(CRDB.DBYEA R=B$2),0),2+ROWS(B$4:B4))

Finally, Excel will not do anythig with these functions when I try to
convert a 1-2-3 file to Excel. What I get is: "REF!". This is a big reason
that I have resisted switching.
....

Excel does nothing with them because it doesn't support the syntax. The
criteria expressions that 123 provides are interpretted as range
references in Excel, and since they're not valid range references, the
formulas return #REF!.

123user wrote...
I am able to use arrays this way to get wat I want. The drawback, as I said,
is that I have to name the full column of each field of the database.
....

No, you don't. Look at the first formula in my previous response.

=INDEX(CRDB,MATCH(1,(INDEX(CRDB,0,MATCH("SQLVAR",I NDEX(CRDB,1,0),0))=$B$1)
*(INDEX(CRDB,0,MATCH("DBYEAR",INDEX(CRDB,1,0),0))= B$2),0),
MATCH($A4,INDEX(CRDB,1,0),0))

As I said, not elegant, but it *only* requires the named range for the
table. That said, I would add one defined name per table returning the
table's top row, which contains the field names. Something like CRDB.FN
referring to =INDEX(CRDB,1,0). That would allow shortening the formula
above to

=INDEX(CRDB,MATCH(1,(INDEX(CRDB,0,MATCH("SQLVAR",C RDB.FN,0))=$B$1)
*(INDEX(CRDB,0,MATCH("DBYEAR",CRDB.FN,0))=B$2),0), MATCH($A4,CRDB.FN,0))

OK. As I said in my 2nd post "at least as much as I have figured out." You
must admit, however, that even the reduced formula is complex. By the way, I
cannot find a download source for SQL.REQUEST. I can only find the
definition.

"Harlan Grove" wrote:

123user wrote...
I am able to use arrays this way to get wat I want. The drawback, as I said,
is that I have to name the full column of each field of the database.
....

No, you don't. Look at the first formula in my previous response.

=INDEX(CRDB,MATCH(1,(INDEX(CRDB,0,MATCH("SQLVAR",I NDEX(CRDB,1,0),0))=$B$1)
*(INDEX(CRDB,0,MATCH("DBYEAR",INDEX(CRDB,1,0),0))= B$2),0),
MATCH($A4,INDEX(CRDB,1,0),0))

As I said, not elegant, but it *only* requires the named range for the
table. That said, I would add one defined name per table returning the
table's top row, which contains the field names. Something like CRDB.FN
referring to =INDEX(CRDB,1,0). That would allow shortening the formula
above to

=INDEX(CRDB,MATCH(1,(INDEX(CRDB,0,MATCH("SQLVAR",C RDB.FN,0))=$B$1)
*(INDEX(CRDB,0,MATCH("DBYEAR",CRDB.FN,0))=B$2),0), MATCH($A4,CRDB.FN,0))

123user wrote...
OK. As I said in my 2nd post "at least as much as I have figured out." You
must admit, however, that even the reduced formula is complex. By the way, I
cannot find a download source for SQL.REQUEST. I can only find the
definition.
....

Granted about complexity. There's no question that Excel REMAINS way
behind 123 in terms of the power of what Excel calls its list
processing functions.

As for SQL.REQUEST, it's provided by the ODBC add-in XLODBC32.XLA. If
you're running Excel 2003, Microsoft for its own mysterious reasons
dropped that add-in from the production CD. You *can* use the Excel
2002 version, which is available from

http://www.microsoft.com/downloads/d...displaylang=en

(or http://makeashorterlink.com/?A340234EB ).

Great. Thanks

"Harlan Grove" wrote:

123user wrote...
OK. As I said in my 2nd post "at least as much as I have figured out." You
must admit, however, that even the reduced formula is complex. By the way, I
cannot find a download source for SQL.REQUEST. I can only find the
definition.
....

Granted about complexity. There's no question that Excel REMAINS way
behind 123 in terms of the power of what Excel calls its list
processing functions.

As for SQL.REQUEST, it's provided by the ODBC add-in XLODBC32.XLA. If
you're running Excel 2003, Microsoft for its own mysterious reasons
dropped that add-in from the production CD. You *can* use the Excel
2002 version, which is available from

http://www.microsoft.com/downloads/d...displaylang=en

(or http://makeashorterlink.com/?A340234EB ).