MFD Query Help

Extra Alarm Association of the Twin Cities

Help and examples for query building

Jump to sample searches

The search form for queries consists of these sections:

Three FIELD SECTIONS to select a field to search, operations desired, and text to search
Two JOIN SECTIONS used to 'join' the FIELD SECTIONS above together

Each FIELD SECTION consists of:

A drop down list box used to select database column (field) you desire to search (located on the left)
A text box ((located on the right) used to enter the text value to be searched
An 'operations' selector (located in the center) used select how you would like to operate on the text you entered to be searched

Each JOIN SECTION consist of:

A 'OR' radio button: to select either 'this OR that' operations
A 'AND' radio button: to select either 'this AND that' operations
A 'No "x" field chosen radio button: if you desire NOT TO JOIN the fields together

Search may be performed on one, two, or all three FIELD SECTIONS if desired.
You are not allowed to select field three before you select field two. If you enter data into a field WITHOUT joining that field with another field, your entries will be disregarded.

The operation selector contains an IS LIKE operator. It is probably the most 'powerfull' feature, and deserves some explanation.

'Is LIKE' is a text string searching operator. It can use 'wildcards' to search text. The wildcard is the % (percent) character.

If you were looking for the text string 'John', and were searching by an 'equals' method, you would ONLY receive 'John' back as a response. 'John Smith' would NOT be found. However, if you searched for IS LIKE JOHN%, you are asking for any text string the STARTS WITH 'John', and has any text after the 'John'.

Hence you results would find, 'John Smith', 'John Doe', and 'John Brown'. You WOULD NOT find 'Andrew John Black', however, because that text string does not BEGIN with 'John'.

A new search for %John% (or %john%, not case sensitive) would find ALL instances of 'John' anywhere in the search.

So...
John% finds any text string STARTING with John.
%John finds any text string ENDING with John.
%John% finds any occurrence of the text string 'John'.

The NULL Operator:

The NULL or the NOT NULL operators are 'boolean', meaning they are either true or false. By 'true or false' it really means EMPTY or NOT EMPTY. A blank field will be FOUND if it is searched for as NULL.

Example searches:

Single field searches:

"Find all apparatus from 1980 onward"
"Find all apparatus in the 1980's"
"Find all apparatus with a 1500 GPM or larger pump"
"I want to see just the records for Engine Companies"
"Show me all records that have a serial number recorded"
"Show me only records for companies that are missing a linked photo"
"Show me all the motor apparatus ever used by Engine 8"

Multi-field searches:

"Show me only records for Squad companies up to the 1980's"
"Find all the Mack engines that had a cab added to the rig"
"Find all the Pirsch's or American LaFrances's up to 1990"
"Find all the Pirsch's since the 1950's, but I don't want the Ford/Pirsch's"

"Find all apparatus from 1980 onward"

Field Chosen	Operator	Text Chosen
Year	greater than or equal to	1980

Search results:
SELECT * FROM mfd_motor_app_new WHERE Year >= ("1980")
Your query returned 'xxx' rows
Returned ALL rows from 1980 though the present day
BACK sample searches

"Find all apparatus in the 1980's"

Field Chosen	Operator	Text Chosen
Year	is LIKE	198%

Search results:
SELECT * FROM mfd_motor_app_new WHERE Year LIKE ("198%")
Your query returned 'xxx' rows
Returned only the rows for 1980-1989.
BACK sample searches

"Find all apparatus with a 1500 GPM or larger pump"

Field Chosen	Operator	Text Chosen
GPM	greater than or equal to	1500

Search results:
SELECT * FROM mfd_motor_app_new WHERE GPM >= ("1500")
Your query returned 'xxx' rows
Returned all pumps with 1500 or larger in the GPM column (data field).
BACK sample searches

"I want to see just the records for Engine Companies"

Field Chosen	Operator	Text Chosen
Type	is LIKE	%eng%

Search results:
SELECT * FROM mfd_motor_app_new WHERE Type LIKE ("%eng%")
Your query returned 'xxx' rows
Returned all records with 'eng' anywhere in the Type field.
BACK sample searches

"Show me all records that have a serial number recorded"

Field Chosen	Operator	Text Chosen
Serial Number	NOT NULL	(entries disregarded)

Search results:
SELECT * FROM mfd_motor_app_new WHERE Serial IS NOT NULL
Your query returned 'xxx' rows
Returned all records with an entry in the Serial Number field.
BACK sample searches

"Show me only records for companies that are missing a linked photo"

Field Chosen	Operator	Text Chosen
Has a photo	NULL	(entries disregarded)

Search results:
SELECT * FROM mfd_motor_app_new WHERE ID_pic IS NULL
Your query returned 'xxx' rows
Returned all records that do NOT have an entry in the ID_pic field.
BACK sample searches

"Show me all the motor apparatus ever used by Engine 8"

Field Chosen	Operator	Text Chosen
Assignment	is LIKE	%e8%

Search results:
SELECT * FROM mfd_motor_app_new WHERE Assignment LIKE ("%e8%")
Your query returned 'xx' rows
Returns all entries that an 'E8' text string in the Assignment field.
BACK sample searches

"Show me only records for Squad companies up to the 1980's"

Field Chosen	Operator	Text Chosen
Year	less than	1980
(field join) AND selected
Type	is LIKE	%squa%

Search results:
SELECT * FROM mfd_motor_app_new WHERE Year < ("1980") and Type LIKE ("%squa%")
Your query returned 'x' rows
Returned all rows for Squads from 1979 and earlier.
BACK sample searches

"Find all the Mack engines that had a cab added to the rig"

Field Chosen	Operator	Text Chosen
Make/Fabricator	is LIKE	%mack%
(field join) AND selected
Type	is LIKE	%eng%
(field join) AND selected
Miscellaneous	is LIKE	%cab added%

Search results:
SELECT * FROM mfd_motor_app_new WHERE Make LIKE ("%mack%") and Type LIKE ("%eng%") and Miscellaneous LIKE ("%cab added%")
Your query returned 4 rows
BACK sample searches

"Find all the Pirsch's or American LaFrances's up to 1990"

Field Chosen	Operator	Text Chosen
Year	less than or equal to	1990
(field join) AND selected
Make/Fabricator	is LIKE	%pir%
(field join) OR selected
Make/Fabricator	is LIKE	%ame%

Search results:
SELECT * FROM mfd_motor_app_new WHERE Year <= ("1990") and Make LIKE ("%pir%") or Make LIKE ("%ame%")
Your query returned 'xxx' rows
Returns all rigs manufactured by Peter Pirsch and Co., OR those from the American LaFrance Co., up the the year 1990
BACK sample searches

"Find all the Pirsch's since the 1950's, but I don't want the Ford/Pirsch's"

Field Chosen	Operator	Text Chosen
Year	greater than or equal to	1950
(field join) AND selected
Make/Fabricator	is LIKE	%pir%
(field join) AND selected
Make/Fabricator	is LIKE	%ford%, with the NOT BOX checked

Search results:
SELECT * FROM mfd_motor_app_new WHERE Year >= ("1950") and Make LIKE ("%pir%") and Make NOT LIKE ("%ford%")
Your query returned 'xxx' rows
Returns all rigs manufactured by Peter Pirsch and Co., since 1950, but eliminates any Pirsch's with Ford tractors. In the text field there is a NOT check box which reverses the text chosen.
BACK sample searches

mailto: eatc7402@comcast.net