Results 1 to 4 of 4
  1. #1
    mslieder is offline Novice
    Windows XP Access 2003
    Join Date
    Dec 2005
    Posts
    10

    Date Calculation within same fields

    I have a table where I want to calculate the difference in dates from one record to the next. To give you a little history, it's a database where we want to track project element time. In other words, a project could be "In Queue" starting at date 1/1/06. In the next record, the same project could be "Active" starting at date 1/3/06. This means that it was in the queue for 2 days.

    I could put Start Date and End Date fields, but the user would have to enter the same date twice (once for End Date of the previous record, and again for the the Start Date of the next record). I'm trying to avoid this.

    Is it possible?

    Here's what the table looks like:

    Status Start Date
    In Queue 1/1/06
    Active 1/3/06
    Review 1/5/06
    Active 1/6/06


    Review 1/8/06
    Complete 1/9/06

    Any help will be much appreciated.
    Anne
    P.S. I know Access fairly well, but am not proficient at code. Be gentle!

  2. #2
    Join Date
    Dec 2005
    Location
    Wilmington, DE - USA
    Posts
    275
    Anne,

    If these are all fields, you can write a query that calculates the values:

    SELECT DateDiff("d", [StartDate], [EndDate]) AS NumOfDays
    FROM MyTable

  3. #3
    mslieder is offline Novice
    Windows XP Access 2003
    Join Date
    Dec 2005
    Posts
    10
    Thanks Patrick! That will work IF I had a Start Date field and an End Date field. But I don't. I merely have a Start Date field. What I need/want to do is calculate the difference from one record to the next.

    So if record 1 has a start date of 1/1/06, and record 2 has a start date of 1/3/06, I need a new field to display the difference of 2 days. In the example I gave earlier, I'd need to know that the In Queue status was 2 days.

    Me thinks this is impossible!

    Thanks,
    Anne

  4. #4
    Join Date
    Dec 2005
    Location
    Wilmington, DE - USA
    Posts
    275
    Anne,

    If you had a table that looked like this:

    ProjectID
    StatusID
    StatusDate

    and if the StatusIDs are sequential (e.g., 1 = started, 2 = phase 1 complete, 2 = phase 2 complete, ..., N = all done) then you could do something like...

    SELECT MyTable.ProjectID,
    (SELECT M.StatusDate
    FROM MyTable AS M
    WHERE M.ProjectID = MyTable.ProjectID AND M.StatusID = 2) -
    (SELECT M.StatusDate
    FROM MyTable AS M
    WHERE M.ProjectID = MyTable.ProjectID AND M.StatusID = 1) AS Days
    FROM MyTable
    ORDER BY MyTable.ProjectID

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

Similar Threads

  1. Calculation Help
    By edge5511 in forum Programming
    Replies: 4
    Last Post: 12-18-2015, 11:11 AM
  2. problem in splitting Date and time fields
    By swaroop1012 in forum Queries
    Replies: 1
    Last Post: 11-22-2008, 11:29 AM
  3. Question about creating a date calculation tool
    By bazillion in forum Programming
    Replies: 0
    Last Post: 01-25-2008, 12:08 PM
  4. Calculation
    By thestclair in forum Reports
    Replies: 1
    Last Post: 04-29-2006, 11:03 AM
  5. Report Calculation
    By thestclair in forum Reports
    Replies: 2
    Last Post: 03-28-2006, 12:23 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