Category: Database Design

Forcing or Limiting Columns Using Cross Tab Queries

MS Access has a version of SQL that can be edited to enforce specific columns being shown in queries.

Within the query editor navigate to the SQL View

Normally when a cross-tab query is selected MS Access will generate columns for every value that exists. This is often very useful but if there are no values there may be gaps in the scale. Additionally you may wish to limit the number of columns that are displayed in the query or force limited numbers to tally with a report. Some reports will crash if certain columns are not found.

Generally the last line of SQL code within a query will follow a similar syntax to the following;

PIVOT [QueryName].YEAR;

Where YEAR is an actual field name that could alter in your circumstances

Altering this to the following will for instance generate years 2000 to 2010 with no gaps even if there are no values in certain columns.

PIVOT [QueryName].YEAR in ("2000","2001","2002","2003","2004","2005","2006","2007","2008","2009","2010");

Project Management – Things that help lead to successful outcomes

Update its interesting to compare this post with a post I made later on the same subject..

Extraction Transformation and Load some thoughts on a large IT transfer project

FinishLine

Many roles within organisations now require good project management skills especially when it comes to implementing new IT systems and applications. But are there things that can be put in place at the beginning to improve your chance of success. I would say yes and if I am involved in a project my personal guidelines are as follows;

Step 1 : Get Stuck In
The benefit of computers is that manipulated electrons are essentially free and immortal. Try to rearrange a few. If you aren’t getting anywhere wipe them and then re-arrange them some more. Even if you are not successful you are successful in knowing that one particular arrangement cannot be achieved. You are creating a machine just like children do with Lego or engineers create with bricks and mortar except your bricks can immediately be removed and copied infinitely and each additional brick often costs nothing. In most organisations you will quickly come up against configuration and security problems. Configuration and security problems come out of nowhere often and can be project killers best to know about them up front.

Step 2 : Know your Technology
If you don’t know it at the beginning you better hope you know it at the end – go to step 1 if you are struggling with step 2 – That’s recursion for you.

Step 3 : Increment often , test constantly and try to keep focused
Set short deadlines and try to regularly meet with colleagues or clients to show progress – can be frustrating if colleagues or clients start going off on tangents especially in meetings so try to keep focused on the remit.

Step 4 : Know the Process
To date I haven’t been asked to design any systems that I have had particular difficulty in understanding the process. Undoubtedly I think this would be different if I was trying to create an application for geology exploration or for instance mapping or maybe translation. The mathematics behind those kind of applications are complicated. Most business processes tend to be remarkably simple and the simple act of normalizing the data is usually enough for me to get to grips on how the system will be used.

Step 5 : Build in redundancy
Properly normalize your data build in extra fields if you want even if they are not used – for example collecting information on individuals I always add a field for date of birth even if its not spec’d invariably someone comes along and says actually it would be useful to know what age our customers are.

Step 6 : Have privileges
There’s nothing that will slow down a project quicker if you have to hand over responsibility of tasks to uninterested individuals who are not part of the project team. Better to have those people in the team and make sure they are on board with the importance of following through with the project.

Good luck and happy hunting

Dealing with Dates can still catch you out

The other day I was dealing with dates and I found a cracking little gotcha that might just be catching a few people out.

When is 07/11/14 not 07/11/14

When one is 17 November 2014 and one is 17 November 1914

I’ve been dealing with legal dates and I needed to set dates some 25 years in the future for payments of moneys. That’s fine just typing in dates I’ve been putting dates in which turn out to be a century in the past. This led me to investigate.

Within windows we get very used to typing in dates by using 6 digits – 01/01/14 or 09/05/15 – most users computers would recognise these dates as 1st of January 2014 and 9th September 2015. What many users may not consider , myself included , is that the interpretation of the year in this scenario is relative. That is relative to a century. We happen to be in the 21st century but if you type in 99 most computers will interpret the year as 1999 and not 2099. Fine but at what time does it change the century. Well turns out there is a configuration setting.

