Help Creating Tracking Database

[2uniq]

From my project Im working with Microsoft Access 2013. I would like to create a database that is able to track nurse certification and ensure they are up to date. There are 7 certifications to track and each expire at different times. Furthermore, the nurses can obtain these at different times. For example Nurse A can have Certificate 1,2,3. She obtained Cert 1 on 12-12-12 but it expires in 2 yrs. While the other 2 certificates she obtained on 12-11-13 and they expire in 1 yr. How could a create a report to show this highlighted. I know I have to use conditional formatting.. just not sure how..

Any help would be appreciated.

NurseID CertCode CertIssue CertExp


1 ...........100 ......12-12-12...2 year or 12-12-14
1 ...........102 ......12-11-13...1 year or 12-11-13

[June7]

What rule do you want to use for 'highlighting'. Maybe if the expiration date is 30 days from now?

[2uniq]

yes, that sounds appropriate.

[June7]

Rule for the CertExp textbox:

BETWEEN Date() AND Date() + 30

Or if you want to capture past due certs as well:

<=Date() + 30

[2uniq]

I created three table certifiction, nurse, and tracking
please note i am a beginner in using access

Nurse tbl has name, address etc..
Certification has CERT_CODE and CERT_NAME
Tracking has NursID, CERT_CODE, CERT_ISSUE and CERT_EXP

However.. should I move CERT_EXP to Certification and set the data type as short text to state 1yr, 2yr, 3yr etc related to Cert_code...

Hope that makes sense.. I am pretty lost...

[June7]

Could have a NUMBER field in Certification that has the renewal interval in years (1, 2, 3, etc). Then the individual expiration dates would be calculated in a query that joins the tables.

The Conditional Formatting for a textbox that displays the expiration date would be the same as suggested.

[2uniq]

I am confused how do I connect the Certification renewal to the individual expiration dates? in two separate tables.. I have 7 certificates I am tracking for each nurse. (For example RN license expires in one year and BLS expires in 2 yrs). Each of them are issued at different dates. So Nurse 1 could have cert 1,2,3,4 but they all expire on different dates...
I am taking an online course and I have to self teach myself.
I appreciate all your help!
Thank you!!

[June7]

Consider:

tblStaff
StaffID (primary key)
LastName
FirstName

tblCerts
CertID (primary key)
CertName (CPR, First Aid, RN, LPN, etc)
IntervalYrs (1, 2, 3, etc)

tblStaffCerts
StaffID (foreign key)
CertID (foreign key)
DateCert

Now build a query that joins all three tables on the related PK/FK fields. Create a field in this query with an expression to calculate the expiration date:

DateAdd("yyyy", [IntervalYrs], [DateCert])

Apply suggested filter criteria to this constructed field.

Basic Access functionality. Access Help has guidelines on using the query builder and creating fields with expressions.