How do I create insert queries to add database information?

Top   « Previous  Next »

 

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.

 

table-relationships

 

Each Table has the following data for this example.

 

patient-details

 

doctor-details

 

report-table

 

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.

 

insert-connectdb

 

 

We move to the Users & Groups section

 

 

Next we click on Custom Query Values and add in 'Patient' and 'Doctor'. This will allow us to match each patient's mobile number with their reporting. 'Patient' and 'Doctor' are given unique values for each patient and also inserted in to the query builder.

 

Note: Don't call these custom query values the same name as the fields in your database.

 

insert-cqv

 

 

Next we add each patient as a user and add their messaging details. We then move to the Custom Query Values tab and add values for each user related to their 'Patient' and 'Doctor'. 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.

 

insert-user-cqv

 

 

Next move to the Query tab and add an INSERT query like below and Save it.

 

insert-query

 

 

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.

 

insert-qb

 

 

You can't test this inside the MobileDataNow application because any data is going to be related to the patient (otherwise the 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 use 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. You would add your own messaging details in here.

 

insert-messagebuilder

 

Start the mail service and when a patient sends in their measurements in the format 'X Y Z', it will be updated in the database at the current date and time the user submitted the info.

 

Note: This is for instructional purposes only, and since Current Date/Time is not supported by Microsoft Access, in reality you would need to use a different database.