Here’s how you get to the configuration settings in Win 8.1

Control Panel
Clock, language and Region
Set the date and time…
Change date and time

Should get you to the following;

Win8ControlPanelDateTime

Then hit Change calendar settings;

Win8CustomizeDateFormat

The danger is of course that users will type in 07/11/30 thinking this will be 2030 when the two digit interpretation configuration will interpret it as 7 November 1930. How it interprets this comes down to the above setting. I believe at present this is the default setting.

Its one to watch out for and doubtless it may be catching some organisations out.

Graphics vs Text

khmerconSo today it is a bit slow for me so I answered a question from a lad in Phnom Phen in Stack Overflow. He was looking for support for Khmer in MS Access 2013, the official language of Cambodia. He has a database but unfortunately he can’t get records to sort alphabetically for Khmer text. I’m not sure I found a solution I made some suggestions. I’m always really interested in questions like that because a solution for him would be the kind of thing that would be useful to anyone able to write Khmer which is presumably millions. I had a look at the alphabet – wow – computer fonts seem to do a terrible job of displaying it reducing it to the point where at a similar scale to other fonts I need a magnifying glass to distinguish differing letters. I guess you just have to choose larger font sizes but it was tiny at the scales I was seeing examples. It made me wonder how much cultures with difficult or tricky languages are disadvantaged in day to day commerce. Obviously to me their alphabet is only pictures.

But then in reality that is only what all alphabets are.

I always think on this when people say we need more pictures and icons in the UI

OBJECT NAMING CONVENTIONS – My UI Design Patterns

I like to do several things.

Name my tables T001TextVariableInCamelCase incrementing the number
Name my queries Q001TextVariableInCamelCase incrementing the number
Name my forms F001TextVariableInCamelCase incrementing the number

This works well for me – some of my first applications only had 2 numeral positions. I now give new projects 3 digits as the more successful applications generally have more than 100 forms and queries and I like to have everything in order. I have yet to come close to more than 200 forms in an application so I feel confident that 999 is satisfactory. Of course its not a major problem if I go over 999 forms it just doesn’t order so nicely in the database window which tweaks my OCD.

All names have no spaces and are in camel case.

I like to have the forms tagged with this incremental Form Prefix – eg F001 or in the picture below F103. When you have lots of forms it is a lot easier to refer to forms by this unique key. Additionally if you are speaking to a user over the phone you can immediately identify the form that they are on that they may wish additional features on or on which they have found bugs.

These pre-fix conventions are only an implementation of the principles of database design. They are effectively primary keys for differing objects. If I was in the access team I would probably hard wire this feature into the IDE so that code referred to the primary key and text descriptions could be changed retrospectively without breaking links and if possible forms would start with the PKID placed on them. (maybe with the feature available to switch this off)

HIDE MENUS – My UI Design Patterns

Really simple – create a new module and create the following simple functions.

I usually name them TurnMenuOn and TurnMenuOff.

Public Function TurnMenuOn()

   Application.CommandBars("Menu Bar").Enabled = True

End Function

Public Function TurnMenuOff()

   Application.CommandBars("Menu Bar").Enabled = False

End Function

Then you can run at start up by creating a macro that is titled autoexec
And using the Runcode action call the TurnMenuOn() function alternatively you can call it from the form opening.

It is often useful to create a couple of straight Macros that run these functions as well so that when you are in design mode you can quickly run the functions.

SEARCHING RECORDS – My UI Design Patterns

Well if it is good enough for Google

One single variable field with filtering only occurring after return has been hit. Search is across complete range of fields and searches for a string within all of the fields. Search string is maintained after search and results shown below. Don’t make the search case sensitive unless there is a good reason for doing so.

Enabling Geospatial integration in applications.

Despite the fact that spatially enabled databases have been around pretty much everywhere for quite sometime there’s still a heck a lot of enterprise applications out there that are not using the feature even though their backends support it.

SQL server has had the facility since 2008 , Oracle has it as well although it is with the expensive Oracle 11g Enterprise edition.

