Velocity Reviews > Spreadsheet Formula Help

Steve Freides
Guest
Posts: n/a

 02-14-2009
I created a table for myself in FoxPro then built some simple SQL
statements to give me tallies of various things. I would like to give
this to someone as a spreadsheet so that the data can be changed by them
and the formulas still give results. Here's the structure of the
table - it's basically a course listing from my department

DEPARTMENT COURSE SECTION FACULTY CREDITS FULLTIME DUPLICATE
DUP_NUMBER

Department is the same for everything right now but I have a second

The Duplicate field is either True or False - sometimes two course
numbers actually refer to the same teacher, place, and time, e.g., the
chorus - you sign up for chorus 1, chorus 2, chorus 3, or chorus 4,
depending on how many times you've taken it before, but for these
purposes, it should only count as one course.

The purpose here is to compare what full- and part-time faculty teach.
e.g., given to the higher-ups at the college as part of trying to win
more full-time faculty lines. The multiple departments are intended to
show which departments need full-time lines the most, all other things
being equal.

Here are two queries that yield useful results:

* credits taught by part- and full-time
SELECT fulltime, SUM(credits) FROM Spring09
WHERE NOT duplicate
GROUP BY fulltime ORDER BY fulltime

* sections taught by part- and full-time
SELECT fulltime, COUNT(*) FROM Spring09
WHERE NOT duplicate
GROUP BY fulltime ORDER BY fulltime

With all this now in a spreadsheet, is there a way in a formula to get
the kind of results the query yields? At the moment each field is now a
column, e.g., faculy name is column D.

Thanks very much in advance for any assistance.

-S-

why?
Guest
Posts: n/a

 02-14-2009

On Fri, 13 Feb 2009 21:00:23 -0500, Steve Freides wrote:

>I created a table for myself in FoxPro then built some simple SQL

That's going back a bit, assume you mean the MS Visual FoxPro not so
nice version as opposed to the very nice original FP.

>statements to give me tallies of various things. I would like to give
>this to someone as a spreadsheet so that the data can be changed by them

Going from a DB to SS, is generally the wrong way round. Lots of SS
should be a DB.

>and the formulas still give results. Here's the structure of the
>table - it's basically a course listing from my department
>
>DEPARTMENT COURSE SECTION FACULTY CREDITS FULLTIME DUPLICATE
>DUP_NUMBER
>
>Department is the same for everything right now but I have a second

You aren't going to get the schema / requirements understood this way,
could have skipped all this bit

>The Duplicate field is either True or False - sometimes two course
>numbers actually refer to the same teacher, place, and time, e.g., the

<big snip>

>Here are two queries that yield useful results:

For someone....

>* credits taught by part- and full-time
>SELECT fulltime, SUM(credits) FROM Spring09
>WHERE NOT duplicate
>GROUP BY fulltime ORDER BY fulltime
>
>* sections taught by part- and full-time
>SELECT fulltime, COUNT(*) FROM Spring09
>WHERE NOT duplicate
>GROUP BY fulltime ORDER BY fulltime
>
>With all this now in a spreadsheet, is there a way in a formula to get
>the kind of results the query yields? At the moment each field is now a

I can't see what those results would be.

>column, e.g., faculy name is column D.

Depending on which unnamed SS you are using, most have some sort of
macro language you could use to get the same results.

>Thanks very much in advance for any assistance.

Leave it as a DB.

Me

Steve
Guest
Posts: n/a

 02-14-2009
Spreadsheets normally have formulas which sum columns where the
records summed are limited (i.e. filtered) by the value in some other
column. For example, SUMIF. The exact synatax of the formula will
depend on the brand of spreadsheet you are using. It may actually
require some digging into the help section of the spreadsheet you are
using. HTH
Steve

On Fri, 13 Feb 2009 21:00:23 -0500, "Steve Freides" <(E-Mail Removed)>
wrote:

