Join muliple tables in one query field. PLEASE HELP!!!

[agh5147]

Hello. I am having a few minor issues with access 2013. I will explain the background of the task so that you understand the concept. Essentially I have made my database to track certifications employees have. I have laid out my database by having a kind of main table with all the employees and what teams they are assigned to. Branching off of that I have made individual tables for each team and placed the employees on that team and the certs. that employee completed within the table. I have made a search form to make it easy pull the data back by querying the information. I am able to pull back the employee name, id and team affiliation, but I am not able to pull back the certs they have.

The Problem:


Within my query I have fields for ID, First Name, Last Name, and Team name. I want to add a Certifications field that pulls from each individual table. The problem is I don't know how to link team abc, team def, and team ghi in one field in my query that grabs the data from their individual team tables. \

PLEASE HELP!!!

[orange]

Welcome to the forum.

You start by saying "I made a database" - which doesn't really tell us the business problem/opportunity you are trying to solve/support with automation.
I see Employee, Certification, Team. But I'd like to see your description (in plain English) of what you are trying to solve/support.
Anything Access is a HOW to do what you are trying to accomplish. Let's get the WHAT clearly defined before jumping to HOW.

I realize you are new to the forum, and I'm not trying to be critical. Just trying to get your requirements clarified before we offer options/suggestions.

[Bulzie]

And to go along with what Orange is asking, maybe also provide a sample of your data in the table structure you have set up and folks can help review those and offer suggestions.

In just reading your post above, seems like you would have something like:
tblEmployee(EmpID(autonumber), Name, Age, etc. TeamID here instead of tblEmpCerts if an employee can only be on 1 Team)
tblTeam(TeamID(autonumber), TeamName, etc.)
tblCerts(CertID(autonumber), CertName, etc.)
tblEmpCerts(EmpCertID(autonumber), EmpID, TeamID, CertID) So first 3 tables link to this table by the ID fields to get all your data.

[agh5147]

Orange,

What I am trying to do is. Make a database that gives me the ability to track each employees certifications. I was asked to use access for it and am somewhat new to access. What I want to be able to do at the end is use a search form and write in a certain certification employee or employee id and get information back from the criteria search. (ie. Show me all the employees that have certification 1). One thing to note is I do not have the autonumber field for employee ID because employee ID contains letters and numbers.

[orange]

Where is the information currently? That is the info/data you are going to put in/manipulate with the database/Access.

I agree with Bulzie's approach to the tables, provided there isn't something you have forgotten to describe.

Employees may have 0,1 or more Certifications.
It allows you to have Certification(s) which may not apply to any Employee, yet.

[agh5147]

The thing confusing me about Bulzie's approach is that each team has there own certs that are completed by the employee on that team. so it wouldn't just be a tblempcerts it would be team1empcerts, team2empcerts, etc.

[orange]

Hmm?? I don't recall that you mentioned Team Certs...
Tell us about a typical day in the life of an Employee who is part of a Team and where Certs fits in.

A Cert applies only to a Team? Only to an Employee? Both Team and Employee?
Please give us some examples so we have some context of your environment and issue.

[agh5147]

so a certification would correspond to an employee that is under alpha team. An employee is only apart of one team. An employee on alpha team can have the same certification as some on bravo team, but as I said the certification would correspond to the employee that is on a particular team.

[orange]

Ok, better.

What is alpha team? How does alpha team differ from bravo team?
Do team memberships change and, if so, how often?

Step back and tell us how Employee Bob gets assigned/delegated/chosen to be on alpha team.

certification would correspond to the employee that is on a particular team.

What exactly is a certification? Remember we only know what you write in your post.

[agh5147]

Ok so the difference between alpha bravo Charlie teams is the work they are assigned on a daily basis. this work has nothing to do with the information needed for this database. The big thing is no employee is one more than one team. so I have in my database I have information laid on in as such
tblrescuers (this has every employee with there id(primary key), first name, last name, and team name)
then I have one to one relationships from that parent table to the teams. (using their ID as the relationship link) (id include letter and numbers ie. ah12345)
tblalpha (id, first name, last name, certifications completed, certification date)
tblbravo (id, first name, last name, certifications completed, certification date)
tblcharlie (id, first name, last name, certifications completed, certification date)
tbldelta (id, first name, last name, certifications completed, certification date)
then these tbls have a many to one relationship to the certifications that the team has to do. I think this might be too far to break down but im not sure. EX:
tblalpha certs (Certification name, Cerification required(yes/no))
tblbravo certs (Certification name, Cerification required(yes/no))
tblcharlie certs (Certification name, Cerification required(yes/no))
tbl delta certs (Certification name, Cerification required(yes/no))

Hopefully this helps you see how I laid it out. What I want to do is have a search form (which I already made) that you can search by id, first name, last name, team name, or certification and it will return what you requested. I have been able to return everything but the certifications because under the form i just want one field for certifications not alpha certifcations bravo, etc.

[rpeare]

You don't need that many tables, you can do this on 3 tables
1. Rescuers, add an additional field to indicate 'team' (alpha, bravo, charlie, etc)
2. Certifications (primary key (autonumber) field, certification name, possibly renewal requirement (1 yr, 2yr, etc))
3. RescuerCertifications, (primary key (autonumber) field, rescuerID, CertificationID, CertificationStartDate, CertificationEndDate)

you're making things way harder on yourself than necessary with your setup.

[agh5147]

If done this way what would be your relationship links between tables? Also I wasn't going to use autonumber for the reason that the employee ids have both numbers and letters.

[orange]

Autonumbers are considered --necessary for the database management software, Not for human consumption.
You should read/watch Normalization articles/videos until "you get " the concepts. It's basic and critical.

If you had to tell an 8 yr old, or your 80 yr old granny, what you are trying to automate, what would you say - in simple clear English, no jargon?

I saw something like this after your earlier posts, but we still don't have enough context. rpeare's approach is similar to Bulzie's

[agh5147]

Still have not been able to address my database problem does anyone have solutions or potentially help with SQL statements to make it work?

[Bulzie]

We will continue to help Agh, sometimes it takes a few tries to get the info needed to help with solutions. For the EmployeeID, it does not have to be autonumber but should be unique if using that as key field. Most important part of a database is the data structure so if you can get that right, rest all falls into place. Do the data structures that orange or rpeare work for you or if not, what issues do you see(besides EmployeeID autonumber)?