Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    ArvinFx is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Feb 2017
    Posts
    15

    Special calculation query

    Hi

    I need to create query like this:

    employee_id Days_between_Visit
    1 11
    1 4
    1 4
    1 7
    2 3
    2 15
    2 10

    from this table:


    ID employee_ID Visit_date
    1 1 01-Dec-18
    2 1 12-Dec-18
    3 1 16-Dec-18
    4 1 20-Dec-18
    5 1 27-Dec-18
    6 2 02-Dec-18
    7 2 05-Dec-18
    8 2 20-Dec-18
    9 2 30-Dec-18


    But totally i am confused , i dont know how to read previous record and subtract from current record

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    One method is to use subqueries - see Get the value in another record section of http://allenbrowne.com/subquery-01.html

    Another method is to create a query and add your table to the query twice. The second copy will be shown as e.g. Table1_1
    Link the two by employeeID and add your employeeID field.
    Now add other fields & criteria as shown below

    Click image for larger version. 

Name:	Capture.PNG 
Views:	40 
Size:	14.0 KB 
ID:	36385

    The query SQL is
    Code:
    SELECT Table1.employee_ID, [Table1].[Visit_date]-[Table1_1].[Visit_Date] AS DaysBetweenVisitFROM Table1 INNER JOIN Table1 AS Table1_1 ON Table1.employee_ID = Table1_1.employee_ID
    WHERE (((Table1.ID)=[Table1_1].[ID]+1));
    The query results are:

    Click image for larger version. 

