Adding (Sum) from different table

[jbtyler2]

Hi,

I Would like to have a field in my faculty table were the Total (sum) all of the CEU hours a faculty member has taken will be displayed. If possible, I would also like a count of the number of courses they have taken. I have attached a copy of the ERD the referenced fields are in the Faculty Table at the bottom, the CEU hours is called CEUHoursAwarded (or pdhours) in the PD History table. The query below gives me the output I need but I cannot figure out how to tie it to individual records. I'm sure I need to use some type of wildcard.

select jagNumber, sum(pdhours)
from pdhistory
Group by JagNumber

[pbottcher]

Hi,

sorry, I do not really get what you try to acheive. In the pdhistory I do not see the jagNumber field.

What identifies an individual records?

[jbtyler2]

Sorry, I will clarify.

Here is one of the problems I experienced with AppGini. The RecordID and CourseID fields are my primary keys that increment as an integer. A JagNumber is what the university uses as our employee/student ID number. It is a unique number and never repeats. The format for it is J123456789. Since the primary key is an integer and the JagNumber is a varchar (10) field AppGini would convert JagNumber to an integer and give me crazy numbers (it apparently cannot do a varchar to varchar if the primary key is an integer). The only workaround I could perform was to remove the "J" from the number sequence. I called this field "JagNumberInt". The ERD does not reflect this on the PDHistory table but the JagNumber referenced in the ERD is linked from the Faculty JagNumberInt field to a PDHistory JagNumberInt field. This appears to work and the math is accurate.

I know realistically it should be RecordID to CourseID in a one to many relationship but we would need to know the RecordID for each faculty member which would be database specific and we import Professional Development (PD) in batches via CSV. Faculty are use to supplying the university staff with their JagNumber when prompted so it makes since to use this value versus a RecordID which is specific only to us.

The original database I inherited did not use a primary key and only the JagNumber to identify records. If this could be fixed fairly easily I am open to correcting this shortcoming.

[pbottcher]

ok, so maybe you can try to use

Code: Select all

select sum(pdhours)
from pdhistory left join Faculty on Faculty.JagNumberInt=pdhistory.JagNumberInt 
WHERE faculty.FacultyId=%ID%
GROUP BY pdhistory.JagNumberInt

as calculated field for Total_CEU_hours

[jbtyler2]

Awesome! That worked. I knew their would be some type of wildcard (%ID%) to pull the current record. Thanks again for your help.

I am ready to start building the reports and purchased the Customizing AppGini web applications course on Udemy to help customize the app. Do you know if there are any report specific resources for APPGini either by the company itself or third party? I also purchased the calendar and Summary Reports plugin.

[pbottcher]

Hi,
glad it works. Can you explain more detailed what you are looking for.

[jbtyler2]

Sure, one of the uses for this data is faculty tenure. Faculty members from time to time will ask for a list of all of the courses they have attended. We called this our Transcript report. Also, some of the college deans like to recognize faculty members based on the number of Professional Development hours (pdhours in the pd history table) they earned in a given semester sorted in a descending order which we call our All Star Report. For this report I have always filtered by college name since all of the data is standarized by the collegeID and college table. I feel fairly confident on building the SQL query for this but the Summary report plugin is not very flexible in terms of adding multiple fields (i.e. firstname, last name, department, pdhours, etc).

[jbtyler2]

I need to add these reports are in PDF format and we are using Microsoft Access 2019 currently. I’m trying to convert this Access DB into a web based system.

[pbottcher]

Hi,

are you looking for a tabular view, or a graph? For a tablular view there are different options you can use to achieve the results. One would be to apply filters according your needs. An other would be to generate a view-table according to your needs.

Also for creating PDF's, there is no build in function. You need to add an additional library and create the PDF on your own, or you stick to the browser option and export the page as PDF.