Contents - Index

Query

Introduction
This page is used to control the special queries that you have created. The queries that you create are only available to you.

Queries are used to retrieve specific data from the database. The database consists of many data files, each of which has many data fields. You need to have a basic understanding of what data is in which file in order to create a query that makes sense.

"Relational Databases 101"
There are far too many fields in Lading Manager for them to be stored in one file, so the data fields are grouped logically by their purpose (or function). For example, all of the origin related fields are kept in a file called, "Origins," all of the carrier information is kept in a file called, "Carriers," and names and addresses for consignees are kept in a file called, "Cons." (etc, etc, etc.)

Each of these files have a common field that links them together. For some of Lading Manager's files, this is the BOL number field. The CarInfo, CustOrderInfo, and Lading files each have a BOL number field. This field "links" the three tables together.

So that you do not need to be a rocket scientist or a database administrator, Lading Manager simplifies the query process by "knowing" how all of the tables relate to each other. You also do not need to know SQL (standard query language) as Lading manager will create the SQL coding for you. All you have to do is select the fields that you want and Lading Manager will do the rest.

Layout
The button is used to add a new query. Queries are named for you and cannot be changed. Lading Manager starts naming at "Query000001," which means that you can have up to 999,999 queries. 

The grid below the button is a list of the queries that belong to you.

The button is used to "clone" or copy an existing query to a new query.

The button is used to delete an existing query.

The large area on the right is comprised of the Lading Manager files (or tables). (The buttons to the right of this will be explained later.) This area will be referred to as the query fields.

The area below the list of queries contains special features that may be used with your query.

Tables Available
CarInfo Carrier Information table. This contains the BOL Carrier Information detail lines. Sorted by BOL number and sequence number.

Carriers Carrier detail. Sorted by carrier number.

Cons Consignee detail. Sorted by consignee number.

CustOrderInfo Customer Order Information table. This contains the BOL Customer Order Information detail lines. Sorted by BOL number and sequence number.

Lading BOL main file. Sorted by BOL number.

Deal Deal related data, such as driver information, depreciation, non-applicant spouse, etc. Sorted by account number and deal number.

Origins Origin detail. Sorted by origin number.


A Simple Query
Let's do a very simple query. You want to see all of the bills that went to consignee "ACME." All you wish to see is the date of the bill and who the carrier was. (We will assume that "ACME" is the consignee's number.)
  • Start a new query by pressing .
  • Give the query a title by typing ACME in the Title field under the list of queries grid.
  • In the query fields, click the + in front of Lading. This will expand the Lading file to show all of its data fields.
  • Click the box for Lading Date.
  • Click the box for Lading Consignee Number.
  • Click on the Lading Consignee Number title so that it is highlighted.
  • Press the button to the right of the query fields box. This will bring up the Lading Manager Query Field Selectors window. This is used to provide selection criteria for the field. In our example, we want "ACME" BOLs only.
  • Type an ACME in the first selector field.
  • Press to save and return to the query screen. Notice that the field title turns red when selection criteria is present for a field.
  • Click the - in front of Lading. This will hide the Lading file data fields.
  • Click the + in front of Carriers.
  • Click the box for Carrier Name.
  • Press to save the query.
  • Press to run the query.

    (The button is used to uncheck all field boxes.)


    Query View Screen
    The query will process and send you to the Lading Manager Query View screen when it has completed.

    You will notice right away that you were given more data fields than you checked. You will always get an RCtr field. This is simply a record counter. 

    The next 3 fields are ones that we asked for. 

    The remaining fields will appear on every query that you do. The report starting and ending dates are shown, but in our simple example, we did not apply the date range. Therefore, these dates are meaningless to us in this example. 

    The query results may be sorted by any of the fields simply by clicking on the field's column heading.

    Query data may be exported via the button. There are a number of export formats available. Use the down-arrow button in the "Save as type:" box to review your options. If you choose to export as Excel Data, Lading manager will automatically provide spreadsheet headings for you.

    Special Features
    Field to apply date range against
    Lading Manager can apply the report date range against any date field in the database.