Join Using MS Access GUI

• Open the HealthyGym database by double clicking on it
• The window will show all the tables in the database. Click on 'queries' under 'Objects' in the left hand side of the window.
• Now, click on 'Open Query in Design View'
• Select 'Gymmembers'in the 'Show Table' pop up window. Click on 'Add'. The Gymmembers table will be added to the blank top half of the Query design area. In the same way, add the 'GymService' and 'MemberService' tables. Click on Close.
• At the bottom half of the design screen, select 'MemberAddress' next to 'field:'. In the next row, 'GymMembers' will automatically show up next to the table. Select 'MemberName' in the next column.
• In the third column, select ServiceName. In the next row, 'GymService' will automatically show up next to the table.
• Click on the box in the same column so that it is unchecked. You do not want the ServiceName field to function as the output.
• In the same column, enter LIKE 'Aerobics' next to 'Criteria:'
• In the next column, next to field, select the following value 'GymMembers.*' - This will automatically select the GymMembers table. Now, select 'MemberID' in the field where you see 'GymMembers.*' . click on the box nex to 'Show:' so that the check mark goes away and enter [MemberService].[MemberId] next to Criteria.
• In the next column, next to field, select the following value 'GymService.*' - This will automatically select the GymService table. Now, select 'ServiceID' in the field where you see 'GymService.*' . click on the box next to 'Show:' so that the check mark goes away and enter [MemberService].[ServiceId] next to Criteria.
• Now, click on the run query - exclamation '!' icon on the top toolbar
• You will get the same results as screenshot 7a.
• Close this window. Save the query under a suitable name if you wish, when the confirmation pop up asks you if you want to save or quit

Screen Shot 7b: JOIN query through Access GUI

Here are a few more joins that you can try out on MS Access.

• SELECT GymMembers.MemberName, GymMembers.MemberAddress
  FROM MemberService, GymService, GymMembers
  WHERE MemberService.MemberID = GymMembers.MemberID
  AND GymService.ServiceID = MemberService.ServiceID
  AND GymService.ServiceName LIKE 'Aerobics'
  ORDER BY GymMembers.MemberName ASC
  
  What this command does is perform the same JOIN function that we have discussed earlier. Additionally, it sorts the member's names in alphabetical order.
• SELECT GymMembers.MemberName, GymMembers.MemberAddress, MemberPayment.PaymentExpire, EnrollmentScheme.SchemeName
  FROM MemberPayment, GymMembers, EnrollmentScheme
  WHERE MemberPayment.MemberID = GymMembers.MemberID
  AND MemberPayment.SchemeID = EnrollmentScheme.SchemeID
  AND MemberPayment.PaymentExpire >= 10-01-06
  
  Give me the names, addresses, plan expiry date and enrollment program of all current Gym members
• SELECT GymEquipment.EquipmentID, EquipmentType.EquipmentDesc, GymEquipment.EquipmentServiceDue
  FROM EquipmentType, GymEquipment
  WHERE EquipmentType.EquipmentTypeID = GymEquipment.EquipmentTypeID
  AND GymEquipment.EquipmentServiceDue <= 12-31-06
  
  Give me a list of all the Equipment that is due to be serviced on or before the end of the year