So if its available why are so many applications not using it?

Well one of the reasons is that many of the applications which would benefit from introduction are central to organisations and were in existence long before the feature was available in backend databases. So why not introduce it as an update? Well the problem is a geospatial attribute is a form of primary key more accurate than the often completely arbitrary primary keys that most tables will take as their reference. Adding it is likely to require not just the addition of a geospatial attribute which will be a defacto primary but potentially adding a full table not as a child but as a parent to the previous parent records – the former parent records requiring the addition of foreign keys that relate to their parents.

As most of you know altering primary keys in tables is pretty much equivalent to transplant surgery for a database.
Totally wiping a primary key and starting with a different primary key that needs to then be captured for itself and related back to its children is if anything several orders of magnitude worse than that..

Doesn’t sound good does it.

This is another case where if you have paying clients or you have a purchased product that isn’t going to happen until there is an outside force from a competitor. But the benefits are legion. As it stands most information in geographical systems is flat files that has to be updated directly within either the web gis or a gis desktop. These programs are really terrible making an application fully geospatial by design allows the UI you to display the geographical information in the GIS – web or desktop and related information in forms which often have vastly improved searching / linking to other systems drop down boxes well pretty much everything.

Leave plenty of time for it but would be proper automation. Too many GIS systems are mirrored copies of a database that periodically have to be updated. This is not the long term optimum.

The devil is always in the detail – Setting constants to Russian cyrillics in VB

With programming the devil is always in the detail. Just out of curiosity I was thinking about the code that I have posted that randomises information in a database and I was thinking why don’t I try to randomise the names using the Cyrillic alphabet?

Totally unnecessary I know but what I did discover is that Visual Basic for applications does not support Russian Cyrillics in the coding window and therefore constants cannot be statically set to Russian Cyrillic values.

As ever people have figured out how to get around this omission.

Here’s some code from the net that may help… (haven’t tried it yet)

strString = ChrW(decimal value) & ChrW(decimal value) & ChrW(decimal value) & ChrW(decimal value) & ChrW(decimal value) etc.

http://unicode-table.com/en/

Search for the characters you want then hover over the symbol to see the decimal number.

It of course raises lots of questions- what alphabet do coders in Russia use as a general standard? – I’m sure other IDEs will support non western alphabets but I suspect many programming languages are Latin alphabet centric. Come to think of it that must present quite a challenge for any individuals with a language not based on the latin alphabet wanting to be programmers. Full respect I guess they first need to learn English to really get to grips with programming.

40 Useful Facts to Know about a System

40

A list of points I should remember to ask when doing ongoing system development, total re-design or external purchase.

For alternative systems rank both existing and new and compare.
Collection of facts is as objective as I could make it – interpretations and consideration as ever is subjective.

I have my own self built project software that records most of these points. The users and user roles is particularly useful my home baked pm software is integrated into my contacts which means if individuals titles and contact details change then the details below are automatically updated.

