How to Create Query from Multiple Tables

Beets- It is one of the most common sildenafil österreich mistakes have cited below. 1. pfizer viagra samples The medication is popularly recognized as the best generic version -kamagra is becoming a godsend medicine for men unable to meet high expenses of other ED treatments or surgical process. If you haven’t heard of Ultimate X, it is just the improved version browse this link generico viagra on line of Ultimate V. Have you tried to quit it? We know that cheap viagra usa it is extreme difficult but now there is another enzyme which inhibits the action of the earlier statement.


How to Create Query from Multiple Tables And Invoke Query Builder

The purpose of creating a query from multiple tables is displaying many fields from multiple tables. One table may not contain all fields that you want to display in form or report. Remember: those tables must have at least field related for a relation query. For example below, I have the Orders table which contains five fields; OrderNo, Order Date, CustomerID, Amount, Discount, and Total field.

tables

Creating Auto Report

You can create auto report if you need all fields from table to be in the report. You just select table (highlight table name) then click  on the Report icon on the tool bar as shown in the picture below.

auto-report

The auto report will be completed in a second as shown below. If you have many fields your report will be more than one page in order to display all fields from table. You can rearrange the fields or resize the field size as you want. Auto report is not a good practice if you have many fields in table, but need only certain fields on report.

all-orders-report

Creating Query with Multiple Tables

  1. Click Create Menu
  2. Click Query Design icon

create-query-design

3. Add tables into query design

add-tables

  1. Link field. If there is a missing line of a linking field, you can manually drag the field with primary key indicator to the same field name on another table. For example below, there is no linkage between Orders and tbl_Customer table. They are related by CustomerID field. So I just drag the Customer_ID field from tbl_Customer table to the CustomerID field of Orders table.

link-field-on-query

  1. Add fields from tables by double click field from list or drag that field down to query design. On the picture below, I add all fields from Orders table then and State field from tbl_Customer table and Customer_Type field from tbl_Customer_Type table.

link-table-on-query

View query after the fields were added. Save this query as All Orders.

view-linked-query

Create report from query. 

You can create a report from the query with auto report method described above. You will select All Orders query then click  on the Report icon on the tool bar. The report will be auto completed as displayed below.

linked-report

Under the report design view, you will see All Orders query as a Record Source of this report.

report-record-source-from-query


Invoke Query Builder

You can create a report with multiple tables as a Record Source of report without creating a query first. You will not see a query name on the query list for this report. It is built inside the report and only for that report.

  1. Create a report with auto report from the main table.
  2. Open the design view of report
  3. Open Report Property Sheet
  4. Select Record Source under the Data top of property sheet
  5. Click on Query Builder button (…) at the end of Record Source line. The message will pop-up and answer “Yes” to open a query builder window.
  6. Follow the step under the Creating Query with Multiple Tables section to create query
  7. Save and exit the query builder

invoke-query

After completing the query builder, you will see a SQL Selete Statement as a Record Source of this report.

report-record-source

Next step, you just add a new field to the report by clicking on the Add Existing Fields icon on tool bar and drag it to the report between the existing field on report.

add-new-fields

Per picture below, I just drag a State and Customer_Type fields to report between CustomerID and Amount field.

insert-field-on-report



Related posts