Results 1 to 8 of 8

Using a form to count and calculate between multiple columns

  1. #1
    Coryjacques is online now Novice
    Windows 10 Access 2016
    Join Date
    Nov 2018
    Posts
    7

    Using a form to count and calculate between multiple columns

    Hello,
    I'm an excel user fairly new to access and just want to know if the below is possible.

    Say I have 3 columns in table Demographics:

    Employee_Name, Hire_Date, Term_Date.

    Employee Name is short text, the other two are m/d/yyyy.

    I'm looking to build a form with three fields that will ultimately count and calculate values to show tenure:



    First I need to count the number of records hired before a specific hire date, removing any records that have been terminated before a specific term date. EX: I want every employee who was hired before and terminated after 1/1/2018.

    Then I need to find all employees hired between 1/1/18 and 4/1/2018 (first quarter of the year). I need to do the same with terminations.

    Additionally, if it's possible to input other criteria such as the field "Department" and search specifically for all of the above records that only fit in with, say, "human resources," that would be even better.

    I can work out the rest of the equation, but I was wondering if and how a query could be built to find my starting headcount, hires, and terminations quarterly per the above.

    Is this possible? Sql or query.
    Last edited by Coryjacques; 11-01-2018 at 01:55 PM. Reason: Addition I forgot

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    45,289
    SQL is a query. Perhaps you meant "VBA or query".

    Query approach would likely require several queries to get the final output.

    A VBA custom function might be better.

    Choice can depend on experience and comfort with each.
    To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.

  3. #3
    Coryjacques is online now Novice
    Windows 10 Access 2016
    Join Date
    Nov 2018
    Posts
    7
    Thanks June, I meant SQL, but I only know the basics at this point - I don't know how complicated it would be to query a query in a query, you know?

    Really any way I can get the job done and deconstruct for learning would help

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    45,289
    Likely you will need to do aggregate query, maybe more than one. Then join them to each other or to master data.

    Perhaps an alternative is to build report, maybe with subreports, and use report Sorting & Grouping with aggregate calcs.

    Maybe use VBA.

    Maybe a combination of these techniques.

    Don't know your database well enough to provide specific guidance.

    If you are new to Access, have you completed any introductory tutorial book?
    To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.

  5. #5
    Coryjacques is online now Novice
    Windows 10 Access 2016
    Join Date
    Nov 2018
    Posts
    7
    I've completed a course on intro SQL but it definitely only covered the basics and access itself is very new. I can automate this in excel, i was just wondering if there was a way that wasn't incredibly complicated to do it in access that didn't involve as much setup.

    So far I've been able to deconstruct anything written out with an explanation and replicate well enough - this I guess is just an attempt to break into something more advanced

  6. #6
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    6,313
    In relation to your from/To, there are 6 possible employee states - hope this layout makes sense to you


    ……………..……………….|From...………………|To
    Emp1...Hire....Term
    Emp2.....Hire...…………...Term
    Emp3...………Hire.....…………………………….…Term
    Emp4...…………………….Hire......Term
    Emp5...…………………… Hire...……………………..Term
    Emp6...………………………………………………………..Hire......Term

    there are 3 others, where the contract has not got a termination date

    Emp7...………Hire.....…………………………….…………………..
    Emp8...…………………….Hire...………………………………………...
    Emp9...………………………………………………………..Hire...……………….
    The basic principle to determine whether to include an employee is hiredate is before/equal To and termdate is after/equal From. In the case of no term date and assuming you want to include them, substitute with the from date using the nz function


    First I need to count the number of records hired before a specific hire date, removing any records that have been terminated before a specific term date. EX: I want every employee who was hired before and terminated after 1/1/2018.
    So relating to the above, From and To are the same date, so you would only want Emp3 - and perhaps Emp7 if you want unterminated employees

    If you wanted to know how many employees were employed in Jan 18, the formula would return Emps 2,3,4,5,7 and 9

    So your basic query would be something like

    Code:
    SELECT *
    FROM tblEmps
    WHERE nz(Term,[Enter From Date])>=[Enter FROM Date] AND Hire<=[Enter End Date]
    you can take it from here to refine for your actual requirements

  7. #7
    Coryjacques is online now Novice
    Windows 10 Access 2016
    Join Date
    Nov 2018
    Posts
    7
    That's an awesome start! I'll give it a try I'm the morning. Thank you!

  8. #8
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    6,313
    I presume you are aware that dates are stored as numbers, so if your stored dates contain a time < or <= can produce incorrect results in certain situations

    now for example is 43406.3734837963

    so if you wanted everything up to today, todays records will not be returned with <= or < today (because today is 43406). However users will not find it natural to enter tomorrow to get the required result. So modifying the criteria slightly

    WHERE nz(Term,[Enter From Date])>=[Enter FROM Date] AND Hire<[Enter End Date]+1

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 05-22-2018, 02:17 AM
  2. Replies: 3
    Last Post: 08-09-2016, 12:52 PM
  3. Replies: 7
    Last Post: 06-02-2016, 07:23 AM
  4. VB to calculate count of distinct values
    By gokul1242 in forum Programming
    Replies: 1
    Last Post: 10-03-2012, 12:38 PM
  5. Count values across multiple columns
    By mkallover in forum Queries
    Replies: 3
    Last Post: 02-06-2012, 11:08 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums