Previous record, data comparison 1

[Halliarse]

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

 

[PHV]

Something like this ?

SELECT C.JobNo,C.Job_Start,Nz(P.Job_Start-C.Job_Start,99) AS Visit_Gap
FROM tblJobs C LEFT JOIN tblJobs P ON C.JobNo=P.JobNo AND C.Job_Start<P.Job_Start

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

 

[Halliarse]

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

 

[PHV]

Use the SQL VIEW pane.

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

 

[Halliarse]

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;

 

[PHV]

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
FAQ181-2886

 

[PHV]

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
FAQ181-2886

 

[Halliarse]

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

 

[PHV]

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 < t[!]1[/!].Job_Start

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