Results 1 to 11 of 11
  1. #1
    brittain12 is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Apr 2016
    Posts
    11

    Trying to link tables for a query


    I have a (Collection Form) that collects all the info on the screen. I have the following form (picture below), what i am trying to do is write a query that will tell me the dates of the (Training - which selections are in drop downs) that are expiring within 30 days from the date that gets keyed in. I'm having the issue when i try and link them to the training table being there are 8 different ones. Any one have an idea?

    Click image for larger version. 

Name:	Capture.JPG 
Views:	10 
Size:	107.2 KB 
ID:	24231

  2. #2
    Perceptus's Avatar
    Perceptus is offline Expert
    Windows 7 64bit Access 2007
    Join Date
    Nov 2012
    Location
    Knoxville, Tennessee
    Posts
    659
    I imagine that trainining is entered into the training table?

    You want something like?

    Select * from temployees, ttraining, join on data that joins these where ExpDate < (DateAdd(Now,D,30))

  3. #3
    brittain12 is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Apr 2016
    Posts
    11
    sorry I'm a bit of a access newbie......but (tEmployeeCollection) has the following in it, what i cant get to work is tying the (tTraining) table to the (tEmployee Collection) which has all the Training 1, Training 2, Training 3 in them

    Click image for larger version. 

Name:	employee.JPG 
Views:	8 
Size:	102.3 KB 
ID:	24232Click image for larger version. 

Name:	traiing.JPG 
Views:	8 
Size:	111.2 KB 
ID:	24233

  4. #4
    brittain12 is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Apr 2016
    Posts
    11
    So the (tTraining) table is the DROP DOWN choices for the Training, 1 Training 2 Training 3 ect choices on the drop down.......when they are submitted they go to the (tEmployeeCollection) table with all the other data

  5. #5
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Your problem is your database design. Your data is not properly normalized, which makes querying very difficult and tedious.
    What happens if an employees has or acquires more than 5 skills, or has taken more than 8 training courses? Among other advantages, normalization will remove those restrictions.

    You need a separate table for skill, and a separate table for training.

    Those two new tables would be something like this:

    empSkills: EmployeeNumber, Skill_Code

    empTraining: EmployeeNumber, Training_Code, Training_Date
    Note that this contains the date training was taken, not the date it expires. The date it expires can be calculated using the data in the tTraining Table.

    Here is one link to help with understanding normalization (there are many): Fundamentals of Relational Database Design -- r937.com

    Proper design is essential in creating an efficient database.

  6. #6
    brittain12 is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Apr 2016
    Posts
    11
    You are correct in your statement, "What happens if an employees has or acquires more than 5 skills, or has taken more than 8 training courses? Among other advantages, normalization will remove those restrictions" that does happen and this is how its taken care of (picture)....i could not figure out another way to get around one person having multiple (skills, trainings, equipment)

    Click image for larger version. 

Name:	Capture.JPG 
Views:	6 
Size:	124.4 KB 
ID:	24240

  7. #7
    brittain12 is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Apr 2016
    Posts
    11
    an i do have different tables.......Skill has its own...Training its own.......Equipment its own..............But i have a collection table, where it all feeds

  8. #8
    brittain12 is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Apr 2016
    Posts
    11
    Click image for larger version. 

Name:	Capture.JPG 
Views:	6 
Size:	78.3 KB 
ID:	24241Click image for larger version. 

Name:	Capture2.JPG 
Views:	6 
Size:	69.7 KB 
ID:	24242Click image for larger version. 

Name:	Capture3.JPG 
Views:	6 
Size:	83.7 KB 
ID:	24243

  9. #9
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Go back to your original post. You want to write a query which will identify all training expiring within 30 days. With your current table structure, you would have to check each of 15 expiry date fields for dates within 30 days. All you would get out of that (with a query) is a list of employees that have some training expiring within 30 days - it won't tell you which training it is. In short, you could not use a query to get the answer you need.

    I'm having the issue when i try and link them to the training table being there are 15 different ones
    You can't link multiple fields containing similar data to one copy of another table. You need one copy for each link, so in your case you would need 15 copies of the tTraining table in the same query. What a mess.

    Now look at the empTable I described in post #5. It has these fields:

    EmployeeNumber
    Training_Code
    Training_Date

    You could in a query link that to the tTraining table on the Training_Code, link to the tEmployeeCollection on Employee number. From that you would get the Employee data, and the training Name, and you could calculate the expiry date from the Date of the training and the ExpiresInYears value (might need a bit of a fancy expression to deal with 999).

    You could then apply whatever search criteria to that query. But none of that can be done if you don't restructure your data to get it properly normalized.

  10. #10
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Referring to your posts #7 and #8. Yes you do have those separate tables, but they are what are called lookup tables, each describing a particular entity, but not linking data from multiple tables together. For example, the tTraining table describes the various types of training available, but it doesn't contain any data about who took training and when.

    Using lookup tables is but one part of the normalization process, and even then is not the most critical part.

  11. #11
    brittain12 is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Apr 2016
    Posts
    11
    I understand that, again I'm a novice at this big time..... so what i need to do it change where i have

    - Training 1 : NAME OF TRAINING Expiration Date: 1/5/15 and change the Expiration date to (Date TAKEN) -
    - Training 2
    - Training 3

    - then change in my (tTraining table) where i have a field of "Expires in Years") fix the 9999

    - then create a table like you described above like the picture ???

    Click image for larger version. 

Name:	Capture.JPG 
Views:	6 
Size:	29.2 KB 
ID:	24244

Please reply to this thread with any new information or opinions.

Similar Threads

  1. How to link 2 tables
    By smba in forum Access
    Replies: 2
    Last Post: 02-29-2016, 08:32 AM
  2. Link tables
    By compooper in forum Import/Export Data
    Replies: 2
    Last Post: 06-23-2011, 07:08 PM
  3. Trying to Link tables
    By brandonze in forum Access
    Replies: 1
    Last Post: 05-19-2011, 11:03 PM
  4. Replies: 3
    Last Post: 09-07-2010, 02:27 PM
  5. Best way to link tables together?
    By marleyuk in forum Access
    Replies: 1
    Last Post: 06-07-2009, 08:44 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums