Results 1 to 4 of 4
  1. #1
    Analyst003 is offline Novice
    Windows Vista Access 2010 64bit
    Join Date
    Mar 2017
    Posts
    3

    Lightbulb Employee having 2 different rates for a specific date range; How to calculate and design the query?

    Hi Everyone/Access Senai!



    I hope I posted this thread in the right section. If not, I'm sorry! I thought this would be the right place for my question.

    I taught myself how to design my first database at a law firm when I was a college intern. I've been assigned to create the Company's Cost Department Database. I'm excited and nervous at the same time because it's been a while. It's helping to learn more about database since I plan to switch fields from Accounting to IT.

    My team has requested what they want the result to be: Choosing specific date range to show the grand total for each staff agency. The breakdown from each agency will contain each Employee ID. Each Employee ID will have the total cost for the specific week range when selected. <-- I hope this makes sense! If not, I'll be happy to explain further.

    I was able to design a report that shows the name of the agency, and the grand total based on the week range selected for each employee ID.

    The only trouble part I'm having is what if one employee has two different rates due to a raise within the week ranges. I researched through countless of the websites and youtube. I can't seem to find the answer to my question. I don't know if I need to create a table, or a query, and redesign the report.

    Please let me know if you need anymore information to help me solve this problem.

    Thank you all!

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Then you need a child table tEmpRates.
    EmpID. (Long)
    Rate
    StartDate
    EndDate

    Then queries on the tWork table would also grab the rate where the work time falls inside the tEmpRate.StartDte and EndDate

  3. #3
    Analyst003 is offline Novice
    Windows Vista Access 2010 64bit
    Join Date
    Mar 2017
    Posts
    3
    Click image for larger version. 

Name:	Database Design 1.JPG 
Views:	10 
Size:	52.2 KB 
ID:	27978

    Hm, I think there might be an issue.

    Let's say the first data record that I entered (SRO ID = 1) and the rate is 10. The company assumes that is the rate the person is going to be for the whole year. So we have the start date being 1/1/17 and end date being 12/31/17.

    However, let's say today he got a raise. Now his rate is 15. If I enter a new entry for that tWork, SRO ID, Rate being 15, Start Date = 3/24/2017 and End Date = 12/31/17.

    Would that cause a conflict when I run a query for the specific dates? Let's say the Company wants to see the amount for each SRO from 01/01/17 - 04/01/17.

    I attached what I have for the relationships. I know I created the database wrong since I'm still learning.

    Thank you, Ranman256-sensai!

  4. #4
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    You will have to have another table to contain the rates, that is called database normalization, as Ransman said. Adding multiple records to this one table is a very bad idea. Then all your queries will read this new table and take the rate based on the dates.

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

Similar Threads

  1. Replies: 7
    Last Post: 01-05-2016, 11:23 AM
  2. Replies: 7
    Last Post: 12-30-2013, 09:53 AM
  3. Replies: 6
    Last Post: 10-21-2013, 04:21 PM
  4. SELECT a specific DATE RANGE in Query
    By taimysho0 in forum Queries
    Replies: 28
    Last Post: 06-04-2012, 04:58 PM
  5. SQL Query Design - Date Range Help Needed
    By StevenCV in forum Queries
    Replies: 1
    Last Post: 02-28-2012, 08:24 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
  •  
Other Forums: Microsoft Office Forums