>I created a table for myself in FoxPro then built some simple SQL
>statements to give me tallies of various things. I would like to give
>this to someone as a spreadsheet so that the data can be changed by them
>and the formulas still give results. Here's the structure of the
>table - it's basically a course listing from my department
>
>DEPARTMENT COURSE SECTION FACULTY CREDITS FULLTIME DUPLICATE
>DUP_NUMBER
>
>Department is the same for everything right now but I have a second
>
>The Duplicate field is either True or False - sometimes two course
>numbers actually refer to the same teacher, place, and time, e.g., the
>chorus - you sign up for chorus 1, chorus 2, chorus 3, or chorus 4,
>depending on how many times you've taken it before, but for these
>purposes, it should only count as one course.
>
>The purpose here is to compare what full- and part-time faculty teach.
>e.g., given to the higher-ups at the college as part of trying to win
>more full-time faculty lines. The multiple departments are intended to
>show which departments need full-time lines the most, all other things
>being equal.
>
>Here are two queries that yield useful results:
>
>* credits taught by part- and full-time
>SELECT fulltime, SUM(credits) FROM Spring09
>WHERE NOT duplicate
>GROUP BY fulltime ORDER BY fulltime
>
>* sections taught by part- and full-time
>SELECT fulltime, COUNT(*) FROM Spring09
>WHERE NOT duplicate
>GROUP BY fulltime ORDER BY fulltime
>
>With all this now in a spreadsheet, is there a way in a formula to get
>the kind of results the query yields? At the moment each field is now a
>column, e.g., faculy name is column D.
>
>Thanks very much in advance for any assistance.
>
>-S-
>

Mac
Guest
Posts: n/a

 02-15-2009
You don't need the order by clause as the group by already sorts. Also using
a separate table name for each year is poor practise (not normalized) add a
column called year and select where year = nn then you only need the one
table

"Steve Freides" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>I created a table for myself in FoxPro then built some simple SQL
>statements to give me tallies of various things. I would like to give this
>to someone as a spreadsheet so that the data can be changed by them and the
>formulas still give results. Here's the structure of the table - it's
>basically a course listing from my department
>
> DEPARTMENT COURSE SECTION FACULTY CREDITS FULLTIME DUPLICATE
> DUP_NUMBER
>
> Department is the same for everything right now but I have a second
>
> The Duplicate field is either True or False - sometimes two course numbers
> actually refer to the same teacher, place, and time, e.g., the chorus -
> you sign up for chorus 1, chorus 2, chorus 3, or chorus 4, depending on
> how many times you've taken it before, but for these purposes, it should
> only count as one course.
>
> The purpose here is to compare what full- and part-time faculty teach. The
> spreadsheet format is universal enough that the spreadsheet can be, e.g.,
> given to the higher-ups at the college as part of trying to win more
> full-time faculty lines. The multiple departments are intended to show
> which departments need full-time lines the most, all other things being
> equal.
>
> Here are two queries that yield useful results:
>
> * credits taught by part- and full-time
> SELECT fulltime, SUM(credits) FROM Spring09
> WHERE NOT duplicate
> GROUP BY fulltime ORDER BY fulltime
>
> * sections taught by part- and full-time
> SELECT fulltime, COUNT(*) FROM Spring09
> WHERE NOT duplicate
> GROUP BY fulltime ORDER BY fulltime
>
> With all this now in a spreadsheet, is there a way in a formula to get the
> kind of results the query yields? At the moment each field is now a
> column, e.g., faculy name is column D.
>
> Thanks very much in advance for any assistance.
>
> -S-
>

why?
Guest
Posts: n/a

 02-15-2009

On Sun, 15 Feb 2009 08:47:52 GMT, Mac wrote:

>You don't need the order by clause as the group by already sorts. Also using
>a separate table name for each year is poor practise (not normalized) add a
>column called year and select where year = nn then you only need the one
>table

Doesn't sort of fit the OP's going from a DB to a SS. It's often enough
hard work doing the normalization in the first place without undoing it
for a SS I managed to skip mentioning it the 1st time.

>
>"Steve Freides" <(E-Mail Removed)> wrote in message
>news:(E-Mail Removed)...
>>I created a table for myself in FoxPro then built some simple SQL
>>statements to give me tallies of various things. I would like to give this
>>to someone as a spreadsheet so that the data can be changed by them and the
>>formulas still give results. Here's the structure of the table - it's
>>basically a course listing from my department
>>
>> DEPARTMENT COURSE SECTION FACULTY CREDITS FULLTIME DUPLICATE
>> DUP_NUMBER
>>
>> Department is the same for everything right now but I have a second

<snip>

Me