Taking relationships one step further, we can see some alternatives in the way we create the join. Enforce Referential Integrity means that the user will not be allowed to enter a record in the pay table that does not have a corresponding reference in the dept table. You can also not delete a department record if there are relationships in the pay table. You also have two other options that can be selected, the cascade options. If you cascade on update that means changes made to the dept table will be made in the pay table and if you cascade delete then deleting a dept will delete related records in the pay. In my opinion, both should be selected with caution but can be valuable in the right situation.
The join type allows you to create the join based on matches or on records missing from either table. See the choices on the screen above.
Now let's look at the query where I want to print out information about the employee including the salary in one column and the hours times the pay per hour in another column. In the column that includes the multiply I simply entered emppayhr * emphrs. What you see is how Access displayed it.
Note that in the Access or SQL I could change the name of Expr1 to get a more meaningful name simply by typing in the new name.
In the query example shown, I am just using one table. However I am changing the data in the fields and adding criteria. For name, I want to print the last name concatenated with the first name. I did this using build (the icon looks like a wand with magic dots beneath). I went over and created it by a combination of key strokes and selection. I did the same thing with appx_pay. With salary I simply changed the column header.
In addition I wanted to only print department 12 if the salary was greater than 40000 or if the appx_pay that I calculated was greater than 40000. To do this in Access I must write it as department 12 and salary > 40000 OR department 12 and appx_pay > 40000. Note that the department test is repeated on both lines.
When you look at expression builder you see three columns at the bottom. The left most column shows all of the objects within the database. When you select something from this column it brings up the information in the middle and right column. In my example, I selected the query so the fields within the query are shown in the middle. To build the expression, you can use a combinations of keying in and select and paste to generate the information needed. I strongly suggest you read about the Expression Builder in Access Help - it gives more complete explanations and examples.
In the expression above, I have named multiplied the emppayhr by hrs by 52 to get the approximately yearly salary which is appx_pay.
This is the expression builder for the name. The first thing is the column title, name:. This is followed by the name and then the + for concatenation and then a comma and then the + for concatenation and then the firstname. Note that the construction [pay]![empfname] means that the empfname is contained in the pay table.
The results show only department 12 and only where either salary or appx_pay is greater than 40000. Note in the results that the salary is edited to currency because it was defined as a currency field on the input. If I click on the column in the design and then click on the properties icon, I can set the format to currency and change the look of the output as shown below.
In this example currency was selected as the format for appx_pay.
In the next example I show another way to do an OR query and I also change the column headers on all columns. This query wants all employees with a type = H or a type =P. The column headers are shown in field followed by a colon and then the name of the actual field.