Datediff problem (1 Viewer)

Hi I hope somebody out there is able to help....


Im farly new to access and have been asked to complete a small project which has me pull my hair out.
Heres the problem :I have a query runs fine, supplies all my data I need , but I need to be able to date diff between to fields in the same column and deliver the date difference to a new field (yet to be created) in the same query , I have tried all i know and nothing works.
My data looks as follows


dt | agent_id | agent_name | Date diff
| | |
30/11/2018 | 1186 | me | 1
| | |
31/11/2018 | 1186 | me | 1
| | |
04/12/2018 | 1186 | me | 4



Any options would help Ive been at this for 2 days ,and am ready to jump out the window, anything please..............

Firstly I'm not sure I understand what dates you are trying to compare as there only appears to be one date in your data?

Secondly, you shouldn't actually store that type of data if it can be calculated.

In your query the format for the calculation would be something like


This would give you the number of days between the two fields.

you need to use a subquery to get the the previous record. Your full query will be something like


note the aliasing of myTable within the subquery

First, arrowmakers, welcome to the forum.

Second, for future reference, we have many headers within the forum. This post should have gone under "Queries." But don't take this as a knock. We see it all the time from new members. Probably at some point soon, one of the moderators will move the post to the right place.

The_Doc_Man said:

Probably at some point soon, one of the moderators will move the post to the right place.

Click to expand...

Done, and welcome to AWF!

cross posted here https://www.accessforums.net/showthread.php?t=77357

@arrowmakers - there is nothing wrong with cross posting but it is polite to advise that you have done so because responders may spend sometimes a considerable time composing a response and are not too pleased to find someone on another forum has provided much the same answer. Consequently posters who get a reputation for not advising the cross post tend to be ignored.

Example:

dt | agent_id | agent_name |DateDiff
30/11/2018 | 1186 | me | |
31/11/2018 | 1186 | me | |
04/12/2018 | 1186 | me | |


But for me, it has something like two same IDs, with two different dates, but it does not show all the data in his example. In fact , the received report can use a column for the dates, by their own configuration, but you can be sure that there are two rows with the same ID. You only need to find the minor date between the two rows and then apply the function. As an basic idea, you can create two tables: one with the smaller date and another with maximum date.

And you want it:
id| dt | agent_id | agent_name |DateDiff
A25|30/11/2018 | 1186 | me | 1|
A48|31/11/2018 | 1186 | me | 1 |
A25|04/12/2018 | 1186 | me | 4|