Convert Listed Dates to Duration to Calculate Equipment Rental Fees

[Alkady81]

Hi all

I'm new in access
I working on DB for my company equipment

I used tables and queries and reached to the following query

Query name (02-By Project)


KSC_NO From_Project To_project Date_of_Trans
K-01-037 52500 12013 02/14/2014
K-01-037 12013 52500 03/31/2015
K-01-037 52500 12013 04/07/2015


K-01-129 52500 12013 03/03/2015



I want to make query to give me the following

KSC_NO IN_Date Out_date Duration (months)
K-01-037 02/14/2014 03/31/2015 13.6667
K-01-037 04/07/2015 Today 0.0333
K-01-037 03/03/2015 Today 1.2


Is that possible?


Thanks and Regards

[ranman256]

There is no query that can do that.
it would require programming to run the data sequentially to determine each record's time span.

[Alkady81]

There is no query that can do that.
it would require programming to run the data sequentially to determine each record's time span.

can you tell me which programming?

[Rawb]

Theoretically it can be done, but we would need to know the exact design of your Table. And, depending on the design, there might need to be pretty strict restrictions on how and when users can enter data.

For example, assuming you have the following design:
Projects Table

• ID - Autonumber, Long Integer, Primary Key.
• KSC_No - Text, 8 characters.
• From_Project - Number, Integer.
• To_Project - Number, Integer.
• Date_Of_Trans - Date/Time. Short Date.
• Is_Start_Date - Yes/No.

As long as all matching date pairs follow the rules below, you can do it:

1. Both Records must have the same KSC_No.
2. The From_Project of the first Record must match the To_Project of the other Record
3. The To_Project of the first Record must match the From_Project of the other Record
4. The second Record (the end date) must be entered BEFORE a new start date is entered unless the new start date has a different KSC_No, From_Project, or To_Project.
5. The first record (the start date) must have Is_Start_Date checked.
6. The second record (the end date) must NOT have Is_Start_Date checked.

As long as all of those rules are obeyed, the following Query can be used:

SELECT 
    A.ID, 
    A.KSC_No, 
    A.From_Project, 
    A.To_Project, 
    A.Date_Of_Trans AS Date_Start, 
    Nz((
        SELECT
            Min(C.Date_Of_Trans)
        FROM
            Projects AS C
        WHERE
            C.ID > A.ID AND
            C.KSC_No = A.KSC_No AND
            C.From_Project = A.To_Project AND
            C.Is_Start_Date=False
        GROUP BY
            C.Date_Of_Trans
        ORDER BY
            C.Date_Of_Trans
    ),Date()) AS Date_End
FROM 
    Projects AS A
WHERE 
    A.Is_Start_Date=True

I've included a DB with an example.

[Rawb]

I just realized that, with the Query I provided earlier, it's theoretically possible (although unlikely) to link a start date to the wrong end date.

To correct the issue, I've changed the main Query a bit. I also created a second Query that references the first and includes all the data.

Attached is an updated database. This one also includes a simple Report that shows how you can use the Query results to determine duration and whether or not there's a hard end date.

[Alkady81]

I just realized that, with the Query I provided earlier, it's theoretically possible (although unlikely) to link a start date to the wrong end date.

To correct the issue, I've changed the main Query a bit. I also created a second Query that references the first and includes all the data.

Attached is an updated database. This one also includes a simple Report that shows how you can use the Query results to determine duration and whether or not there's a hard end date.

thanks Rawb, that's worked for me