Method and system for constructing database queries using a field selection grid

• US 5,619,688 A
• Filed: 09/02/1993
• Issued: 04/08/1997
• Est. Priority Date: 09/02/1993
• Status: Expired due to Term

• Current Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Original Assignee: Microsoft Corporation
• Inventors: Bosworth, Adam, Hunter, Ross A., Habib, David J.
• Primary Examiner(s): AMSBURY, WAYNE P
• Application Number:
  US08/116,888
• Time in Patent Office: 1,314 Days
• Field of Search: 395/600
• US Class Current: 1/1
• CPC Class Codes: G06F 16/2423 Interactive query statement specification based on a database schema
• CPC Class Codes: G06F 16/2428 Query predicate definition using graphical user interfaces, including menus and forms G06F16/2423 takes precedence
• CPC Class Codes: Y10S 707/99934 Query formulation, input preparation, or translation

Abstract

A method and system for constructing queries which make changes to an entire set of data stored in a database is provided. In a preferred embodiment of the present invention, a user selects one of more tables from which to construct a query. Next, the system stores a list box for each selected table. Each list box is displayed on a user interface including a query window which includes an upper portion and a lower portion. The user selects one or more columns from the list boxes and drags the selected columns into a QBE grid located in the lower portion of the query window. The QBE grid provides a graphical representation of the query requested by the user. When the user invokes the query, the system determines the query type and performs appropriate action on the selected column from the selected tables.

Claims

1. A method in a computer system for specifying selection criteria for retrieval of information from a database table having a plurality of fields with field names, the computer system having a display device, the method comprising the steps of:

displaying on the display device a selection grid having rows and columns, the selection grid having one of the rows designated as a field name row having empty fields for containing user-specified field names and one of the rows designated as a criteria row for containing selection criteria corresponding to the field name in the same column;

specifying by a user of the computer system one field name of the database table for which to specify a selection criterion;

displaying the user-specified field name in one of the empty fields of the field name row;

specifying by the user the selection criterion for the user-specified field name; and

displaying the specified selection criterion within the criteria row in a column corresponding to the user-specified field name.

2. The method according to claim 1, prior to the step of specifying by a user of the computer system one field name of the database table, further comprising the step of displaying on the display device the plurality of field names of the database table, wherein the step of specifying by a user of the computer system one field name of the database table includes the steps of:

using a pointing device,

selecting the one field name from the displayed plurality of field names;

dragging the selected field name to one of the empty fields of the field name row; and

dropping the selected field name into the one of the empty fields of the field name row.

3. The method according to claim 1, after the step of displaying on the display device a selection grid, further comprising the steps of:

selecting one of the empty fields of the field name row; and

displaying the plurality of field names of the database table responsive to the selection of the one of the empty fields.

4. The method according to claim 3 wherein the step of specifying by a user of the computer system one field name of the database table includes the step of selecting one of the displayed plurality of field names.

5. The method according to claim 3 wherein the step of displaying the plurality of field names of the database table includes the step of displaying the plurality of field names in a drop down menu in proximity to the field name row.

6. The method according to claim 1 wherein one row of the selection grid is designated as a show row having fields, each field for specifying whether the corresponding field name is to be displayed in a report generated according to the displayed selection grid, the method further comprising the steps of:

selecting one field of the show row; and

displaying in the selected field an indication that the corresponding field name is to be displayed in the report.

7. The method according to claim 1 wherein one row of the selection grid is designated as a sort row having fields, each field for specifying whether the corresponding field name is to be sorted in a report generated according to the displayed selection grid, the method further comprising the steps of:

selecting one field of the sort row; and

displaying in the selected field an indication that the corresponding field name is to be sorted in the report.

8. The method according to claim 1 wherein the user-specified field names and the selection criteria define a query specification, the method further comprising the steps of:

selecting by the user a query language representation for the query specification displayed in the selection grid; and

displaying on the display device the query specification in the query language representation according to the user selection.

