=DateDiff( between multiple dates?????

[Brianwarnock]

I thought that that was what you wanted, the sum of all the days,. You said that once you ha d the column of days you were going to sum them.

If you want each individual day period then it's back to the drawing board and without a date out on each record I thing it must be VBA, but maybe somebody has a way of doing it in SQL.

THe code thingy is done by using menu items above the typing area.
The # is the code, highlite your code and then click on it.

Brian

 

[weeblebiker]

yah, I think I need to group by [Serial #] ascending order on the [Date in]
then for each group have some sort of macro that select the earliest [Date in] and the next [Date in] in the [Serial #] group to generate a DateDiff
Then a loop to select the previously selected next [Date in] and the next [Date in] in the group to generate a DateDiff and repeat till there is no more dates in the [Serial #] Group.

or something like that

 

[Brianwarnock]

http://baldyweb.com/index.htm

The above contains a sample DB showing how to get info from a previous record. It involves subqueries and I haven't got the time at the moment to examine it and convert it to your requirements, but you might like to try.

Brian

 

[weeblebiker]

so I looked at that and wrote this

SELECT [XRS-3 repair].[Serial #], [XRS-3 repair].[Date in], (SELECT [Date in] FROM [XRS-3 repair] AS Alias WHERE  [Date in]= (SELECT Max([Date in]) FROM [XRS-3 repair] AS Alias2 WHERE Alias2.[Date in] < [XRS-3 repair].[Date in] AND Alias2.[Serial #] = [XRS-3 repair].[Serial #]) AND Alias.[Serial #] = [XRS-3 repair].[Serial #]) AS PrevEnd, DateDiff("d",PrevEnd,[Date in])
FROM [XRS-3 repair]
ORDER BY [XRS-3 repair].[Serial #], [XRS-3 repair].[Date in]

AND IT ALMOST WORKS!:banghead::banghead::banghead:

It returns:
The [Serial #] in order (albeit text order but that's another minor issue)
The [Date in],
The [Prev End],
And [Exp 103] DateDiff

,,,,,all look right,,,,

then a window pops up that says "At most one record can be returned by this subquery."
what did I bugger???

 

[weeblebiker]

so I think I need to dich the outer alias statment as it refers the field to itself (maybe returning one record?).
I'm not at work (db) but does this look right?

SELECT [XRS-3 repair].[Serial #], [XRS-3 repair].[Date in],(SELECT Max([Date in]) FROM [XRS-3 repair] AS Alias WHERE Alias.[Date in] < [XRS-3 repair].[Date in] AND Alias.[Serial #] = [XRS-3 repair].[Serial #]) AS PrevEnd, DateDiff("d",PrevEnd,[Date in])
FROM [XRS-3 repair]
ORDER BY [XRS-3 repair].[Serial #], [XRS-3 repair].[Date in]

 

[CJ_London]

Your subquery looks right - my interpretation is it is bringing through the previous date for each serial # and DateIn

 

[CJ_London]

Thinking about it, not sure you can incorprate the subquery into your datediff function.

If you have a problem, try this instead

SELECT DISTINCT [XRS-3 repair].[Serial #], [XRS-3 repair].[Date in] As DateIn, [XRS-3 repair_1].[Date in] AS PrevEnd, DateDiff("d",[XRS-3 repair_1].[Date in] ,[XRS-3 repair].[Date in])
FROM [XRS-3 repair] INNER JOIN [XRS-3 repair] as [XRS-3 repair_1] ON [XRS-3 repair].[Serial #] = [XRS-3 repair_1].[Serial #]
WHERE [XRS-3 repair_1].[Date In] = (SELECT Max([Date in]) FROM [XRS-3 repair] AS Alias WHERE Alias.[Date in] < [XRS-3 repair].[Date in] AND Alias.[Serial #] = [XRS-3 repair].[Serial #]) 
ORDER BY [XRS-3 repair].[Serial #], [XRS-3 repair].[Date in]

 

[Brianwarnock]

Your first attempt worked on my test data but I have tweaked it to get it completely correct, however you will notice that I do not use spaces or special characters , except _ , in my object names.

SELECT XRS_3repair.serialnumber, XRS_3repair.datein,
 (SELECT [Datein] FROM [XRS_3Repair] AS Alias
 WHERE  [Datein]= (SELECT Max([Datein]) FROM [XRS_3Repair] AS Alias2
 WHERE Alias2.[Datein] <[XRS_3Repair].[Datein] AND Alias2.[Serialnumber] =[XRS_3Repair].[Serialnumber]) AND Alias.[Serialnumber] =[XRS_3Repair].[Serialnumber]) AS PrevEnd,[COLOR="Red"] IIf(Prevend is null,1,DateDiff("d",PrevEnd,[Datein])) AS Expr1[/COLOR]
FROM XRS_3repair
ORDER BY XRS_3repair.serialnumber, XRS_3repair.datein;

Brian

 

[weeblebiker]

my work internet connection is flakey and not finding any .co.uk sites
so I'm typing this at home during lunch, removing the "outer" alias statement worked, I think the "outer" alisa statement was for selecting a field based off the date where I'm only interested in the date, so that statement was slescting the date based off itself
now the sql returns a list like it should

SELECT [XRS-3 repair].[Serial #], [XRS-3 repair].[Date in],(SELECT Max([Date in]) FROM [XRS-3 repair] AS Alias WHERE Alias.[Date in] < [XRS-3 repair].[Date in] AND Alias.[Serial #] = [XRS-3 repair].[Serial #]) AS PrevEnd, DateDiff("d",PrevEnd,[Date in])
FROM [XRS-3 repair]
ORDER BY [XRS-3 repair].[Serial #], [XRS-3 repair].[Date in]

I'll give the code you guys suggest a whirl also, maybe be able to get a better understanding of some other code bits in addition to alias expressions.

Thanks a bunch for your help, I think I graduated a up level in my code monkeydom

 

[Brianwarnock]

Unfortunately although your results may look correct on your live data they are not.
You are not including the first day in your count,
so if the datein for an item are
1 Jan
2 Jan
10 Jan
you get

Null
1
8
but the Total should be 10
if you add 1 to the datediff you get
Null
2
9
Wrong again
With my mod to your first code you get
1
1
8

But to me the whole DB is illogical for not having a Date out as surely the correct answer is 1, 9, ?

I also notice that you have not responded to my naming comments, you are apparently at the beginning of your Access work, believe me having spaces and special characters other than the underscore will lead to trouble, and if you get to write VBA then access will not be able to help you with its intellisense.

Brian

 

[weeblebiker]

Unfortunately although your results may look correct on your live data they are not.
You are not including the first day in your count,
so if the datein for an item are
1 Jan
2 Jan
10 Jan
you get

Null
1
8
but the Total should be 10
if you add 1 to the datediff you get
Null
2
9
Wrong again
With my mod to your first code you get
1
1
8

But to me the whole DB is illogical for not having a Date out as surely the correct answer is 1, 9, ?

I also notice that you have not responded to my naming comments, you are apparently at the beginning of your Access work, believe me having spaces and special characters other than the underscore will lead to trouble, and if you get to write VBA then access will not be able to help you with its intellisense.

Brian

you are ASSUME'ing I'm wanting to do arithmetic with the numbers I am generating

There is a date out in the db which is not relevant nor is the exclusivity/inclusivity of the date difference range.

I will be summarizing the instances of time periods between [Date in]'s as in a second query with Sum(IIf([Date Diff] Between A And B, 1,0)) Statements where A and B are 0 to 90 days, 90 to 180 days, 180 to 365 days, 365 to 730 days and greater than 730 days which will dump into a larger summarizing query and report that is used as a trending tool.

I already have code written to catagorize the time period between new unit ship date and the first repair Date in. (two different tables and each serial number only appears once in the new unit table, so less of a challenge)

I know about the naming issue however this db is fairly extensive and mostly done so I will not change the naming nomenclature now, it is my first db and I will not carry forward some of the mistakes in initial setup. But thanks for pointing that out.

most of this db is classifying and summarizing repair information there is very little arithmetic based number manipulation.

again really, Thanks for the help!

 

[weeblebiker]

The question I am answering is "how many times have we performed a repair(on any unit) and the unit has come back for repair in 0-90 days, 90-180 days, etc"

 

[Brianwarnock]

LOL
That clarifies things, I was answering the question
"How long has this item been in the repair shop's different departments"??!

Brian

 

[weeblebiker]

LOL

IIf([Our repair guys keep watching the coffee pot brew coffee so they can have 1 fresh cup] Between [8:00AM] And [5:00PM], [your code],0))

 

[weeblebiker]

I'll remember to include the overriding question the coding question relates to in the initial post next time.

 

[Brianwarnock]

Thanks it is a good idea and I will say that you have shown yourself to be a quick learner who is not afraid to have a go so you should make good progress.

Cheers

Brian

 

[weeblebiker]

the more I learn, the more I am aware of and humbled by my ignorance

now if I could find a mesage board as helpfull as this one for dielectric breakdown chemistry :banghead: