Results 1 to 9 of 9
  1. #1
    Lahohaemluwo is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2020
    Posts
    5

    Can Access input data into a field if there are duplicate dates.

    I am trying to create a database for our productivity. I want to know if Access can enter data based on the number of duplicate dates entered. At most, only 2 of us are working at a time(for now). So, when there are 2 duplicate dates I want the query to label each person as a count of 0.5, otherwise when there is 1 person working, I want it to enter 1 into the field for the count. Is that possible? Just as an FYI, I am self taught when using Access, I do not have any formal training. But I have also created a database that keeps up with the procedures we do on a daily basis, so I do have some knowledge. Thank you in advance for any assistance you are able to provide!

  2. #2
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    2,438
    So, when there are 2 duplicate dates I want the query to label each person as a count of 0.5, otherwise when there is 1 person working, I want it to enter 1 into the field for the count.
    I don't understand this. Why don't you just use some validation code in the forms BeforeUpdate event to prevent more that two entries with the same date.
    If this helped, please click the star at the bottom left of this posting and add to my reputation. Many thanks.
    Not everyone can access an accdb, you may reach a broader audience by attaching in mdb format.

    Bob Fitzpatrick

    I have never failed. I have found a thousand ways that do not work!

    Microsoft Community Contributor Award (2011)



  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    7,124
    I have to wonder why you'd need to see .5+.5 rather than 1+1. Surely there is not something about the process that limits you to 1?
    You said "for now". When it's not 'now' then what - 0.3 + 0.3 + 0.4?
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    "Everyone has a photographic memory; some just don't have film." Steven Wright

  4. #4
    Lahohaemluwo is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2020
    Posts
    5
    Thank you for your response. Because I am ok with duplicate dates. When there are duplicates I want a return number for [TCD] to be 0.5, when there is not a duplicate date I want [TCD] to be 1. It's not my rule, I don't like productivity tracking because those who put it in place can't explain how it show productivity. I am just trying to make their "productivity numbers" work. What my boss does right now is: When one person works the [TCD](Total Covered Day) = 1, When 2 people work the [TCD] = 1 for one person and 0 for the other. When calculating out individual productivity based on this method, someones counts will be off.

  5. #5
    Lahohaemluwo is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2020
    Posts
    5
    Thank you so much for your response. Yes, it is not my system for counting, just trying to make it more accurate. They are keeping a "productivity log". Each day that someone works the [TCD](Total Covered Days) is equal to one. 1 is the only number they can have for that entry(total). So rather than the way I am seeing it done, I am trying to get Access to give me a return value of 0.5 for each person if there are 2 people working on a day or 1 for the person working. As is, when 1 person is working it is 1, when 2 people are working it is 1 for the 1st person and 0 for the second person. Which to me skews the productivity numbers for the individual but remains accurate for the group as a whole. And yes, there may come a day when the work load is split with more people. Not sure how it would work with 3, but for 4 it 0.25 + 0.25 + 0.25 + 0.25 for total of 1. (Again not my system for productivity) Just trying to make a program that will work with what I have.

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    7,124
    Normally it is not wise to store a calculation but with this one, I'm undecided, especially without knowing more about the process. You'd need a table that contains records, one record for each person working on a particular time frame. A query that counts the records (Totals query) based on your criteria such as date, shift, etc. would return a value, e.g. 1, 2, 3 or perhaps 4 for that time frame. You'd divide 1 by that count and use the result. Whether or not you simply display this calculation in forms or reports in the normal fashion might depend on what you need to do with it. Numbers like .333 might have to be rounded up at the other end, otherwise you might have .999 and not 1. If another system needs the calculation and you can calculate and export as needed I think that would be better than storing it.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    "Everyone has a photographic memory; some just don't have film." Steven Wright

  7. #7
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,905
    If the intent is to Count who did what and when (productivity), then why not record the Date and EmpName with the record being processed? Sum the records by Date and EmpName??

  8. #8
    Lahohaemluwo is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2020
    Posts
    5
    Quote Originally Posted by orange View Post
    If the intent is to Count who did what and when (productivity), then why not record the Date and EmpName with the record being processed? Sum the records by Date and EmpName??
    Thank you for your response. Yes that is essentially the intent, but what I want is to try and automate the procedure where when there is a duplicate date, a value for the EmpName becomes 0.5 if there is a duplicate date and 1 if there is not a duplicate date. If I am reading this right it will give me a sum of 2 when there is a duplicate date with EmpName, right?

  9. #9
    Lahohaemluwo is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2020
    Posts
    5
    Quote Originally Posted by Micron View Post
    Normally it is not wise to store a calculation but with this one, I'm undecided, especially without knowing more about the process. You'd need a table that contains records, one record for each person working on a particular time frame. A query that counts the records (Totals query) based on your criteria such as date, shift, etc. would return a value, e.g. 1, 2, 3 or perhaps 4 for that time frame. You'd divide 1 by that count and use the result. Whether or not you simply display this calculation in forms or reports in the normal fashion might depend on what you need to do with it. Numbers like .333 might have to be rounded up at the other end, otherwise you might have .999 and not 1. If another system needs the calculation and you can calculate and export as needed I think that would be better than storing it.
    Thank you again for your response. I was thinking I may have to carry this out on a form or report, but I need a value for the TCD, or at least I think I do. It is only a small part of the equation. So there will be the following fields: [EnteredDate], [EmpName], [StartingCensus], [Admissions], [Floor], and [TotalCensus]. One calculation will be to add [StartingCensus]+ [Admissions]+ [Floor]= [TotalCensus]. Then, later either quarterly or yearly for the average when the [TotalCensus] is divided by [TCD] for the average per [EmpName] and also for overall for every [EmpName]. There are other fields, but I only need simple sums for these at present until the method for keeping up with productivity changes.

    I have tried some of the following in the validation rules for [TCD]: IIf([EnteredDate]=[EnteredDate],0.5,1) and IIf(sum[EnteredDate]=2,05.,1) also IIf(count[EnteredDate]=2,05.,1), or "greater>1" was placed in the spot for "=2", I have also tried statement/equations like that in Criteria on the Query as well under the [TCD]. (Not at the same time, in a trial and error fashion)

    I may be looking at this all wrong. Thank again for any assistance you are able to provide.

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

Similar Threads

  1. Replies: 15
    Last Post: 04-02-2019, 02:41 PM
  2. Replies: 6
    Last Post: 08-09-2018, 11:41 PM
  3. Replies: 2
    Last Post: 10-27-2015, 09:12 AM
  4. Replies: 5
    Last Post: 01-29-2014, 02:42 PM
  5. Query input dates for range of dates in two fields
    By kagoodwin13 in forum Queries
    Replies: 3
    Last Post: 02-13-2012, 04:25 PM

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 - Senior Forums