9. The method according to claim 8 wherein the step of selecting includes selecting a structured query language (SQL) representation.

10. A method in a computer system for specifying a cross tabulation for a database table having a plurality of fields, each field having a field name, the computer system having a display device, the method comprising the steps of:

displaying on the display device a selection grid having rows and columns, one of the rows of the selection grid being designated as a field name row having empty fields for containing user-specified field names and another one of the rows of the selection grid being designated as a cross tabulation row;

receiving, from a user of the computer system, the field names to use in the cross tabulation and displaying within the empty fields of the field name row of the selection grid the received field names; and

receiving from the user a function of each of the displayed field names and displaying the received functions within the cross tabulation row, whereby the cross tabulation is generated in accordance with the displayed selection grid.

11. The method of claim 10 wherein the step of displaying the received functions within the cross tabulation row displays each received function in the same column in which the corresponding field name is displayed.

12. The method of claim 10 wherein a row of the selection grid is designated as a totaling row, and the method further includes the step of receiving from the user an indication of an action to perform in the cross tabulation and displaying within the totaling row the indication of the action.

13. The method of claim 10, including the step of, in response to a request by the user, displaying a list of functions available for the cross tabulation.

14. The method of claim 13 wherein the step of displaying the list of functions displays the list in a drop down menu in proximity to the cross tabulation row.

15. The method of claim 10 wherein the function to be received from the user is one of a row heading function, a column heading function, and a value function.

16. A computer system for identifying a field name of a table for retrieving information from the table, the table having a plurality of field names, the computer system comprising:

a display device for displaying the plurality of field names;

a query grid displayed on the display device, the query grid having a plurality of columns and a row designated as a field name row having empty fields for containing field names; and

a pointing device for inserting into one of the empty fields of the field name row one of the displayed plurality of field names to identify the field name of the table from which to retrieve information.

17. A method, in a computer system having a display device, for specifying selection criteria for retrieval of information from a database table having a plurality of fields with field names, the method comprising the steps of:

displaying the plurality of field names of the database table in a first portion of the display device;

displaying a query grid in a second portion of the display device, the query grid having rows and columns, the query grid having one row designated as a field name row for containing user-selected field names and another row designated as a criteria row for containing selection criteria; and

selecting by a user a field name from the displayed plurality of field names;

displaying the selected field name in one column of the field name row;

specifying by the user a selection criterion for the selected field name; and

displaying the specified selection criterion in a column of the criteria row corresponding to the selected field name displayed in the field name row.

18. The method according to claim 17, after the step of selecting by a user a field name from the displayed plurality of field names, further comprising the steps of:

dragging the selected field name to a column of the field name row; and

dropping the selected field name into the column of the field name row.

19. A method, in a computer system having a display device and a pointing device, for specifying selection criteria for retrieval of information from a database table having a plurality of fields with field names, the method comprising the steps of:

displaying the plurality of field names of the database table in a portion of the display device;

displaying a query grid in another portion of the display device, the query grid having rows and columns, the query grid having one row designated as a field name row for containing user-selected field names and another row designated as a criteria row for containing selection criteria; and

using the pointing device,

selecting by a user a field name from the displayed plurality of field names;

dragging the selected field name to a column of the field name row; and

dropping the selected field name into the column of the field name row;

specifying by the user a selection criterion for the selected field name; and

displaying the specified selection criterion in a column of the criteria row corresponding to the dropped field name displayed in the field name row.

20. A method in a computer system for updating a database table having fields with field names, the computer system having a display device, the method comprising the steps of:

displaying on the display device an update grid having rows and columns, wherein one of the rows is designated as a field name row having empty fields for containing user-specified field names and another one of the rows is designated as an update row;

specifying by a user of the computer system one field name of the database table;

displaying the user-specified field name in one of the empty fields of the field name row;

receiving from the user an update specification, the update specification specifying how to update the field with the displayed field name; and

displaying in a column of the update row the update specification, whereby the computer system updates the database table in accordance with the update specification.