1. Name of system
2. Version number
3. General Description of the system (written)
4. Date of Creation
5. Name of Creator along with contact details
6. Name of Administrator along with contact details (If different from Creator)
7. Name of the person responsible for backing up the system
8. Backup description
9.Names of existing users – should include administrators and creator
(Name – Organisation Position – e-mail – contact no – approx time used per day – Method of access – Role in system – Description of tasks carried out – Machine tag numbers if required)
10.Names of potential future users
(Name – Organisation Position – e-mail – contact no – approx time used per day – Method of access – Role in system – Description of tasks carried out – Machine tag numbers if required)
11. Operating system runs on (Windows / Linux / Mac / Wine) If Linux please indicate distro
12. Backend Database type (SQL Server, Oracle, MS Access, Filemaker Pro, Omnis, Fox Pro, DB2, MySQL, PostGres, PostGIS, VoltDB, MongoDB, NoSQL)
13. – unc name of database server(s)
14. – unc name of web server(s)
15. – Important directories on particular servers
16. – name of backend database
17. Has the application been designed specifically to allow for geo referencing ( This is not a question as to whether the back end is spatially enabled ) – (Yes / No)
18. Front End technologies – list
19. Main programming languages
20. Is this a client facing system (Yes / No)
21. Could it be a client facing system (Yes / No)
22. Do individuals presently access this from mobile phones (Yes / No)
23. Do individuals presently access this from tablet devices (Yes / No)
24. Is the information available to the general public if they make a freedom of information request. ( Yes / No )
25. Please indicate the information in the system that would not be distributed even if someone requested it. [Description]
26. Is there a statutory requirement to keep this information ( Yes / No)
27. Was the system originally internally or externally created (Internal / External)
28. Capital cost of original construction (if relevant)
29. Estimated annual revenue cost of system ( not interested in wages of individuals only interested in actual costs paid to outside bodies )
30. Can we get a table structure of the system complete with all field names and field types. (Yes / No) – Please attach if yes
31. Is the process unique to the organisation( Yes / No )
32. How often is the design of the system altered ( As and when required, Changes are batched and pushed through periodically, Only in exceptional circumstances, Never)
33. Who can request changes to the system ( Anyone , only management , there is a working group of disparate users who request changes to an outside company , pretty much no one)
34. If that is an outside company makes the changes how often is that done (monthly, yearly, very rarely, never)
35. Are users presently happy with the system ( Yes / No )
36. Are management presently happy with the system ( Yes / No )
37. Any obvious other systems it could be combined with (No – Yes please list)
38. How is performance (Very good, Good, Adequate, Poor, Very Poor)
39. Can you list other organisations that use the same software (No, list of organisations)
40. Lastly where do you consider the application to be strong and where weak and do you have any specific proposals that could significantly improve your interaction with the system. [description]

VBA access code – Passing a selection of e-mail addresses to Outlook

This is similar to the previous post except uses the inbuilt SendObject object to pass the string sBcc directly to Outlook. Outlook picks up the variables and so there is no need to set up public variables.

Private Sub CommandGroupEmail_Click()
On Error GoTo Err_CommandGroupEmail_Click

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Dim MyDB As DAO.Database
Dim rsEmail As DAO.Recordset
Dim sBcc As String
Dim sSubject As String
Dim sMessageBody As String

