Days since line item listed before...

[Ambyr via AccessMonster.com]

I have a report that is set up as:
CoName SiteNum CallDate DaysBetween
ABC Co 0001 1/15/09
ABC Co 0001 1/19/09
ABC Co 0001 1/26/09
Bob Mac 0020 1/4/09
Bob Mac 0020 1/24/09

What I need this report to do is first verify that the line item above this
line has the same CoName and SiteNum, and if so, put the difference in days
in the DaysBetween column. For example, since there is no entry before the
first line ABC Co, the DaysBetween would be 0, however, on the next line, the
company and site number is the same, so DaysBetween would be 1/19 - 1/15, to
fill in the answer of 4.
Line item 3's DaysBetween would be 7, and the first instance of Bob Mac would
be 0, as the co and site are different in the line above it.

I can not figure out a single way to get this information to appear. Any
ideas? Your assistance is GREATLY appreciated.

 

[Roger Carlson]

I can't either, not the way that your data stands now. However, I can think
of two ways if you added an autonumber field like this:
ID CoName SiteNum CallDate DaysBetween
1 ABC Co 0001 1/15/09
2 ABC Co 0001 1/19/09
3 ABC Co 0001 1/26/09
4 Bob Mac 0020 1/4/09
5 Bob Mac 0020 1/24/09

SELECT MT.OrderDetailID, MT.CoName, MT.SiteNum, MT.CallDate,
nz([CallDate] - (SELECT max(CallDate) FROM tblMyTable
WHERE CoName=MT.CoName and ID < MT.ID),0) AS DaysBetween
FROM tblMyTable AS MT;

or

SELECT tblMyTable.ID, tblMyTable.CoName, tblMyTable.SiteNum,
tblMyTable.CallDate, nz(CallDate - DMax("orderdate","tblMyTable","CoName= "
& [CoName] & " And ID< " & [ID]),0) AS DaysBetween
FROM tblMyTable
ORDER BY tblMyTable.ID;


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Roger Carlson]

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "DaysBetweenQuery.mdb" which illustrates how to do this. You
can find it here:
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=445

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

I can't either, not the way that your data stands now. However, I can
think of two ways if you added an autonumber field like this:
ID CoName SiteNum CallDate DaysBetween
1 ABC Co 0001 1/15/09
2 ABC Co 0001 1/19/09
3 ABC Co 0001 1/26/09
4 Bob Mac 0020 1/4/09
5 Bob Mac 0020 1/24/09

SELECT MT.OrderDetailID, MT.CoName, MT.SiteNum, MT.CallDate,
nz([CallDate] - (SELECT max(CallDate) FROM tblMyTable
WHERE CoName=MT.CoName and ID < MT.ID),0) AS DaysBetween
FROM tblMyTable AS MT;

or

SELECT tblMyTable.ID, tblMyTable.CoName, tblMyTable.SiteNum,
tblMyTable.CallDate, nz(CallDate - DMax("orderdate","tblMyTable","CoName=
" & [CoName] & " And ID< " & [ID]),0) AS DaysBetween
FROM tblMyTable
ORDER BY tblMyTable.ID;


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L