Need help turning a list into something form readable - training certifications

[SkeletorPlus]

I have a list called TrainingCerts with fields ID, CertName, PersonName, Date, and Attachment.FileURL. This is a linked list from sharepoint lists.

There are about 10 different certifications each due every year, lets call them cert1, cert2, etc.

So, my list is pretty big (about 800 people in my company), and they have to do all 10 certs a year.

I have been combining the date and URL into a hyperlink so its just one field called DateURL

I know that I have to:

1. Find the most recent certification by type, and person, by max date.
2. Display the non-aggragated table to make my DateURL field
2. Somehow convert the certification field from a single row into the table header? With CertPerson in the first column, and every other column being the name of a certification

I tried this with a crosstab query on a subform but access wouldn't even process it (I got an error).

I think my other option is to do it manually, but if certifications change, I would have to update it manually each time.

Does anyone have any advice on what to do here? And how to query efficiently for this. I am scared that I am taking longer than I need to to even query the crosstab.

I also would not mind any sample databases or articles on the subject if you know of any similar to this.

 

[MajP]

I will do it in pieces to make easier you could do this in two queries or even 1 if you are really good a sql.

1. query MaxCertDate

SELECT trainingcerts.certname,
       trainingcerts.personname,
       Max(trainingcerts.DATE) AS MaxOfDate
FROM   trainingcerts
GROUP  BY trainingcerts.certname,
          trainingcerts.personname;

2. qryCertDetails

SELECT trainingcerts.id,
       trainingcerts.certname,
       trainingcerts.personname,
       maxcertdate.maxofdate,
       trainingcerts.attachfileurl,
       [date] & "" & [attachfileurl] AS Date_URL
FROM   trainingcerts
       INNER JOIN maxcertdate
               ON ( trainingcerts.DATE = maxcertdate.maxofdate )
                  AND ( trainingcerts.personname = maxcertdate.personname )
                  AND ( trainingcerts.certname = maxcertdate.certname );

3. qryCertDetails_CrossTab

TRANSFORM First(qryCertDetails.Date_URL) AS FirstOfDate_URL
SELECT qryCertDetails.PersonName
FROM qryCertDetails
GROUP BY qryCertDetails.PersonName
PIVOT qryCertDetails.certname;

FYI "Date" is a reserved word and should not be used as a field name. I did not do it, but everywhere you use Date you should surrond by [Date] square brackets.

 

[theDBguy]

Hi. Welcome to AWF!

I also have a certificate list in SharePoint and all I did was create a New Form, which is I think a new feature in SharePoint Online. I just put the link to the form in the site navigation and everyone was able to submit their completion certificates. I can view the list, which is grouped by cert title to see who were done. I can then cross reference that list to the list of staff to find out who were behind on their training. Not sure if you're doing the same thing with yours.

 

[SkeletorPlus]

I will do it in pieces to make easier you could do this in two queries or even 1 if you are really good a sql.

1. query MaxCertDate

SELECT trainingcerts.certname,
       trainingcerts.personname,
       Max(trainingcerts.DATE) AS MaxOfDate
FROM   trainingcerts
GROUP  BY trainingcerts.certname,
          trainingcerts.personname;

2. qryCertDetails

SELECT trainingcerts.id,
       trainingcerts.certname,
       trainingcerts.personname,
       maxcertdate.maxofdate,
       trainingcerts.attachfileurl,
       [date] & "" & [attachfileurl] AS Date_URL
FROM   trainingcerts
       INNER JOIN maxcertdate
               ON ( trainingcerts.DATE = maxcertdate.maxofdate )
                  AND ( trainingcerts.personname = maxcertdate.personname )
                  AND ( trainingcerts.certname = maxcertdate.certname );

3. qryCertDetails_CrossTab

TRANSFORM First(qryCertDetails.Date_URL) AS FirstOfDate_URL
SELECT qryCertDetails.PersonName
FROM qryCertDetails
GROUP BY qryCertDetails.PersonName
PIVOT qryCertDetails.certname;

FYI "Date" is a reserved word and should not be used as a field name. I did not do it, but everywhere you use Date you should surrond by [Date] square brackets.

Thank you for your reply. I actually made a typo, and used CertDate for the field.

I did exactly what you have, this is how it was set up before I got the error - yet sadly the error persists. I should mention that the error is possibly from how I am linking this to my main form. My main form consists of my “accounts” table, which includes “accountID”, “PersonID”, etc. (we have separate systems people are on). Main form is accounts table, master/parentID is PersonID, childID is CertPerson (same thing but sharepoint list), and then the subform is build around this query.

is it because in my main form I have many account per person, and I am flipping the one-to-many relationship with main forms and sub forms? Do I need to have my main form be based around people and not accounts?

 

[MajP]

If I understand correctly, an account has many people and each person has many certs. If so you can design a form with subforms where you select an account and see the people in that account and see the current certs for the selected person. Depends on what you want to show. But you should be able to validate the query without the form.

 

[MajP]

You can nest subforms but I find them really hard to follow. If that was me I would have an unbound combo to select an account. This filters continous subform 1 with people in that account. I use conditional formatting to highlight the current row. Then I have contnous subform 2 which is the certs for the highlighted row.
The form wizard will complain when you try to link these forms but there are some tricks to make work.

 

