Notes Access (Relationships and Queries):

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.

SELECT pay.empid, pay.emplname, pay.empsalary, [emppayhr]*[emphrs] AS Expr1, pay.empdept, dept.deptname
FROM dept INNER JOIN pay ON dept.empdept = pay.empdept;

empid

emplname

empsalary

Expr1

empdept

deptname

12345

Ash

$45,000.00

0

12

IS

78901

Smith

$60,000.00

0

12

IS

90123

Brown

$0.00

1200

12

IS

23456

Costa

$0.00

600

15

Sales

45678

Trent

$0.00

200

15

Sales

56789

Brooks

$50,000.00

0

15

Sales

92345

Hersey

$0.00

500

15

Sales

94567

Doe

$0.00

1000

15

Sales

34567

Farmer

$0.00

540

17

Payroll

67890

Souza

$55,000.00

0

17

Payroll

89012

Ames

$65,000.00

0

17

Payroll

91234

Daniels

$0.00

500

17

Payroll

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.

SELECT pay.empid, pay.emplname, pay.empsalary, [emppayhr]*[emphrs] AS HrPay, pay.empdept, dept.deptname
FROM dept INNER JOIN pay ON dept.empdept = pay.empdept;

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 SQL for this query:

SELECT pay.empid, pay.emptype, pay.empdept, [emplname]+","+[pay]![empfname] AS name, pay.empsalary AS salary, [pay]![emppayhr]*[pay]![emphrs]*52 AS appx_pay
FROM pay
WHERE (((pay.empdept)="12") AND ((pay.empsalary)>40000)) OR (((pay.empdept)="12") AND (([pay]![emppayhr]*[pay]![emphrs]*52)>40000));

empid

emptype

empdept

name

salary

appx_pay

12345

S

12

Ash,Susan

$45,000.00

0

78901

S

12

Smith,Robert

$60,000.00

0

90123

H

12

Brown,Jeffrey

$0.00

62400

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.

empid

emptype

empdept

name

salary

appx_pay

12345

S

12

Ash,Susan

$45,000.00

$0.00

78901

S

12

Smith,Robert

$60,000.00

$0.00

90123

H

12

Brown,Jeffrey

$0.00

$62,400.00



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.

SELECT pay.empid AS id, [emplname]+", "+[pay]![empfname] AS name, pay.emppayhr AS [pay/hr], pay.emphrs AS hours, [pay]![emppayhr]*[pay]![emphrs] AS pay
FROM pay
WHERE (((pay.emptype)="H" Or (pay.emptype)="P"));

id

name

pay/hr

hours

pay

23456

Costa, Joseph

$15.00

40

600

34567

Farmer, Linda

$12.00

45

540

45678

Trent, Mary

$10.00

20

200

90123

Brown, Jeffrey

$30.00

40

1200

91234

Daniels, Stephen

$25.00

20

500

92345

Hersey, Carl

$25.00

20

500

94567

Doe, John

$20.00

50

1000