Calc # of days between date records in 1 table 1

[KH2009]

Hi, novice Access 2007-er, green SQL-er here …
I’ve tried very hard to figure this out by working with advice from similar posts but I am stuck, yet I think I’m almost there…
Table: Proposal Stage_tbl
Fields: ProjectID, PropStageID, ComDate(as well as Topic and a couple other flds not needed for query)

I need to keep track of the number of days that accumulate between records via the ComDate date field.
When I run this qry with the following SLQ behind it, the PrevDate field is empty in every record. I can’t figure out where I’m going wrong. If anyone can point out the problem(s) I would be very grateful.

SELECT [Proposal Stage_tbl].ProjectID,[Proposal Stage_tbl].PropStageID,[Proposal Stage_tbl].ComDate,
SELECT Max(ComDate) as PrevDate
FROM [Proposal Stage_tbl]
WHERE ProjectID = [Proposal Stage_tbl].ProjectID
AND ComDate < [Proposal Stage_tbl].ComDate)
AS PrevDateFROM [Proposal Stage_tbl];

Once I get this qry working then I think I can use it in a new query with a calculation field, with something like [ComDate] – [PrevDate] in it.
Thanks much in advance for your help.

Kathy

 

[HarleyQuinn]

Where do the values in [red]red[/red] come from?

SELECT [Proposal Stage_tbl].ProjectID,[Proposal Stage_tbl].PropStageID,[Proposal Stage_tbl].ComDate,
SELECT Max(ComDate) as PrevDate
FROM [Proposal Stage_tbl]
WHERE [red]ProjectID[/red] = [Proposal Stage_tbl].ProjectID
AND [red]ComDate[/red] < [Proposal Stage_tbl].ComDate)
AS PrevDateFROM [Proposal Stage_tbl];

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[KH2009]

Harley,
ProjectID is primary key in the Project table and foreign key in Proposal Stage table. Is this perhaps creating an issue or conflict? ComDate is a date picker field in the Proposal Stage table only.
Thx.
Kathy

 

[HarleyQuinn]

Thinking about this, it might be easier if you were to show us some sample data and the result you're after displaying.

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[KH2009]

Does this help at all? Thanks.
Date Diff 1_qry
ProjectID PropStageID ComDate PrevDate
1 12 1/29/2009
1 11 1/27/2009
1 2 1/20/2009
1 1 1/15/2009
2 14 1/20/2009
2 13 1/14/2009
2 3 1/7/2009
3 17 1/26/2009
3 16 1/19/2009
3 15 1/8/2009
3 4 1/5/2009
4 20 1/21/2009
4 19 1/19/2009
4 18 12/17/2008
4 5 12/2/2008
5 21 1/28/2009
5 8 12/17/2008
5 7 12/17/2008

 

[HarleyQuinn]

Do you want the number of days between the max and min dates in the ComDate field for each projectID?

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[KH2009]

Harley,
I think so, if I understand correctly...
Say for the first three records:
Project ID PropStageID ComDate
1 12 1/29/2009

1 11 1/27/2009

1 2 1/20/2009

I'm trying to get at how many days transpire between each occurrence of a PropStage record for yes, Project ID.

Sorry for not being clear enough. Part of problem is I'm hiking up this steeplearning curve. Thanks. Kathy

 

[PHV]

A starting point:

SELECT A.ProjectID, A.PropStageID, A.ComDate, Max(B.ComDate) AS PrevDate
, A.ComDate - Nz(Max(B.ComDate),A.ComDate) AS NumberOfDays
FROM [Proposal Stage_tbl] AS A LEFT JOIN [Proposal Stage_tbl] AS B
ON A.ProjectID = B.ProjectID AND A.PropStageID > B.PropStageID AND A.ComDate >= B.ComDate
GROUP BY A.ProjectID, A.PropStageID, A.ComDate

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[HarleyQuinn]

Yep, nip out of the office for five minutes and PHV beats me to the punch

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[KH2009]

PHV,

Your code worked so good as a starting point it may turn into my happy ending point. It works great! Thanks so much!

Harley, shame on you for taking a 5-minute lunch break. Thanks to you also for helping me think through it a little better.

Have a good weekend, all.
Kathy

 

[KH2009]

Hi, I discovered a little snag after some data entry and so am continuing with last Friday's thread, hopefully that is the best approach. Problem in a nutshell is. . . I need to calculate the number of days that transpire between occurrences of communication (email, phone calls, etc.) between and among inhouse staff and outside subcontractors. (Yes I wonder too if this is more trouble than it’s worth but it was a directive.)

PHV's code below works perfectly UNTIL somebody comes along and enters a record with an out of sequence date.
Code:
SELECT A.ProjectID, A.PropStageID, A.ComDate, Max(B.ComDate) AS PrevDate
, A.ComDate - Nz(Max(B.ComDate),A.ComDate) AS NumberOfDays
FROM [Proposal Stage_tbl] AS A LEFT JOIN [Proposal Stage_tbl] AS B
ON A.ProjectID = B.ProjectID AND A.PropStageID > B.PropStageID AND A.ComDate >= B.ComDate
GROUP BY A.ProjectID, A.PropStageID, A.ComDate;

Result:
Date Diff 1_qry
ProjID PropStageIDPrevDate ComDate Days Between
1 1 1/15/2009 0
1 2 1/15/2009 1/30/2009 15
1 11 1/15/2009 1/29/2009 14
1 12 1/30/2009 1/30/2009 0
1 13 1/30/2009 2/5/2009 6
1 14 2/5/2009 2/6/2009 1
1 15 2/6/2009 2/20/2009 14

It should work like this:
Date Diff 1_qry
ProjID PropStageID PrevDate ComDate NumberOfDays
1 1 1/15/2009 0
1 11 1/15/2009 1/29/2009 14
1 2 1/29/2009 1/30/2009 1
1 12 1/30/2009 1/30/2009 0
1 13 1/30/2009 2/5/2009 6
1 14 2/5/2009 2/6/2009 1
1 15 2/6/2009 2/20/2009 14


Record #3 in the result is example of the issue that crops up when Joe realizes he forgot to make a record that he emailed the subcontractor on 1/29/09 requesting edits to a project doc. The code is referencing the Record #2’s Project ID #1’s PropStageID value and PrevDate value. But it should be referencing Record #2’s PropStageID and ComDate value – I think – in order for it to work. Hopefully I’m making sense. I’ve tried all I know (which isn’t that much when it comes to SQL to be honest) without success. Can anybody help? Thanks. K