An INSERT query is used to add a new record to a database table.
Using this is best demonstrated by an actual example. We'll also use Custom Query Values to match a patients phone number with their medical record.
This shows an example of a patient who would send in daily medical test results they do at home from their mobile phone.
The patient would send an sms message with three measurements for Blood Pressure, Glucose and Cholesterol. Measurements they have taken themselves.
The format of the message would be three numbers representing measurements of each test, each separated by a space and sent to a pre-defined mobile number. eg. send: 170 45 56 to +1-554-567-8976
The nice thing about this is the patient can enter the phone number in their address book as 'Medical tests' or similar and only needs to remember the order the three results are sent in.
First of all, lets assume a simple database with 3 tables. Patient_Details, Doctor_Details and Report_Info. Both the Patient and Doctor tables have a 1 to many relationship with the Report table.
Each Table has the following data for this example.
Since this is a Microsoft Access Database, we first connect the medical database to the ODBC Administrator, in the Windows Control Panel.
Next, we connect the database in the MobileDataNow application like so.
Next, create a View called Report. We are only interested in the Report Table, so we select that and Save.
We add a Groups called Patients, like so.
Next we click on Custom Query Values and add in PatientID and DoctorID. This will allow us to match each patient's mobile number with their reporting. PatientID and Doctor ID are given unique values for each patient and also inserted in to the query builder.
Next we add each patient as a user, add them to a group and add their messaging details. We then move to the Custom Query Values tab and add values for each user related to their PatientID and DoctorID. This means, when the patient sends in their query, MobileDataNow recognizes it is from that patient and not from anyone else. This also makes it easier for the patient as they don't need to send in their patientID or Doctor's name when they submit info as these figures are already entered when the mobile phone number is recognized.
Next move to the Query tab and add an INSERT query like below and Save it.
Move to the query builder tab and complete it like below. The Wizard has filled out the table as follows and we add in the query values. The PatientID and DoctorID are filled in automatically when a query is executed and the patient only needs to enter in their three measurements.
Note: Report Date will need to use the 'Current Date/Time' option. If you're using Microsoft Access, Current Date/Time is not supported.
Note: We've minimized the Manual SQL Syntax window to make the queries easier to see.
You can't test this inside the MobileDataNow application because any data is going to be related to the patient (otherwise PatientID and DoctorID would end up as records in the database) and you wouldn't want to add testing data to the database.
Next, move to the message builder tab and select the messaging application we'll use for this. In this case we'll an email address mdntest@mobiledatanow.com and will leave the keyword area blank, since we don't want patients to have to remember additional info.
Start the mail service and when a patient sends in their measurements, it will be updated in the database.