How to create a Query for Report
In this How To, I will show you how to create a query for a report. The report will be empty if you don’t have any data on the Record Source of report. It is a simple way to create the query to be used as the Data Record Source for report.
In this How To, I will use a Northwind Access Database to create the query. If you want to create the invoice like the picture below, invoice #77, you will need to create the query to have all information to display on this invoice. You can create one query and save it for other report or you can create a query using a Query Builder for its own report.
If you can get all information from one table for the report or invoice then you don’t have to create the query. However, most invoices or reports contain information from many tables. In order to have many fields from different tables in one place, we need to create the query for this invoice.
Rest the role of on line cialis medicine will make your sensuality double and you can easily enjoy intimacy that you were missing due to the problem of ED. Forzest 100mg pill work excellently and lets you have http://icks.org/n/data/ijks/1482459755_ij_file.pdf viagra 50mg amazing and firm erections in you. Medical research and clinical studies support the therapeutic actions of antioxidants, vitamins, enzymes, minerals, probiotics, trace elements, phytonutrients, healing mineral viagra online canadian water, etc. in numerous chronic digestive disorders counting chronic pancreatitis. When blood flow enhances in the penis, it leads to rock hard erection that last for long time, enabling men to perform and impress their lady love in http://icks.org/n/data/conference/1482731836_agenda_file.pdf buy generic viagra bed.
Per the example invoice above, the information on this invoice comes from the following tables below.
Customer table: Fist Name, Last Name, Company, Address, City, State, Zip Code, Country
Orders table: Invoice # (Order ID), Order Date, Date Shipped (Shipped Date), Employee ID, Freight (Shipping Fee)
Order Details: Order ID, Product ID, Unit Price, Quantity, Discount
Products table: Product ID, Product Name – for a Product name
Employee table: First Name, Last Name – for a Saleperson field.
Why do we need Product and Employee table?
On the query below, you can find a Product ID under the Order Details table and an Employee ID under the Orders table. You will see the Employee name (or Saleperson) or Product name on the datasheet view of the query below. The Employee ID and Product ID are foreign keys which are linked to the ID of another table. So these fields will display the ID number on the invoice instead of the employ name or product name.
Query:
Datasheet View:
From the above datasheet view, the Employee (Sales person) is Anne Hellung-Larsen and Product is Northwind Traders Boysenberry Spread. However, number 9 is displayed on the Employee (Sales person) field and number 6 is displayed on the Product Name filed on the Invoice. So we still need the lookup tables of Employee ID and Product ID in the query.
In the final query below, I need to add the Product table to get a Product Name and add an Employees Extended query to get the Employee Name (Sales Person), Employee Full name. You can use the Employees table to get the Employee Name (Sales Person) as well. The Employees table doesn’t have the Full Name fields, but you can use a [First Name] field and [Last Name] field as [Full Name]: [First Name] & ” ” & [Last Name].
The Customers table is also needed for the customer information such the customer name, address, etc. There is no Customer Full Name, but you can create a new filed on the query as CustomerName: [customers].[First Name] & ” ” & [customers].[Last Name]. Since there are [First Name] and [Last Name] fields on both Employees Extended and Customers tables. So we have to use the table name in front of the field name as [customers].[First Name] and [customers].[Last Name].
All the information is displayed correctly in the completed invoice below. A Price field is not appear in any table, but we need a create a new calculated field in the query as ExtendedPrice: CCur(Nz([Unit Price]*[Quantity]*(1-[Discount]),0)/100)*100.
You also will need to create a Subtotal and Invoice Total fields for the Invoice by input a text box with a format of Currency and with a Data Control Source as
Subtotal: Data Control Source =Nz(Sum([ExtendedPrice]),0)
Invoice Total: Data Control Source =[InvoiceSubtotal]+[Shipping Fee]
