Special Topic

30 January 2011 - 13:18

Full outer join in Access

During a course a participant informed about Access' possibility to create a full outer join.
If you've gone into the topic of joins, you might have realised too there isn't really a feature doing that - there's an inner join, a left and right join, but no way in one go to get a full outer join.

Of course I didn't needed more to get my curiosity taking off, and I found a really well written and clarifying article from the hand of Gregory A. Larsen.

His simple device is: if it can't be done in one time, do it in 3 + one time: create the 3 distinct queries that are possible and with a 4th union query you just combine those results.
Cool !

Here's another example from the Northwind Database:

SELECT Customers.CompanyName, Customers.CustomerID, Employees.LastName, 
Customers.City, Employees.FirstName, Employees.City
FROM Customers INNER JOIN Employees ON Customers.City = Employees.City

UNION ALL

SELECT Customers.CompanyName, Customers.CustomerID, Employees.LastName, 
Customers.City, Employees.FirstName, Employees.City
FROM Customers LEFT JOIN Employees ON Customers.City = Employees.City
WHERE (((Employees.City) Is Null))

UNION ALL

SELECT Customers.CompanyName, Customers.CustomerID, Employees.LastName, 
Customers.City, Employees.FirstName, Employees.City
FROM Customers RIGHT JOIN Employees ON Customers.City = Employees.City
WHERE (((Customers.City) Is Null))
(1) Comments ·
29 January 2011 - 01:48

Handling multi-selection lists in Access

One of the new features in Access 2007 was the possibility to implement multivalued fields, but that's not always going to help out for various reasons.
So handling data from lists where multiple selections are accepted remains a interesting challenge.

Here are some links I found helpful in implementing a solution for this:

(0) Comments ·
20 January 2011 - 05:42

Office Manuals for Download

The Mouse Training group has released their Office Manuals 2007 (2003 & 2000) are still available as well.

They form a good basis for those who are in to self training and are thus worth a download: http://www.mousetraining.co.uk/ms-office-training-manuals.html
http://www.microsofttraining.net/microsoft-training-manuals.php

(6) Comments ·
19 January 2011 - 06:04
09 January 2011 - 14:26

Conditional Formatting for Excel charts

If you'd like to obtain some conditional formatting in your Excel charts, you'll have to as roll up the sleeves.
As there's no immediately feature for that - unlike of course the (greatly enhanced since Excel 2007) conditional formatting possibilities for cells- you'll have to proceed through VBA.

conditional formatted chart

Here are some links and samples to get you going:

If you really and rather go for something without VBA, then Andy Pope has some interesting scenarios for some specific situations:

(1) Comments ·
Page 1 of 1 pages