Results 1 to 5 of 5
  1. #1
    Access_Novice is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Sep 2013
    Posts
    265

    Adding a value depending on date

    In the attached file I am working with a query called 'qryBillingRate' which is based on the table AddBillRate. In this query, I want to add a 5th column which will drop in the billing rate for each person depending on the Project/Task and also depending on the effective date.



    The first 3 rows in the 5th column for Barry Johnson should have a billing rate of $95.87 since his timesheet entry date is after 01.01.2015 and before 03.01.2015.

    The next 3 rows in the 5th column for Barry Johnson should have $102.85, since his timesheet entry date is on or after 03.01.2015.

    In short, the query should drop in the correct billing rate, for each person, while also considering the timesheet entry date in relation to the Effective Date of the billing rate.

    Can anybody point me in the right direction? What is the best way to pursue this?
    Attached Files Attached Files

  2. #2
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Not trying to burn you, but I looked at your dB and I don't see how this will work.

    Specific to your question, I suppose you could write a function (UDF) to get the pay rate. Or you could try using DLOOKUP() function. But both methods would be slow.

    The some of the problems I saw were:
    No PK field in any table, therefore no FK fields
    No Employee (or worker) table,
    No Contracts table,
    Used reserved words as field names ("Name", "Type", "Start" "End)...... plus these names are not very descriptive. (Start of what?)

    Work through these tutorials, actually create the dBs:
    http://www.rogersaccesslibrary.com/forum/forum46.html

    Then start creating your table structure using pencil and paper/ whiteboard/ sticky notes, before starting to create them in Access.

    I don't have a whiteboard, so I use my window and a dry marker to design my table structure.

    Only use letters, numbers and the underscore for object names; No spaces, punctuation or special characters in object names.

    Good luck with your project.

  3. #3
    Access_Novice is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Sep 2013
    Posts
    265
    Quote Originally Posted by ssanfu View Post
    Not trying to burn you, but I looked at your dB and I don't see how this will work.

    Specific to your question, I suppose you could write a function (UDF) to get the pay rate. Or you could try using DLOOKUP() function. But both methods would be slow.

    The some of the problems I saw were:
    No PK field in any table, therefore no FK fields
    No Employee (or worker) table,
    No Contracts table,
    Used reserved words as field names ("Name", "Type", "Start" "End)...... plus these names are not very descriptive. (Start of what?)

    Work through these tutorials, actually create the dBs:
    http://www.rogersaccesslibrary.com/forum/forum46.html

    Then start creating your table structure using pencil and paper/ whiteboard/ sticky notes, before starting to create them in Access.

    I don't have a whiteboard, so I use my window and a dry marker to design my table structure.

    Only use letters, numbers and the underscore for object names; No spaces, punctuation or special characters in object names.

    Good luck with your project.
    Thank you for your reply. This isn't my "real" DB. This is a problem I foresee when I go to create the DB I have in mind, so I figured I should try to figure out now how to solve the problem. So that is why everything is very simple and vague (in terms of the column names).

    In Excel, a MATCH function would do the trick I believe. I was hoping that Access had something similar, something that could search for an approximate match instead of an exact match.

  4. #4
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    In Excel, a MATCH function would do the trick I believe. I was hoping that Access had something similar, something that could search for an approximate match instead of an exact match.
    Nope - Access doesn't have a Match function. Have to write your own..... depending on how your dB is designed.


    Your best bet is to forget pretty much everything you know about Excel. I had to - I struggled for a while; after going through several (many) tutorials on Access and help from a friend (mentor?), I started to pierce the darkness. (finally!!)

    THEN I had to relearn how to function (program) in Excel. Excel and Access are TOTALLY different animals.

    You sound like you are just starting out in Access (relational databases). Best thing you can do is to do the tutorials, design on paper (tables, then forms), then enter into Access.
    I thought I knew a lot since I had spent a couple of years working with dBIII, as well as Excel.
    Boy, was I wrong. dBIII is procedure driven and Access is event driven. Knowing dBIII didn't really help other than I knew a little dB theory.

    Don't want to write a book...
    Good luck... keep plugging away.


    Here is another site I use:
    http://www.accessmvp.com/strive4peace/Index.htm

  5. #5
    Access_Novice is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Sep 2013
    Posts
    265
    Quote Originally Posted by ssanfu View Post
    Nope - Access doesn't have a Match function. Have to write your own..... depending on how your dB is designed.


    Your best bet is to forget pretty much everything you know about Excel. I had to - I struggled for a while; after going through several (many) tutorials on Access and help from a friend (mentor?), I started to pierce the darkness. (finally!!)

    THEN I had to relearn how to function (program) in Excel. Excel and Access are TOTALLY different animals.

    You sound like you are just starting out in Access (relational databases). Best thing you can do is to do the tutorials, design on paper (tables, then forms), then enter into Access.
    I thought I knew a lot since I had spent a couple of years working with dBIII, as well as Excel.
    Boy, was I wrong. dBIII is procedure driven and Access is event driven. Knowing dBIII didn't really help other than I knew a little dB theory.

    Don't want to write a book...
    Good luck... keep plugging away.


    Here is another site I use:
    http://www.accessmvp.com/strive4peace/Index.htm
    Thank you for your suggestions. I am pretty good with Excel and maybe I'm trying to make Access work like Excel.

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

Similar Threads

  1. Replies: 7
    Last Post: 12-30-2013, 09:53 AM
  2. Replies: 0
    Last Post: 02-22-2013, 02:13 AM
  3. Replies: 25
    Last Post: 11-16-2012, 12:47 PM
  4. Replies: 5
    Last Post: 09-20-2012, 11:34 AM
  5. Replies: 2
    Last Post: 09-18-2011, 03:45 AM

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