Name:	QueryResult.PNG 
Views:	40 
Size:	5.9 KB 
ID:	36387

    See attached for the example database using your data
    Attached Files Attached Files
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    ArvinFx is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Feb 2017
    Posts
    15
    Thanks, It works now. I get the way,

    I really amazed how MS software engineer could design suck a simple software with this much of ability, and also how a person can understand this script like you. It was quite brilliant way .

    Regards
    Arvin

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    You're welcome.
    Ironically I answered another query with almost exactly the same issue immediately after yours. Same solution.
    However in more complex cases, a subquery is likely to be needed
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  5. #5
    Join Date
    Apr 2017
    Posts
    1,673
    Quote Originally Posted by ridders52 View Post
    The query SQL is
    Code:
    SELECT Table1.employee_ID, [Table1].[Visit_date]-[Table1_1].[Visit_Date] AS DaysBetweenVisitFROM Table1 INNER JOIN Table1 AS Table1_1 ON Table1.employee_ID = Table1_1.employee_ID
    WHERE (((Table1.ID)=[Table1_1].[ID]+1));
    This solution assumes that:
    1. All visits of 1st employee are entered as single batch, after that all visits of 2nd employee are entered, etc.;
    2. All visits of same employee are entered in visit date order, i.e. no visits are registered afterwards.

    Having 1st condition filled makes using this table practically impossible. (The 2nd one is minor problem only.)

    My advice is to use UDF to calculate the time difference. E.g. the UDF can query the TOP 1 date from same table with same employee id and with date less than entry date, ordered by date descending (and ID ascending for case of several entries at same date).

  6. #6
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    I agree completely that the solution wouldn't work in many cases.
    Indeed I wrote:
    However in more complex cases, a subquery is likely to be needed
    Having said that, I've done other more generic solutions of this type by using a ranking query
    Sort the data by employeeID & visit date.
    Add a rank order field - I use a Serialize function for this - see http://www.mendipdatasystems.co.uk/r...ies/4594424063
    Then use that as the reference field in place of the ID field

    If that method is also not feasible for the particular example, I'd go for a subquery - see my link in post 2

    EDIT : Thought I'd explain the rank order approach by example (attached)
    I've reordered the records in Table1
    Query1 therefore no longer works as already explained by Arvil
    New query2 puts the records in rank order
    Query3 uses 2 copies of query2 to provide a solution as before

    That solution will work for many such examples - its faster than a subquery & more transferrable than a UDF
    Comments welcome
    Attached Files Attached Files
    Last edited by isladogs; 12-03-2018 at 12:50 PM. Reason: Added attachment & extra text
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  7. #7
    ArvinFx is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Feb 2017
    Posts
    15
    ArviLaanemets point to correct weakness, I was fighting with that, so i have to use some script .

    Dearridders52 ,
    I can not understand why you used [EmpVisitDate] ? or maybe i don't know how this script works.

  8. #8
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    The original solution was only ever intended to work with the data you supplied which was a very specific situation

    Version 2 will work no matter how the employee records are sorted in your table.
    It does so by using the serialize function to re-sort in the order required
    I added the EmpVisitDate field so I could have a numerical field for the serialize function to sort by both employeeID and visit date at once
    The first digit is the emplyeeid (1 or 2) followed by the date converted to a long integer.
    So all records for each employee are sorted together and in date order.

    To use in your own application, import module1 containing the serialize function together with both queries 2 & 3.
    You won't need any additional code (unless your real data is in a different format to that supplied)
    Hope that explains things for you

    It would also be worth looking at the link I gave to my website explaining how serialize is used in ranking queries
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  9. #9
    Join Date
    Apr 2017
    Posts
    1,673
    For case OP wants to try the solution based on UDF, here is one I use im most of my Access applications. The function returns the valid value from list of values in table. I think adding a parameter to choose between "<" and "<=" for DateField condition allows to use this function in OP's query.
    Code:
    Option Compare Database
    Option Explicit
    --------------------------------------------------------------
    Public Function ValidValue(parTable As String, IdField As String, IdCond As String, IdIsString As Boolean, RetField As String, DateField As String, parDate As Date)
        Dim dbs As Database
        Dim rs As Recordset
        Dim varQstr As String
        ' The function returns the value of RetField from table parTable
        ' where IdField equals IdCond and DateField is nearest past or equal to parDate.
        ' The parameter IdIsString must be True, when IdField has Text format
        
        On Error GoTo Err_ValidValue
        Set dbs = CurrentDb
        varQstr = "SELECT [" & RetField & "] FROM " & parTable & _
                " WHERE [" & DateField & "] <= " & Format(parDate, "\#mm\/dd\/yyyy\#") & _
                " And [" & IdField & "] = " & IIf(IdIsString, "'" & IdCond & "'", IdCond) & _
                " ORDER BY [" & DateField & "] DESC"
        Set rs = dbs.OpenRecordset(varQstr)
        rs.AbsolutePosition = 0
        ValidValue = rs.Fields(0).Value
        
    Err_ValidValue:
            rs.Close
            dbs.Close
            Set rs = Nothing
            Set dbs = Nothing
    End Function

  10. #10
    ArvinFx is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Feb 2017
    Posts
    15
    Guys I regraded to do my report by query maybe i have to write a long VB script,

    But I Curious to know whether access query can give me this report or no?! In excel it is like a piece of cake , Plz find the attachment.Sample.zip

  11. #11
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Sorry - no idea what you're asking or what the Excel file shows.

    Do you now understand how to use version 2 of my approach?
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  12. #12
    Join Date
    Apr 2017
    Posts
    1,673
    Here is an example of using an UDF in query
    Attached Files Attached Files

  13. #13
    ArvinFx is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Feb 2017
    Posts
    15
    Yes I understand all solutions , but exception happens for me to finalize my calculation,

    I have to process to times, first is subtract two dates that you solve it for me .
    second is do a math on days that we exacted from tables witch is (day1+2)-day2 this is simple and easy but i need to do this calculation on only day2 and day4 and day6 like this:

    day1
    day2 day1+2-day2
    day3
    day4 day3+2-day4
    day5
    day6 day5+2-day6

    sample:

    20
    9 20+2-9=13
    45
    11 45+2-11=36
    56

    Second query result has to give me 13 and 36 as you can see i need to step by 2 on records. you can see excel file for clearation.

  14. #14
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If the order of data is random as opposed to the example given, why not just try 2 ordered and/or grouped queries instead of 2 tables?
    The only time I had to do anything like this myself, it was handled by a subquery but they and I usually end up in a fight and sometimes I lose.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  15. #15
    Join Date
    Apr 2017
    Posts
    1,673
    Quote Originally Posted by ArvinFx View Post
    second is do a math on days that we exacted from tables witch is (day1+2)-day2 ...
    What is day here?

    When Day means 1st, 2nd, ... dates, then I modified my example adding additional column following your example exactly. But I found it weird why you want the time difference to be negative! And I see no way to get same result as in your example!
    Attached Files Attached Files

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Make Query Ignore Special Characters
    By NadiaVKhan in forum Queries
    Replies: 39
    Last Post: 04-04-2017, 09:43 AM
  2. Calculation Field in table vs Query calculation
    By Leonidsg in forum Database Design
    Replies: 18
    Last Post: 02-28-2013, 09:38 PM
  3. Special characters in Default Value and Query
    By blacksaibot in forum Queries
    Replies: 1
    Last Post: 03-07-2012, 10:36 AM
  4. Replies: 2
    Last Post: 08-17-2011, 03:02 AM
  5. Query to perform several special operations
    By Allison in forum Queries
    Replies: 4
    Last Post: 05-13-2011, 04:30 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