Query Expression Help: Calculating Date Differences For One Field

[drhopkins]

So I have a table that is pretty simple. It has [ID], [Shot] and I need to calculate the difference in days between each shot for each grouped ID.

I was trying to use DLookUp in SQL(among a few other ways) but it wasn't working for me. I wouldn't say I'm even close to an experienced user, but I do understand a lot of basics and have created a database for my research(albeit using wizards, details form, and the expression builder). That being said, some of the links that I found on previous articles just wasn't working for me. After a few days of tinkering with it on and off, I felt like it was time to post this.

Here's an example where the first two fields are what I want to use in the query, and my desired output for each record. The ID's are all structure like shown. In design view I have it grouped by ID using Totals.

[TABLE="width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Shot[/TD]
[TD]Value Desired[/TD]
[/TR]
[TR]
[TD]JB0123[/TD]
[TD]9/18/13[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]JB0123[/TD]
[TD]9/20/13[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]JB0123[/TD]
[TD]9/29/13[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]AB0101[/TD]
[TD]7/1/13[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]AB0101[/TD]
[TD]7/8/13[/TD]
[TD]7[/TD]
[/TR]
</tbody>[/TABLE]


I hope that is enough clarification, but I would be willing to provide a lot more. Thanks in advance

 

[SydneyGeek]

You need a subquery to reference a previous row in Access.
I took your data and assumed that the table was called Photos. Here is the output of the query: (Dumped into Excel, then pasted here)
Excel 2010

	E	F	G	H
AB0101
AB0101
JB0123
JB0123
JB0123

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #C0C0C0, align: center"]ID[/TD]
[TD="bgcolor: #C0C0C0, align: center"]Shot[/TD]
[TD="bgcolor: #C0C0C0, align: center"]PrevShot[/TD]
[TD="bgcolor: #C0C0C0, align: center"]Interval[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]01-Jul-13[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]08-Jul-13[/TD]
[TD="align: right"]01-Jul-13[/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]18-Sep-13[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]20-Sep-13[/TD]
[TD="align: right"]18-Sep-13[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]29-Sep-13[/TD]
[TD="align: right"]20-Sep-13[/TD]
[TD="align: right"]9[/TD]

</tbody>

Sheet1​

Here is the SQL for that query:

SELECT Photos.ID, Photos.Shot, (SELECT TOP 1 Shot FROM Photos AS P WHERE P.ID = Photos.ID AND P.Shot < Photos.Shot ORDER BY ID, Shot DESC) AS PrevShot, IIf(IsNull([PrevShot]),0,[Shot]-[PrevShot]) AS [Interval]
FROM Photos
ORDER BY Photos.ID, Photos.Shot;

Some explanation:
To find the previous record in a table, you need to build a query where the table references itself. The second copy is given an alias (in this case P, the first letter of the original table) and you need to find the first record where Shot is earlier than the current date, and the IDs match. That's what the TOP 1 statement does; limit the result to just one record, from which you pull the Shot value.
Once you have the data lined up, you need an expression to return 0 if PreviousShot is null, otherwise subtract the dates.

Denis

 

[drhopkins]

Great explanation Denis, thanks very much!

I inputted the data into the tables and changed to table references around, but I got this code [h=2]"At most, one record can be returned from this subquery"[/h]

SELECT tblVivi.ID, tblVivi.Shot, (SELECT TOP 1 Shot FROM tblVivi AS V WHERE V.ID = tblVivi.ID AND V.Shot < tblVivi.Shot ORDER BY ID, Shot DESC) AS PrevShot, IIf(IsNull([PrevShot]),0,[Shot]-[PrevShot]) AS [Interval]
FROM tblVivi
ORDER BY tblVivi.ID, tblVivi.Shot;

I know it has to do with the subquery finding multiple matches. At least Microsoft Access tips: Surviving subqueries says that is the most likely cause. I tried switching to the SELECT max option listed.

I'm wondering if there is anything inherently wrong with the code for Microsoft Access 2010 or if the problem is being generated from poor design

 

[SydneyGeek]

I'd guess a design or syntax issue because I can't imagine that Access 2010 would have a special problem with the code.
I can't see an obvious issue with your SQL -- looks like a straight replacement of table names to me.

How many records are there in the table? I'll see if I can create a subquery to find where the multiples are occurring.

Denis

 

[drhopkins]

Yea I don't think it looks wrong either. There is 396 total records. The table has two fields, ID and Shot. The table also has a 1-Many relationship with a table where information based on each ID is stored. Shot is set as a date/time field. In the design view of the query both ID and Shot are set to ascending sort. I tried fiddling with ascending, descending, and no shot to no avail.