Good Morning!

Access 2010

I am trying to create a query whereby I display a numeric value which represents the number of days between 2 dates of consecutive records where the record ID is the same (That makes sense in my head!!)

I have a table called tblJobs where the key field is JobNo and the date field to compare is Job_Start.

I have also added the table again to the query so have an identical data source called tblJobs_1 as I believe this is the way to compare records.

Assuming the following dummy data:

JobNo, Job_Start
10000, 31/12/2010
10001, 31/12/2010
10001, 10/12/2010
10002, 01/11/2010
10003, 30/11/2010
10003, 20/11/2010

I want to acheve the following result by creating a 'field' criteria where the field will be name Visit_Gap:

JobNo, Job_Start, Visit_Gap
10000, 31/12/2010, 99
10001, 31/12/2010, 99
10001, 10/12/2010, 21
10002, 01/11/2010, 99
10003, 30/11/2010, 99
10003, 20/11/2010, 10

If the previous record isn't the same JobNo or its the first record in the table, default to 99

I'm having a mare with this...can anyone offer any assistance....please?

Many thanks in advance

Steve

Something like this ?

Hope This Helps, PH.
FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

Hi PHV

I want to be able to add this to the query when it's in design mode...can this be done and if so, how?

Apologies if I'm not making sense!

Steve

Use the SQL VIEW pane.

Hope This Helps, PH.
FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

Good Morning PHV

I am still having real issues with this!!

Below is my original SQL....can you advise how / where to insert your suggested SQL?
Apologies if I'm coming across as an idiot!!

Many thanks

Steve

SELECT tblJobs.JobNo, tblEquipment.EQ_Desc, tblJobs.Job_Start, IIf([tblJobs]![Eng_Service],"Service",IIf([tblJobs]![Eng_Breakdown],"Breakdown",IIf([tblJobs]![Eng_Commission],"Commission","S/Works"))) AS JobTransactionType, tblCustomers.SN_NAME, tblCustomers.SN_PSTCODE, tblCustomers.SN_ACCOUNT, tblJobs.JB_InvoiceNo, tblEngineers.Eng_Name
FROM tblEquipment INNER JOIN (tblEngineers INNER JOIN ((tblSite_Details INNER JOIN tblCustomers ON tblSite_Details.SN_ACCOUNT = tblCustomers.SN_ACCOUNT) INNER JOIN (tblSiteContract INNER JOIN (tblJobs INNER JOIN tblJobSubType ON tblJobs.JB_SubType = tblJobSubType.js_id) ON tblSiteContract.SC_SiteID = tblJobs.SiteID) ON (tblSite_Details.SiteID = tblJobs.SiteID) AND (tblSite_Details.SiteID = tblJobs.SiteID)) ON tblEngineers.EngineerID = tblJobs.EngineerID) ON (tblEquipment.EQ_EquipmentID = tblSiteContract.SC_EquipmentID) AND (tblEquipment.EQ_EquipmentID = tblJobs.JB_EquipmentID)
ORDER BY tblCustomers.SN_ACCOUNT, tblJobs.Job_Start DESC;

What about this (typed, untested) ?
SELECT CJ.JobNo, E.EQ_Desc, CJ.Job_Start
, IIf(CJ.Eng_Service,'Service',IIf(CJ.Eng_Breakdown,'Breakdown',IIf(CJ.Eng_Commission,'Commission','S/Works'))) AS JobTransactionType
, C.SN_NAME, C.SN_PSTCODE, C.SN_ACCOUNT, CJ.JB_InvoiceNo, E.Eng_Name
, Nz(PJ.Job_Start-CJ.Job_Start,99) AS Visit_Gap
FROM ((((((tblEquipment E
INNER JOIN tblJobs CJ on E.EQ_EquipmentID = CJ.JB_EquipmentID)
INNER JOIN tblJobSubType JST ON CJ.JB_SubType = JST.js_id)
INNER JOIN tblSiteContract SC ON CJ.SiteID = SC.SC_SiteID)
INNER JOIN tblSite_Details SD ON CJ.SiteID = SD.SiteID)
INNER JOIN tblCustomers C ON SD.SN_ACCOUNT = C.SN_ACCOUNT)
INNER JOIN tblEngineers E ON CJ.EngineerID = E.EngineerID)
LEFT JOIN tblJobs PJ ON CJ.JobNo=PJ.JobNo AND CJ.Job_Start<PJ.Job_Start
WHERE E.EQ_EquipmentID = SC.SC_EquipmentID
ORDER BY C.SN_ACCOUNT, CJ.Job_Start DESC

Hope This Helps, PH.
FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

Oops, sorry for the typos:
SELECT CJ.JobNo, EQ.EQ_Desc, CJ.Job_Start
, IIf(CJ.Eng_Service,'Service',IIf(CJ.Eng_Breakdown,'Breakdown',IIf(CJ.Eng_Commission,'Commission','S/Works'))) AS JobTransactionType
, C.SN_NAME, C.SN_PSTCODE, C.SN_ACCOUNT, CJ.JB_InvoiceNo, E.Eng_Name
, Nz(PJ.Job_Start-CJ.Job_Start,99) AS Visit_Gap
FROM ((((((tblEquipment EQ
INNER JOIN tblJobs CJ on EQ.EQ_EquipmentID = CJ.JB_EquipmentID)
INNER JOIN tblJobSubType JST ON CJ.JB_SubType = JST.js_id)
INNER JOIN tblSiteContract SC ON CJ.SiteID = SC.SC_SiteID)
INNER JOIN tblSite_Details SD ON CJ.SiteID = SD.SiteID)
INNER JOIN tblCustomers C ON SD.SN_ACCOUNT = C.SN_ACCOUNT)
INNER JOIN tblEngineers E ON CJ.EngineerID = E.EngineerID)
LEFT JOIN tblJobs PJ ON CJ.JobNo=PJ.JobNo AND CJ.Job_Start<PJ.Job_Start
WHERE EQ.EQ_EquipmentID = SC.SC_EquipmentID
ORDER BY C.SN_ACCOUNT, CJ.Job_Start DESC

Hope This Helps, PH.
FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

Hi PH

Apologies for the long time between responses!!

I have tried what you have suggested and still cannot get it to work! I've also done some further research and come up with the following SQL:

SELECT t1.JobNo, t1.SN_ACCOUNT, t1.Job_Start, IIf([t1]![Eng_Service],"Service",IIf([t1]![Eng_Breakdown],"Breakdown",IIf([t1]![Eng_Commission],"Commission","S/Works"))) AS JobTransactionType, (SELECT max(t2.Job_Start) from tblJobs as t2 WHERE t2.SN_ACCOUNT = t1.SN_ACCOUNT AND t2.Job_Start < t2.Job_Start) AS NextValue, t1.Job_Start - NextValue AS Diff
FROM tblJobs AS t1
ORDER BY t1.SN_ACCOUNT, t1.Job_Start DESC;

I cannot get a date to appear within NextValue and as such, cannot calculate a difference in days between t1.Job_Start and NextValue. I'm assuming that this could be to do with date formatting but I'm at a loss of where to go next!

Could you please take a look and advise?

Many thanks

Steve

still cannot get it to work
If you really want help then, please, explain what happens !

Anyway, I'd replace this:
t2.Job_Start < t2.Job_Start
with this:
t2.Job_Start < t1.Job_Start

Hope This Helps, PH.
FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?