2

I have a challenge ahead of me. I have looked at this for a couple of days now in a trial and error sense and am getting tired of not getting it… My SQL knowledge is very very basic.

Each quarter I have to report on the questions below (of course the date period changes):

  • The number of doctors with whom the designated body has a prescribed connection at 31st December.
  • The number of doctors due to hold an appraisal meeting in the reporting period (from 1st October to 31st December 2017).
  • The number of those doctors above who held an appraisal meeting in the reporting period.
  • The number of those doctors above who did not hold an appraisal meeting in the reporting period.

I have three lists:

  • A list of staff responsible to the designated body. In a linked table- ‘GMC_Main’ Field- GMC Ref No
  • A list of all appraisals that have ever taken place (historical and ones performed by staff not responsible to us). In a query called- ‘Latest Appraisal’ Fields- ProfNum, MaxOfAppDate
  • List of emails in a linked table- ‘MARS_Core’ Fields- ProfNum, EmpSurname, EmpFirstName, EmpEmail

Things to consider

  • ProfNum and GMC Ref No are the Unique identifiers for each member of staff.
  • GMC_Main is the list of all staff that need to be considered in the report. So should have a row regardless of the results from the other tables.
  • All appraisals are valid for 365 days. So The date 1 year in the future from that in MaxofAppDate will be needed to calculate expiry in the period.
  • Due to software limitations I only have available Access 2016.
  • I need to count all that should have taken place, including ones that are still overdue from previous quarters.
  • Count all the ones that actually took place in the period.
  • Be able to contact all the ones that did not achieve and appraisal.
  • At year end (31 March) do this for the entire year and not just the quarter…

Fuff!

Each time I approach this problem I am missing a group of people or feel I am doing it in a very wrong-handed way.

If anyone could help, then that would be amazing. This is a little beyond me.

Erik A
  • 31,639
  • 12
  • 42
  • 67
Matt McKee
  • 21
  • 2
  • "Each time I approach this problem I am missing a group of people or feel I am doing it in a very cack-handed way." - can you give us some examples how you've approached this? It sounds like you just need suitable SQL queries that will work on Access? (and you probably want to tag this 'sql' and 'access') – Rup Mar 16 '18 at 13:18
  • That is pretty much what I am after but I'm struggling to comprehend the code with my tiny knowledge of SQL. – Matt McKee Mar 16 '18 at 13:59
  • I've been creating separate Queries and referencing them and then using totals. But I need to keep in mind the overdue from outside 1 year (overdue by a very long time) but also only refer to each ProfNumber once. – Matt McKee Mar 16 '18 at 15:16
  • 1
    Can you provide sample data and expected output based on that data? – Erik A Mar 16 '18 at 18:37
  • Just a simple table would be fine for the count part. Count of all at the Designated Body 438 -Easy one it's just a count of all on the sheet GMC_Main. Count of Due ?? Completed ?? Overdue ?? But I would also need to be able to identify and send emails to all the overdue ones so maybe a datasheet for all those overdue during that period that I can copy the email address out of. Here's a GDrive of the files... https://drive.google.com/drive/folders/1T1WkzO-NE93IK-YsZDDJE4-dGA1ngJDj?usp=sharing – Matt McKee Mar 19 '18 at 11:44

0 Answers0