Access07: Hire Co : Hire Query : Hire x Equipment

In order for the Client Hire Form to work correctly, the sub form needs to contain a combination of Hire information and the related equipment information. This is achieved by creating a query that joins the tables (Hire and Equipment).

1. Create > Query Design

2. In the Show Table dialog popup, double click tblHire then tblEquip, then close the pop-up

3. From each of the tables displayed double click the fields to add to the query, as shown in the diagram below.



4. Save the query as qryHirexEquip (this is important if you want to use the Query Setup | Builder

Adding a calculated field
What we would also like is for the query to automatically calculate the Hire Length (Hours) x Equipment Hire Charge. Note that are are from different tabbleds but both have been included in the query. What we can do is add a calculated field to the query. This is achieved as follows.

1. In the design view of the query, select the next available column. (You may want to make the other columns smaller - like I have)

2. Click on the Builder option (in the Query Setup panel)

3. The builder should show qryHirexEquip as open

4. Double click HirLengthHrs, click [*], double click EqHireCharge

5. Click [Ok] to close the expression builder and the result will be transferred to the query column



6. Finally change Expr1 (at the start of the formula) to HirEquTotal. What yu have done is added a new column to the query called HirEquTotal, with the calculation [HirLengthHrs] * [EqHireCharge]

7. Finally save the query.

8. If you change the view to Datasheet view you can add data to the database using this query.

Note: What you really did was create an SQL Query that is applied to Microsoft Access. If you change the view to SQL View you will see the query:

SELECT tblHire.HirID, tblHire.ClID, tblHire.EquID, tblEquip.EquLDsn, tblHire.HirDateOut, tblHire.HirLengthHrs, tblEquip.EquHireCharge, tblHire.HirDateIn, [HirLengthHrs]*[EquHireCharge] AS HirEquTotal FROM tblEquip INNER JOIN tblHire ON tblEquip.EquID = tblHire.EquID;