Set MyDB = OpenDatabase("\\SERVERNAME\DIRECTORYPATH\" & "TARGET.MDB")
Set rsEmail = MyDB.OpenRecordset("SELECT ... STATEMENT", dbOpenSnapshot)

With rsEmail
.MoveFirst
Do Until rsEmail.EOF
If IsNull(![E-mail]) = False Then
sBcc = sBcc & rsEmail![E-mail] & ";"
sSubject = ""
sMessageBody = ""
End If
.MoveNext
Loop
End With

DoCmd.SendObject , , , , , sBcc , sSubject, sMessageBody, True

Set MyDB = Nothing
Set rsEmail = Nothing

 
Exit_CommandGroupEmail_Click:
    Exit Sub

Err_CommandGroupEmail_Click:
    If (Err = 2467) Or (Err = 91) Or (Err = 2483) Then
      Resume Next
    End If
    Resume Exit_CommandGroupEmail_Click
    
End Sub

VBA access code for pulling together a list of e-mails and passing to a form

Below some standard code that I use to loop through a selection of records and create a string from the individual [Email] s in the below case the e-mails are passed to a form (FORMTOOPEN) – if you are using this then all text in capitals will need to be replaced by application specific information. You want to set up a global variable that is made public which you can pass the combined rsemail (in this case SendBCC) string to between opening up forms.

I tend to have a module called modGlobalVariables and I would put this in

Public SendBcc as string

Remember to be careful with your variable definitions – If you have the same variable dimensioned locally within a command and publically over the whole project values may not appear as expected when you get them.

Private Sub CommandGroupEmail_Click()
On Error GoTo Err_CommandGroupEmail_Click

    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
    MsgBox "Please note all available e-mails are placed in BCC section of a new form in alphabetical person name order. If a person doesn't have a listed e-mail address he/she will be omitted", , "APPLICATIONNAME"

    Dim MyDB As DAO.Database
    Dim rsEmail as DAO.Database

    Set MyDB = OpenDatabase("\\SERVERNAME\DIRECTORYPATH\" & "TARGET.MDB")
    Set rsEmail = MyDB.OpenRecordset("SELECT STATEMENT HERE"
   
    With rsEmail
    .MoveFirst
    Do Until rsEmail.EOF
    If IsNull(![Email]) = False Then
    SendBcc = SendBcc & rsEmail![Email] & ";"
    MessageSubject = ""
    End If
    .MoveNext
    Loop
    End With
    
    stDocName = "FORMTOOPEN"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    
    Set MyDB = Nothing
    Set rsEmail = Nothing
    SendBcc = ""

     
Exit_CommandGroupEmail_Click:
    Exit Sub

Err_CommandGroupEmail_Click:

    If Err.Number = 2501 Then
    MsgBox "The e-mail was cancelled without sending", , "APPLICATIONNAME"
    Exit Sub
    
    If Err.Number = 3734 Then
    MsgBox "There are no Records Cancelling", , "APPLICATIONNAME"
    Exit Sub
        
    Else
    
    MsgBox Err.Number
     
    End If
    
    End If
    
    Resume Exit_CommandGroupEmail_Click
    
End Sub

Note on loading of the new form you will need to pass SendBCC into whatever text box you wish to see it in where Me.Bcc is the name of the field receiving the SendBCC string.

Private Sub Form_Load()

 Me.Bcc = SendBcc

End Sub

The Importance of a Booking Confirmation and Careful Communication

rsvp

With booking systems there are a lot of important things that will make a system successful – good operational speed – a well designed table structure – great UI and good stability but without really really great communication and confirmation with the customer you’re missing a golden opportunity.

I would argue that wedding invites represent the absolute gold standard in organising communication between parties in what is essentially a booking contract.

* Firstly you give the responsibility of organisation to a responsible and motivated party who is familiar with the whole process and why they are doing what they are doing.
* Then great care is taken in the timing of when items should be sent out (not too long before not too short)
* Addresses of all parties are carefully and thoroughly checked
* Extreme care is taken in the wording and importantly formating of communications (and often flexible and personal messages included for specific parties)
* Extreme clarity is taken to ensure party’s know who to contact if their circumstances change and RSVPs come back to the persons that sent them out.
* Direct contact is encouraged in situations lacking clarity
The RSVP process is formalised to reduce confusion ensuring that the original party can identify who communications come back from and simplified to require the minimum amount of effort on the part of the guest.
* All the while the process is constantly overseen by senior management who have complete visibilty and problems are tackled when they arise.

A good booking confirmation will act not only as a great piece of promotion for the organisation (you have the attention of the individual concerned they are listening to every word you write) but superb quality and clarity reduces confusion, reduces the requirement for clarifying further work resulting in less errors, increases the likelihood that the individuals will return to do business, probably will encourage them to book more courses and give confidence that the contract will be actually carried out.

To my mind this kind of detail should be born in mind for all system communications.

An important consideration when designing new systems.

MS Access SQL for randomising the results from a query

bullish_fractal

I was wanting to randomize the source of a form so that I could test myself on some Russian within a table. Turns out its very simple. Basing a Query on the following SQL will achieve it.

SELECT TableName.PKID, TableName.Field1, TableName.Field2
FROM TableName
ORDER BY rnd(INT(NOW*PKID)-NOW*PKID);

The nice thing about basing a form on this is that every time you open the query it will run a query and give you a complete random list but importantly if you want to navigate through the records in the form it will remember the random order and you can go back and forth in the list and it will be in the order as originally opened.

The interesting thing about this code is that it takes its seed from the time (the function NOW) the next thing I am thinking about doing is making that a definable variable that can be set automatically by the user. As I said I have used the above code to test myself on Russian Vocabulary. I have a dictionary of all the words that I have come across at present. I am given a phrase or word in English and I must type it out in Russian. This was good except when I opened the form it would test me on the table in the same order everytime. Great except I new the first part of the table well and steadily got worse as I went through the table. The table is now so big that I would never sit down and work through the whole table. Randomising the table prior to opening the form solved this but introduced a new problem. Having the same list was useful for building up knowledge of the words it effectively broke the table into a small subset. By having a variable that the user could define they or myself will be able to only move to a random list once I am confident of those words. My thinking is that this will break down what is now quite a large list into smaller parts to learn but I can still use repetition to improve my competency and rate of learning.

Wanting to demonstrate a database and need to scramble the data?

dice

Here’s a nice function I found that will completely randomize information within fields of a database. Data will not be recoverable from this process which of course is its strength.

Good if you are wanting to demonstrate a database to people that normally contains sensitive information but don’t have time to make up your own records.
Works on text fields and will randomize numbers as further numbers and letters as further letters.

Public Function ScrambleID(parmString) As String
    Dim lngLoop As Long
    Const cAlpha As String = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
    Const cNum As String = "0123456789"
    Dim strNewChar As String
    Dim strThisChar As String
    For lngLoop = 1 To Len(parmString)
        strThisChar = Mid$(parmString, lngLoop, 1)
        Do
            Select Case strThisChar
                Case "A" To "Z"
                    strNewChar = Mid$(cAlpha, Int(Rnd * Len(cAlpha)) + 1, 1)
                Case "0" To "9"
                    strNewChar = Mid$(cNum, Int(Rnd * Len(cNum)) + 1, 1)
            End Select
        Loop While strNewChar = strThisChar
        ScrambleID = ScrambleID & strNewChar
    Next
End Function

Attaching Databases to SQL Server 08R2 Express

jigsaw2
It should be noted that the following although the easiest way to get a new database into an instance it should not be used in a production environment. In fact doing so may get you sacked. If experimenting though this method should be fine. If in doubt seek help as in a production environment you would want to look through all the code before attaching anything into an instance.

Go to SQL server management studio and on the Databases tree
Right click and select
Attach…..

Attach database window should appear which will allow you to use the Add… button to navigate to the
\Data\ subdirectory where all the sql server databases are held.

IMPORTANT – prior to loading a file in the database will have needed to have been DETACHED and you should always move anything mdf file that you are wanting to put into a database into the data subdirectory.
This ordinarily is done by going to database in question scrolling down to the database and right clicking on the database

Tasks > – Detach…

WARNING
If a database has not been detached properly it may NOT be possible to re-attach the database this is of course a security feature. So experimenting with simple moving files about will not work…

The listed code below does it at command line but the above works in SSMS

The default location for databases in SQL 08R2 Express is
C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA

The default location for database in SQL 2012 is
C:\Program Files\Microsoft SQL Server\MSSQL11.[InstanceName\MSSQL\DATA

Using VBA to open URL in chrome

Haven’t tried this out but could be useful. I have a digital mapping web application that I link to from a database and it has issues with IE but works perfectly in Chrome…

shell("C:\Program Files (x86)\Google\Chrome\Application\chrome.exe -url http:google.ca")

Update : 24 June 2014 – just tested this out on a windows 8.1 device and seems to work

%localappdata%\google

Scope of variables – And Getting Confused

Note to self

If you are wanting to pass parameter values between forms ensure that you place public variables in a module outside of the form.

IMPORTANT – additionally ensure that the same variable names are NOT also listed in the function on the form. If you don’t remove the local variables of the same name. Parameters will appear to be set to the public variables but when you try and call them subsequent to the local scope closure they will be blank.

This must mean that variables are set consecutively and transferred into a memory address. If two variables of the identical name are set the first gets one memory address and the second another. Thus they may appear the same but reference different locations. For clarity be careful with your variable definitions!!!!

slide-1-638