Results 1 to 5 of 5
  1. #1
    quidlat
    Guest

    Looking for Missing Months Between 2 Tables

    Hi guys,
    Can anyone help me with this? I am stumped.

    Currently I can detect staff members who have skipped a month of reporting. A query that I have created shows the number of months that they skipped. However my boss wants me to create a query that actually points out which month/s they skipped (makes perfect sense, right?).

    But for the life of me I cannot find a way to do this. I have a table containing monthly course reporting that staff members simply dont have the ability to skip. Therefore this is the reference table. There is another table called leave/absence reporting that staff members can accidentally skip month/s of reporting.

    Now, I need a query that can somehow see if there are missing months in the leave reporting table based on the course reporting table (which contains the complete months for which the staff member has reported).

    The problem is, I cannot use a simple unmatched query because each staff member contains a subset of months. The unmatched query will select any values that do not exist in a table that exist in another table. So it may not select a person's missing month since another person may have that month. See what I mean?

    Is there a way that I can make a query that can actually group the table by SSN and based on that, query the leave table for missing months based on the subset of months belonging to one person in the other table?

    wow, i hope i have not confused you guys!! Please help!

    Thanks

    chris

  2. #2
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    120
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Looking for Missing Months Between 2 Tables

    You should be able to double link the monthly course table and the leave/absence table linking on SSN and month. Change both links to show everything in the monthly course table and null as a criteria in ssn and month in the leave/absence table.

  3. #3
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Looking for Missing Months Between 2 Tables

    I recently created a test database that tried to solve a similar problem. I created an outer join between my data values and a table of reference values. In this case, you could first summarize your data down to the month and then join it with a table or query containing a list of employees and months. The trick is to ensure that you list all records from the list of eployees and months, even if the particular employee has no data for that month. You then can query that subquery for nulls. So, perhaps this is a more concrete expression:

    q1test: Create employee/month reference table
    SELECT Employee.EmpSysNbr AS EmpCode, ActngPeriod.PrdNbr AS MonthNo
    FROM Employee, ActngPeriod
    WHERE (((ActngPeriod.PrdYear)=2001));

    q2test: Summarize actual time entered by month (you might not need this step)
    SELECT BilledTime.BTTkpr, Month([BTDate]) AS BTMonth, Sum(BilledTime.BTActualHrsWrk) AS SumOfBTActualHrsWrk
    FROM BilledTime
    WHERE (((Year([BTDate]))=2001))
    GROUP BY BilledTime.BTTkpr, Month([BTDate]);

    q3test: Combine billings into reference table without losing rows (repeat for data from second table, but combine into a different column)
    SELECT q1test.EmpCode, q1test.MonthNo, q2test.SumOfBTActualHrsWrk
    FROM q1test LEFT JOIN q2test ON (q1test.MonthNo = q2test.BTMonth) AND (q1test.EmpCode = q2test.BTTkpr);

    q4test: Identify the periods with no billings (you might need to sum the values in the two columns into a third column and test that to confirm no value in either table)
    SELECT Employee.EmpName, q3test.MonthNo, q3test.SumOfBTActualHrsWrk
    FROM q3test INNER JOIN Employee ON q3test.EmpCode = Employee.EmpSysNbr
    WHERE (((q3test.SumOfBTActualHrsWrk) Is Null));

    I'm sure there's a more elegant way to do this...but it worked for me!

  4. #4
    quidlat
    Guest

    Re: Looking for Missing Months Between 2 Tables

    Oh My God!!! You totally solved it!! Thanks so much, DUDE!!!!

    I had queries like your test queries 1 and 2 already. So I just had to write the third query, replacing the table and field names. I didnt think it would work, but, lo and behold! Voila!! It worked and worked elegantly, contrary to your suspicions.

    Thanks dude!

    Chris

  5. #5
    quidlat
    Guest

    Re: Looking for Missing Months Between 2 Tables

    Hey dude, thanks for the quick response. I tried doing your suggested solution and it worked. It turned out that you and jfsher's solutions were the same, essentially. So Thanks man!!!

    Chris

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •