How to get a qualification set from a junction table

[Oblio]

Hi,



I have an Access 2010 DB (32 Bit) running on Windows 7.

I need to get a qualification set for each person based on the values in the junction table...

t_person
PersonID Autonumber, long Integer

t_Qualifications
QualificationID Autonumber, long integer
QualificationDescription

jt_Person_Qualifications
PersonID, Foreign Key, Number
QualificationID, Foreign key, Number
Start_Date, Date/Time
End_Date, Date/Time

There are only 4 qualifications: A (ALL Persons are this qualification)
B (You must have A as well)
C (You must have A as well)
D (You MUST have qualification B in addition to D)

Short version:

Therefore, there are 6 allowable Qualification Sets:
1. A
2. A AND B
3. A AND C
4. A AND B AND C
5. A AND B AND D
6. A AND B AND C AND D

I would like to query the junction table to produce a current qualification set that I can assign a mapping icon to as well...

So, sample jt_Person_Qualifications records would be, for example:

PersonID QualificationID Qualification Set for Person=1 would be Qualification Set 5 (from above)
1 A
1 B
1 D
2 A Qualification Set for Person=2 would be Qualification Set 1
3 A Qualification Set for Person=3 would be Qualification Set 3
3 C
4 A Qualification Set for Person=4 would be Qualification Set 6
4 B
4 C
4 D


What I need is a query or expression to provide me with the person's ID and their CURRENT Qualification Set as well as a Mapping Pin Type icon.
I built a lt_Qualification Set, where I can add the mapping icon for that set, and then figured I would compare it to the jt_Person_Qualifications records somehow using an if then type of query????

The reason I am not just using the t_Qualification_Set is I need to show when there are any changes and what date they occurred on....so I would need a query to be able to calculate show which of the qualifications were added or dropped which I suspect may be more difficult to report?

Any advice would be greatly appreciated, and I apologize for the length of this ...

Oblio

[ssanfu]

In table "jt_Person_Qualifications" you have fields "Start_Date" and "End_Date".
Can a Qualification expire? When calculating qualification sets, do you have to take the dates into consideration?


--------------------------------------------------------
Forgetting the date issue for now, I would write a UDF to calculate the Qualification Set.

In table "t_Qualifications", add another field "QualificationValue".

QualificationID	QualificationDescription	QualificationValue (Integer)
101	Qualification A	1
102	Qualification B	2
103	Qualification C	4
104	Qualification D	8

The table "lt_Qualification" would look like:

Set_ID (Long)	QualificationSet	SetValue (Integer)
1	A	1
2	A and B	3
3	A and C	5
4	A and B and C	7
5	A and B and D	11
6	A and B and C and D	15

The function would open a totals query to get the sum of the current qualifications. Then open another record set on "lt_Qualification" to get the set ID.

Use the function in a query to get which Qualification Set each person has/was in.



OK, I was bored... added an mdb example file...

(still didn't account for dates. )

[Oblio]

You are correct...I do need to consider the dates when determining the current Qualification Set as each qualification above A can be temporary, removed by disciplinary action, earned, relinquished voluntarily etc. So I was just going to have the qualification set as that part of jt_Person_Qualifications, which would work I suppose... I believe I need a way to show what was gained and what was lost on that date, but not sure how to do that in a query???

BTW...did I saw WOW!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! !!!!!!! What an AWESOME ANSWER and the amount of detail and effort blows me away!!!
Not sure how I can thank you enough, but WOW...that blew me away!!!

Hi,

I have an Access 2010 DB (32 Bit) running on Windows 7.

I need to get a qualification set for each person based on the values in the junction table...

t_person
PersonID Autonumber, long Integer

t_Qualifications
QualificationID Autonumber, long integer
QualificationDescription

jt_Person_Qualifications
PersonID, Foreign Key, Number
QualificationID, Foreign key, Number
Start_Date, Date/Time
End_Date, Date/Time

There are only 4 qualifications: A (ALL Persons are this qualification)
B (You must have A as well)
C (You must have A as well)
D (You MUST have qualification B in addition to D)

Short version:

Therefore, there are 6 allowable Qualification Sets:
1. A
2. A AND B
3. A AND C
4. A AND B AND C
5. A AND B AND D
6. A AND B AND C AND D

I would like to query the junction table to produce a current qualification set that I can assign a mapping icon to as well...

So, sample jt_Person_Qualifications records would be, for example:

PersonID QualificationID Qualification Set for Person=1 would be Qualification Set 5 (from above)
1 A
1 B
1 D
2 A Qualification Set for Person=2 would be Qualification Set 1
3 A Qualification Set for Person=3 would be Qualification Set 3
3 C
4 A Qualification Set for Person=4 would be Qualification Set 6
4 B
4 C
4 D


What I need is a query or expression to provide me with the person's ID and their CURRENT Qualification Set as well as a Mapping Pin Type icon.
I built a lt_Qualification Set, where I can add the mapping icon for that set, and then figured I would compare it to the jt_Person_Qualifications records somehow using an if then type of query????

The reason I am not just using the t_Qualification_Set is I need to show when there are any changes and what date they occurred on....so I would need a query to be able to calculate show which of the qualifications were added or dropped which I suspect may be more difficult to report?

Any advice would be greatly appreciated, and I apologize for the length of this ...

Oblio

[ssanfu]

BTW...did I saw WOW!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! !!!!!!!

Awww, Geeee.... Now you're making me blush...

Here is another example mdb with dates... just for ideas.....

[Oblio]

I would need the current Qualification Set, in other words, those that have not ended, and so are current, but not sure if that complicates things with them being a null end date? Tried just future dating the end date which works as long as one of the qualifications is not in the past. With Bob, I changed one of his qualification end dates to a date 2 days ago, which caused the form table to return the number 0 and no qualification set for him...

I really wish I was better at this....it is so tantalizing to have a sense of what you can do, and so frustrating to know you maybe don't have the smarts to get there, lol!

Well, I am a glutton for punishment I guess 'cause I can't give it up!!! You have been so helpful, Steve! BTW, nice form look! Might have to borrow that ;-P

Best Regards,

William


BTW...did I saw WOW!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! !!!!!!!

What an AWESOME ANSWER and the amount of detail and effort blows me away!!!

Not sure how I can thank you enough, but WOW...that blew me away!!![/QUOTE]

[ssanfu]

but not sure if that complicates things with them being a null end date?

Yes it does. But I set all of my end dates to be 50 years in the future. (I didn't do that in this example, but it is easy enough to do.)

Lets say I have a qualification start date for Qualification C that is 7/1/2013.
On a new entry, the end date is automatically set to 50 years + start date - 1 day. So the end date is 6/30/2063.
So there should never be NULL dates. (I don't allow NULL dates in my dBs.)

Or if the qualifications expire after 1 year/18 months/2 years/... , set the end date to that date.

Does this answer your question?? (I think that is what you are asking)



Also, another thought.... adding a date control on the form would allow you to look back in time to see a historical view of someone's qualification sets.
On form open, the date control would default to today's date. Changing the date would show the qualifications/Qualification Set on that date.

There are only 4 qualifications: A (ALL Persons are this qualification)

I took this to mean A never expired, so at minimum there would be a Qualification Set of A.
If someone can have no qualifications, need to add a Qualification Set set of None.


I won't be able to get to a A2010 version until Fri/Sat. If you have more questions, hopfully I can push... errr... nudge you in the right direction. Post your dB (with sensitive data munged) or PM me and we can trade email addresses.

BTW, nice form look! Might have to borrow that ;-P

Sure, borrow away. I should mention I get royalties in the amount of ....... drat!!! I should have told you before I sent it to you.