Totals in Sharepoint Designer

It turns out that this is pretty straightforward - but woh betide you if you try putting sum against currency - it just doesn't want to know - but if you follow these instructions http://community.bamboosolutions.com/blogs/bambooteamblog/archive/2009/04/24/how-to-total-calculated-columns-in-a-sharepoint-list.aspx should see you right.

Creating one to many relationships between Sharepoint Lists

People will say that creating two (or more lists) that are joined in sharepoint is difficult / impossible/ involves 3rd party web parts well its just not true. Admittedly its not exactly like doing it in 'normal' database, but it can be done and furthermore there are numerous ways to do it:

1. Assuming that you have list A and list b then you could simply use a lookup. To view them is to connect the web parts. Assuming that the two lists are on the same page then you can use a radio button (and it can only be a radio button) to produce (master details views). I have to admit this a little limited but I have use it and when the details is actually a library this represents quite a lot of advantages. Users can create a list entry for each project lets say and then see all the documents related to a particular project - actually its a lot like folders.
2. To return to classic example list M for master and list D for details - how to create a 'Detail' entry from within a Master entry. The scenario I have in mind is that the user chooses a particular company and then they want to 'add a new employer'. I've seen various options for doing this but this one worked for me- assuming that you only want to ever edit the child details and master details seperately then create a link column on the master list called something like 'add a employee' to do this you'll need to know how to create an html calculated column http://pathtosharepoint.wordpress.com/category/the-html-calculated-column/
   and into this you'll need to put a unique key from the parent column. In my case each master had a RecID which was a GUID (we were trying to port a database into sharepoint), this RecID was used to tie the two lists together, you can use this column later to do joins. This column can be of almost any type -e.g. date and time suggests itself and here is a microsoft article detailing the steps to pulling the contents from the query string - and pre-populating some part of a new item form http://blogs.msdn.com/sharepointdesigner/archive/2007/06/13/using-javascript-to-manipulate-a-list-form-field.aspx . They use a similar trick to put default values into lists.
3. To create views from multiple lists again lots of options
   http://blah.winsmarts.com/2007-10-Performing_joins_between_SharePoint_lists.aspx - though its not necessary you can just put the two lists into dataviews and attach the same querystring filter to both of them - assuming that the two lists share the same column. If you are feeling very naughty and assuming that you have to produce some sort of report of masters and details then you can create a formula column with a querystring in the url and use that to populate an iframe (make sure you create a blank master page , or it will take up too much space) . Strictly speaking this is v bad for accessibility and all sorts of other best practice, however in my case it was to produce printable reports so it didn't matter.
4. In my case I had a siutation where the master and child lists needed to be edited together, after much swearing, because I was trying to edit two lists on the same page, which I couldn't do, the only way I found to fix this was to update the entries in the master and use workflow to copy them to them to the details list.

KPI's in MOSS and WSS

Well you think you know how things go...and then you see something that makes you think again...in particular I didn't realise that you could slip HTML inside calculated columns - how useful is that!

This creates formulas, using some simple html and css inside a calcuated column, completely automatically, its rather lovely.http://www.pathtosharepoint.com/HTMLcc/default.aspx and a little script needs putting in a CEWP the script lives here http://pathtosharepoint.wordpress.com/2008/09/01/using-calculated-columns-to-write-html/

Also if you have MOSS and you want KPI's from your Sharepoint Lists then try this

http://sharepointservices.wordpress.com/2008/09/30/how-to-creating-a-kpi-list-based-on-a-sharepoint-lists/

Nested if statements in calculated columns

Assuming that you have a column called severity and likelihood and you've multiplied them together in column called risk score:

=IF([Risk Score]<=3,"Low",IF([Risk Score]=9,"High","Medium"))

should work out what is a high medium and low risk. The post below this, from endusersharepoint.com is fine and lays out in excruiting detail all that is necessary to do anything with calculated columns. However the above solution for nesting columns looks easier on the eye, and mind or at least my eye and mind and sort of explain itself really.

Calculated columns, formulas etc

Sharepoint calculated column Common formulas list http://office.microsoft.com/en-us/sharepointtechnology/HA011609471033.aspx

You've tried [Today] and [Me] here are some more functions http://office.microsoft.com/en-us/sharepointtechnology/CH100650061033.aspx

Introduction to the subject http://office.microsoft.com/en-us/sharepointtechnology/HA101215881033.aspx

http://www.sharepointblogs.com/mkruger/archive/2007/06/26/howto-using-sharepoint-calculated-columns-to-display-a-list-item-as-quot-x-quot-days-old.aspx