Search results

You dont use DSum or any D thing in a query or in any database in general. They are query's in them selves, but a slow and bad habit limitted version of a query. Stop using them ASAP! Try something along these lines. SELECT CardInfo.CardID , PassengerInfo.PassengerName ...

I have adjusted my local test data to conform with the test data I put on the forum and then this is the output: ID BATCH DEPT STARTDT NEWDEPT ENDDT RIJNR DEPT_ENDDT 5 X 1 2020-04-01 1 2020-04-02 1 2020-04-02 9 X 3 2020-04-02 1...

The result is different because I am using different dates in my (new) table. And yes, the last record is without enddate (in your query) but since it is contained in a (second) meta table, it is easy to join that and nvl(Thisdate, MetaDate) Enddate to get the right enddate. Number of records...

Your query works nicely as well... just have to change the INNER join to LEFT SELECT X.Batch, X.Dept, Min(X.StartDT) AS MinOfStartDT, X.qEnd FROM (SELECT R.Batch, R.Dept, R.StartDT, Min(S.StartDT) AS qEnd FROM tbl AS R LEFT JOIN tbl AS S ON R.Batch = S.Batch AND R.Dept <> S.Dept...

Good catch about record 77, it does indead get its end date from a meta record of Batch X. Batch X has both a Startdate (= startdate on ID 5) and Enddate (which doesnt exist in this recordset). This enddate eventually is the end of the batch thus the enddate of the last record (77) Without the...

Column names where thrown online, are not actual column names, should have thought a bit more about them. Partition over batch and department I tried, it breaks in the same way both your codes do... "joining" the seperated periods of Department 3.

@CJ_London I had to rewrite your query to SELECT R.Batch, R.Dept, R.StartDT, (SELECT max (EndDT) FROM (SELECT P.* FROM tbl AS P LEFT JOIN tbl AS E ON P.EndDT = E.StartDT AND P.Dept = E.Dept AND P.Batch = E.Batch WHERE...

@CJ_London ID BATCH DEPARTMENT START 5 X 1 01-4-2020 9 X 3 02-4-2020 15 X 4 05-4-2020 28 X 4 07-4-2020 44 X 3 11-4-2020 6 X 3 15-4-2020 66 X 3 18-4-2020...

@bastanu Fixed the dates, so yeah there should be 2 groups of department 3 seperated by departement 1.

@bastanu Vlad Thank you for trying, it is close, but no sigare. In reality the sequence extents en repeats even more.. adding: 7 X 3 25-4-2020 28-4-2020 77 X 3 28-4-2020 01-5-2020 Breaks your solution :( @CJ_London Its saturday seems like I worked...

I have considered making a udf, they are expensive when run from queries considering the mass of data this needs to run on I have just about dismissed that option. I considered writing a dozen or so joins even 50 to make make sure and just writing a big ass Case statement to weed thru the...

So i have a table ID BATCH DEPARTMENT START END 5 X 1 01-4-2020 02-4-2020 9 X 3 02-4-2020 05-4-2020 15 X 4 05-4-2020 07-4-2020 28 X 4 07-4-2020 11-4-2020 44 X 3 11-4-2020...

So i have a table ID BATCH DEPARTMENT START END 5 X 1 01-4-2020 02-4-2020 9 X 3 02-4-2020 05-4-2020 15 X 4 05-4-2020 07-4-2020 28 X 4 07-4-2020 11-4-2020 44 X 3 11-4-2020...

you already have the calculation, which I presume to be in a query? Simply plot the query on a graph and you should be done?

Unfortunately access doesnt allow for the "normal" route most big boy's databases allow, so in access you need to make a 2 step query 1) Select distinct SeatNumber, EmpID, Month from yourTable Of if you are intrested in the numbers Select SeatNumber, EmpID, Month, count(*) as theCount from...

You can use a hidden form that the user cant see or even your main menu and use the "On close" event to run your cleanup code. Though the very need makes me wonder about the database :(

You are not thinking accoording to a database, you do not store this seperately therefor do not need to update the field What you want to do is simply run a query to sum all the FX_value 's per year and month and be done with it.

And more on stuff like "is there other life out there" or what does the other side of the moon look like. And even "simple" stuff like the JSF .... Could make a list as long as my arm, but yeah... loads of moneys beeing spend on ambitious projects... though partly offcourse those projects do...

mortality is nasty and stuff, but the real problem will still come with the aftermath. Those that ended up on ICU with a serious infection(s) are at heavy risk of beeing patients for the rest of their lives with more or less serious effects

data can only be destroyed once, assuming it is the only source offcourse. Too often one finds, I have chosen not to process this data, however now I need it.