Join table query

[chrizzis]

I have three tables and I want to formulate a query for a form.

tblMbr:
mbr_ID (key) 1-many with tblMbrCerts
first_name
etc...

tblMbrCerts: join table
mbr_ID (key)
cert_ID (key)
cert_date

tblCerts:
cert_ID (key) 1-many with tblMbrCerts
cert_name

I want to:
-Display all the skills for the given member in one form.
-Display all the members with a given skill (or set of skills) in another form.
I want to display the non ID values. I hope I created the join table correctly. I was just following the help directions provided by Access.

Thanks in advance,
-C

 

[RuralGuy]

You need to specify whether your keys are PrimaryKeys (PK) or ForeignKeys (FK).

 

[chrizzis]

I have done nothing more than link them and enforce referential integrity in the relationships window. Do I need to create a query or do anything more to explicitly declare the PKs/FKs?

tblMbr:
mbr_ID (PK) 1-many with tblMbrCerts
first_name
etc...

tblMbrCerts: join table
mbr_ID (FK)
cert_ID (FK)
cert_date

tblCerts:
cert_ID (PK) 1-many with tblMbrCerts
cert_name

 

[chrizzis]

To clear the air, this is not a homework assignment. I was "tasked" by the unit I serve with to create a database that stores members and their skills with the ability to query the database for members with desired skill sets for rapid deployment. I am not a database guy, but since I am a technical artist in the civilian world, my computer-challenged unit thinks that equates to "database programmer" - I was actually introduced to my OIC by my chief this way. I had to have a side chat with my OIC subtly indicating the extent of my chief's computer illiteracy by telling him my civilian job.

Thanks,
-C

 

[jdraw]

I have done nothing more than link them and enforce referential integrity in the relationships window. Do I need to create a query or do anything more to explicitly declare the PKs/FKs?

tblMbr:
mbr_ID (PK) 1-many with tblMbrCerts
first_name
etc...

tblMbrCerts: join table
mbr_ID (FK)
cert_ID (FK)
cert_date

tblCerts:
cert_ID (PK) 1-many with tblMbrCerts
cert_name

For clarity , tblCerts represents Skills/set of skills, right?
Any reason this wasn't called Skills?

This should give a list of all Certs for all Members
in Alpha ascending order

SELECT cert_name, cert_date, first_name
From
tblMbr, tblMbrCerts,tblCerts
WHERE
tblMbr.mBr_ID=tbMbrCerts.MBr_ID and
tblCerts.Cert_ID = tbMbrCerts.Cert_ID 
Order By First_name, cert_name

To get members (alphabetically with a certain cert ) prompts for cert_name

SELECT first_name,cert_name, cert_date
From
tblMbr, tblMbrCerts,tblCerts
WHERE
tblMbr.mBr_ID=tbMbrCerts.MBr_ID and
tblCerts.Cert_ID = tbMbrCerts.Cert_ID 
and cert_name = [Enter cert name]
Order By First_name

Hope this helps

 

[chrizzis]

Sorry about the confusion. The database is going to track skills, certs, and education. The certs tables were the shortest to type and I figured if I got the answer to this one, I can apply it to the rest.

Thanks for the answer jdraw. Tested and works beautifully.

I have another similar question. I am trying to create a member data entry form which has a subform for the skills of that member. How do I create a query displaying all certs for that particular member? Is there a way to reference the mbr_ID in the main form to populate the subform within the query statement?

Also, I want to create a form that allows the user to find all members with particular skills/certs. How do I create a query displaying members with a particular skill set (a list of skills, certs, and degrees)? Instead of:

cert_name = [Enter cert name]

can I link this to a form for collecting a skill set to plug into the query statement?

Thanks for the help,
-C

 

[chrizzis]

Okay, I wrote the statement for querying all skills for an individual member (I renamed the tables):

SELECT cert_name, cert_date
FROM
tbl_mbr, tblJoin_mbrCerts, tblRef_certs
WHERE
tbl_mbr.mbr_ID=tblJoin_mbrCerts.mbr_ID And
tblRef_certs.cert_ID=tblJoin_mbrCerts.cert_ID And
first_name=[Enter name]
ORDER BY cert_name;

...which works when I test it.

Now I would like to create a subform that uses that query to list the skills for the current member. What do I replace the "first_name=[Enter name]" part of the statement with?
"tbl_mbr.mbr_ID=MemberForm.mbr_ID" doesn't work...

Thanks,
-C

 

[chrizzis]

Update on what I have done so far:

I created my query:
SELECT tbl_mbr.mbr_ID, skill_name, skill_description, exp_lvl, years_exp
FROM
tbl_mbr, tblJoin_mbrSkills, tblRef_skills, tblRef_experience
WHERE
tbl_mbr.mbr_ID=tblJoin_mbrSkills.mbr_ID And
tblRef_skills.skill_ID=tblJoin_mbrSkills.skill_ID And
tblJoin_mbrSkills.exp_ID=tblRef_experience.exp_ID
ORDER BY skill_name;

Then I created a MemberForm form with a tab control. I stuck a subform in the "Skills" tab and selected all the fields in the query to include in the subform. I did NOT want to see the mbr_ID in the subform, but this was the only way I could find to link it to the main form.
I don't know if this is a permanent fix, but I viewed it in form view and selected the mbr_ID column and hid it. Is that the way it is normally done? It all seems to work with the data points I have in the database.

If this is correct, I now need to figure out how to add a skill. I'm guessing I should go over to the forms discussion board.

If I can get some positive feedback, I can move on to the next problem.

Big thanks to jdraw for pointing me in the right direction.

 

[chrizzis]

Crap, I totally missed that I went from certs to skills. I tried to solve the problem using the skills tables because I had more data there. I'll list the skills table structure tomorrow. I'm going to bed.