[MajP]

See this example of synchronized subforms. This is intuitive where you more than 2 levels
Post in thread 'Sub Forms' https://www.access-programmers.co.uk/forums/threads/sub-forms.328859/post-1890497

 

[LarryE]

Thank you for your reply. I actually made a typo, and used CertDate for the field.

I did exactly what you have, this is how it was set up before I got the error - yet sadly the error persists. I should mention that the error is possibly from how I am linking this to my main form. My main form consists of my “accounts” table, which includes “accountID”, “PersonID”, etc. (we have separate systems people are on). Main form is accounts table, master/parentID is PersonID, childID is CertPerson (same thing but sharepoint list), and then the subform is build around this query.

is it because in my main form I have many account per person, and I am flipping the one-to-many relationship with main forms and sub forms? Do I need to have my main form be based around people and not accounts?

Could you post a screenshot of your table and relationship design? That will tell us if the design is the problem to begin with. It sounds like:

1. You have multiple employees
2. Each employee needs multiple certifications
3. Each certification is completed each year

So, you need:

1. An Employee table
2. A CertificationName table linked to the Employee table
3. A CertificationDate table linked to the CertificationName and the Employee table as a Junction table

So, each employee may complete each certification each year.

 

[SkeletorPlus]

Could you post a screenshot of your table and relationship design? That will tell us if the design is the problem to begin with. It sounds like:

1. You have multiple employees
2. Each employee needs multiple certifications
3. Each certification is completed each year

So, you need:

1. An Employee table
2. A CertificationName table linked to the Employee table
3. A CertificationDate table linked to the CertificationName and the Employee table as a Junction table

So, each employee may complete each certification each year.

I am not sure if I am understand the part about linking the certification name table to the employee table. Wouldn’t it more be an employee table, a certification name table, and an employee/certification date junction table? And if you are saying that for query purposes of who needs what, wouldn’t it make more sense to have the certification name table functioned to something like the department or role table?

 

[MajP]

I am not sure if I am understand the part about linking the certification name table to the employee table. Wouldn’t it more be an employee table, a certification name table, and an employee/certification date junction table?

I agree I do not understand that either.

EmployeeTable
- EmployeeID
- Employee Name
-Other employee fields

Certification Table
- Certification ID
- Certification Name
- other Certification Fields

Employee Cert Taken Table
- EmployeeID_FK ' foreign key to employee table
-CertificationID_FK ' foreign key to cert table
- DateTaken

Some kind of variation of that. What you described originally is a non normalized version of this junction table where you used the cert names and person names repeated instead of a foreign keys. However you could treat those names as key if they relate back to the other tables.

 

[SkeletorPlus]

I agree I do not understand that either.

EmployeeTable
- EmployeeID
- Employee Name
-Other employee fields

Certification Table
- Certification ID
- Certification Name
- other Certification Fields

Employee Cert Taken Table
- EmployeeID_FK ' foreign key to employee table
-CertificationID_FK ' foreign key to cert table
- DateTaken

Some kind of variation of that. What you described originally is a non normalized version of this junction table where you used the cert names and person names repeated instead of a foreign keys. However you could treat those names as key if they relate back to the other tables.

So in my case, the CertName and CertPerson are actually foreign keys for those fields.

Here is a question I have for you to go along with this:

Let’s say there are 20 types of training a person could have. And I was looking to build out a form to display them all. In my mind, there do great way to do this based on having them in a list in a subform - unless I am missing something.

I’m imagining creating a sort of massive cross tab query - where I have one main field (person) and each cert would have 3 fields associated with it: CertName, Date, and Hyperlink. 1 + (20x3) is 61 fields, and I’m not sure if that is something access can handle reasonably.

The reason I say I’m not sure if they would work in a list well is because I would want to be able to group and display the trainings on the form in a manner that makes sense - grouping similar trainings (general user vs privileged user, etc) and I think by displaying them as a subform list I would be limited in how I could order them. I could very well have 5 or 6 queries based on type of training, and then use “select distinct” to make every cert name show up regardless of if the person has it or not, but I’m not fully confident that would work - again it’s about the same amount of data being displayed and this time on 5 different subforms.

Just in your mind and your experience with access, what do you see as the best way to approach this? End goal being able to type in a user and have a standard form that displays who does or does not a training cert.

(For the subform, non-cross tab solution, I would:

1. query people who need x training

2. query the max date grouped by cert, person

3, self join back to the first query (this is my max date per person with ID and hyperlink)

4. query select distinct on cert names

5. left join query 4 on query 3 (and you some IFF() statements for “No Record”)

long winded… but thoughts is that efficient at all?)

 

[MajP]

Not sure I even get the crosstalk idea, unless you really want columns. Assume I have my qryCertDetails above. If I Cartesian join people to certs I get a query with all people and every cert. If I left join that query to qrycertdetails by person and cert. it will form a record for every person for every cert the most updated date or blank if no date. Then if main form is a person the sub form is all possible certs for that person with details or blank.
Why not just a subform. Each row is the required cert and date or empty. Do not know why colums would make sense. If there is cert groups, then you could sort by group or put separate groups on different subforms. The subforms could be on a tab control to save real estate.