Start data to be equal to End data of previous record? 1

[Sanasta]

Hi,
I have a problem. I would like data in a field KmEnd to be equal to KmStart in a previous record for the Same RegNo. (RegNo is not a primary key). I have no idea how to do this.
Kindly please help me.
Sanasta

 

[CautionMP]

Try looking at the Domain Aggregate Functions, probably [tt]DMax()[/tt] or [tt]DLast()[/tt].

If you need more help psot more specific details on what your trying to do and we can help,
CMP

Funny thing about being unemployed, weekends don't mean quite so much, just means you get to hang out with your working friends. Primus

 

[Sanasta]

Hi,
I have a query qryKursAvtovozy with fields KomNo (Primary key), KomDate, RegNo, KmStart, KmEnd, TankStart, TankEnd, etc.
I have a form based on this query KursAvtovozy with text box controls with Control Sources above fields.
I need to solve the following task but have not enough experience:
For any particular vehicle (RegNo) the value of KmStart (TankStart) to be equal of Km End (TankEnd) from previous record. If the record is first for the same vehicle KmStart to be written but for any further record start value to be taken from the last value of previous record.
I have tried with DLookup but without any success.
Hope somebody to help me.
Thanks in advance
Sanasta

 

[Zion7]

Why won't DLookUp work?
And even better, as CMP said, DMax?

Private Sub RegNo_AfterInsert()
Dim varKmEnd As Variant

varKmEnd = DMax("kmEnd","qryKursAvtovozy", _
"RegNo =" & Me.RegNo)

If Not IsNull(varKmEnd) Then
Me.KmStart = varKmEnd
End If

End Sub

 

[Sanasta]

Hi,
Sorry, but it does not work
I shall try something else
Thanks

 

[sfatz]

Without further details, it's hard to guess what is really desired here. I think Zion7 may be on the right track, however, there is no guarantee that the last kmEnd is also the maximum kmEnd. I think that DLast is a better choice instead of DMax.

Sanasta,
On the off chance that you do not understand what Zion7's code is doing . . .

First he declares the variable varKmEnd to store the value.

Then he looks for the maximum value of kmEnd in the table/query qryKursAvtovozy where RegNo = the value in the control on your form that is called RegNo. He stores that in varKmEnd

He then checks varKmEnd to see if there is a value there. If there is, he stores that in the control on your form that is called KmStart. If not, then nothing gets done.

I hope this helps

 

[Sanasta]

Thank you Sftas and Zion,
What you try to explain me is clear for me. I understand and apply code of Zion without any success. I follow step by step your instructions, Sftas. RegNo is control on my form as well as field in my table. KmEnd is indeed maximum value for each avtovoz (car carrier truck on my native language). I think that may be my form is more complicate than I explain. KursAvtovozi is a very complex form with many tabs and subforms, but the fields I am quoted are in main form.
what i do not understand is whether I have use the correct event, because there is event AfterInsert for form itself and when I enter code builder for this event
Private Sub Form_AfterInsert() appears but not
Private Sub RegNo_AfterInsert()
May be here is somthing difficult for me to understand.
I shall consult my friends and hope to find solution.
Sanasta

 

[PHV]

Private Sub RegNo_AfterInsert()
You meant Private Sub RegNo_After[!]Update[/!]() I guess.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Sanasta]

Thank you PHS,
I have create text box control on ny form named Km_Start.
Than in After Update Event Procedure of RegNo Conntrol wrote
Private Sub RegNo_AfterUpdate()
Dim varKmEnd As Variant

varKmEnd = DMax("kmEnd", "qryKursAvtovozy", _
"RegNo =" & Me.RegNo)

If Not IsNull(varKmEnd) Then
Me.Km_Start = varKmEnd
End If
End Sub
Then I wrote RegNo. As result I have enter debugger yellowing
varKmEnd = DMax("kmEnd", "qryKursAvtovozy", _
"RegNo =" & Me.RegNo)

Sanasta

 

[PHV]

Any error message ?
I guess something like Type mismatch.
If RegNo is defined as text in qryKursAvtovozy then you may try this:
[tt]varKmEnd = DMax("kmEnd", "qryKursAvtovozy", "RegNo='" & Me!RegNo & "'")[/tt]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Sanasta]

Thank you PHV,
Trying your advice I have avoid Debugger, but text boxes in each records became equal to max value of this concrete Reg No, not for particular record as I want.
I thought a lot over my problem and came to a conclusion that I have not explained it quite well, so kindly please to be apologized for I have not enough professionalism for this forum wasting your time.
How I have explain before KomNo (# of concrete trip) is my primary key. There is a lot of data in my DB. Simply I do not want kmstart to answer any event at all. I want to write any formula like in a simple select query but do not know how to do this. Most often when I enter RegNo I have neighter KmStart nor kmEnd, nor any other data at all. When the driver returns I enter data in database and waste time searching whether kmends of previour trip are equal those starting next trip. So I simply want to get mileage for starting position directly from mileage of ending position of previous trip for the same truck. I have tried to write formula in a select query without result, try with macros, code, etc.
In the end I shall leave things like they are writing KmStart and KmEnd as separate fields like so far.
Shall appreciate any help. If I find solution I have advise forum accordingly.
Sanasta

 

[ddiamond]

Sanasta,

I don't think it should be too difficult to do this strictly from a select query, but I have a couple of questions first.

Is there any field that orders your trip records such as a sequence number? If so, are the sequence numbers continuous, or could there be gaps? If you don't have a sequence number, how are your records being ordered?

- Dan

 

[Sanasta]

Hi Dan,
Thanks you try to help.
Field KomNo contains unique sequence numbers: 1, 2, 3, 8, 11, ...., 1211, 1215, ... 1227 from totally 687 records - with gaps. These numbers we get from company's order book and are convenient for primary key as tey are unique. For example
KomNo KomDate RegNo
659 11.07.05 C 8738 HK
660 11.07.05 C 6250 XC
662 12.07.05 C 0847 MB
663 12.07.05 C 3027 XM
664 12.07.05 C 2576 XC
665 12.07.05 C 3027 XM
666 12.07.05 C 2576 XC
I have 29 trucks and they depart different days and duration of trips is different due to different itineraries.
This year I have introduce somthing new adding figure 6 (like 2006) before KomNo: 0600001, 0600002, etc. in order not to confuse records for 2006 and 2006 years.
Hope you to find solution.
Sanasta

 

[ddiamond]

Just to make sure I understand, in your example, would you want the KMStart from record 665 to equal the KMEnd from record 663? And KMStart from 666 equal to KMEnd from 664?

 

[ddiamond]

The following select statement will return the current mileage and the prior mileage assuming that you know the KomNo of the current trip. I'm not sure the name of your table, so I called it Trips in my example.

select a.KmEnd as CurrentMileage, b.KmEnd as PreviousMileage
from Trips a Inner Join Trips b on
  a.RegNo = b.RegNo
where a.KomNo = <Current KomNo>
  and b.KomNo < a.KomNo
  and not exists (
    select * from Trips c
    where c.KomNo between b.KomNo and a.KomNo)

You will definately want to add a non-unique index to RegNo. Otherwise your performance will be poor. I'm assuming you have some way of determining <Current KomNo>. If not, we can discuss other options.

 

[Sanasta]

Thanks Dan,
Kindly please apologize me for not responding but unfortunately now I am with temperature and pneumonia. Now I have try to do someting but not understand all.
What I understand is that your code is a SQL view of a Select query. What I understand is that I add my table RouteAvtovozi (or Trips) twice (or may be three times because it is in my source query yet) with alias a and b. Then I create fields CurrentMileage and PreviousMileage. Then I join RegNo on a and b and further ...
I answer to thank you but now I am ill and need a rest.
Exuse me
Sanasta

 

[TheAceMan1]

How are ya Sanasta . . .

At the beginning of the thread [blue]DMax[/blue] was suggested by [blue]CautionMP[/blue] and an example given by [blue]Zion7[/blue]. [blue]PHV[/blue] pulling up the rear suggested you move the code to the [blue]AfterUpdate[/blue] event of RegNo . . . with which I agree.

I've simulated [blue]DMax[/blue] for your situation as above and see no reason why your having problems, unless your not installing the code properly.

Are you placing the code on the AfterUpdate [blue]event line[/blue] in the properties window, or in the AfterUpdate event in the [blue]VBE editor?[/blue]

I know you need this ASAP, but don't get in such a rush you miss hearing you've won the lotto! ;-)

[purple]BTW . . . Get Better![/purple]

See Ya! . . . . . .

 

[Zion7]

Well put, AceMan!

Sanasta, your post after the error message, raised a few questions in my mind,
"...but text boxes in each records became equal to max value of this concrete Reg No, not for particular record as I want...."

Private Sub RegNo_AfterUpdate()
Dim varKmEnd As Variant

varKmEnd = DMax("kmEnd", "qryKursAvtovozy", _
"RegNo ='" & Me.RegNo & "'")

If Not IsNull(varKmEnd) Then
Me.Km_Start = varKmEnd
End If
End Sub

Is regNo unique?, If so, it shouldn't be.
You want max value for a particular truck.
You're looking for its Mileage, No?
Shouldn't your critetria be the TruckNo?

varKmEnd = DMax("kmEnd", "qryKursAvtovozy", _
"TruckNo ='" & Me.TruckNo & "'")

 

[Sanasta]

Thank you AceMan and Zion7,
RegNo is the number of registration of concrete truck. And RegNo is an index, but not unique. Unique is my primary key, the number of trip KomNo. I did exactly what you quote above. When the truck depars I get its number from a combo box. But all situations are different. Sometimes I have neither kmstart, nor kmend because of issuing KomNo when concrete truck is abroad. What I need is in case I or anybody else enter kmend in next trim whether it exists or not in a text box named kmstart kmend to appear (in current record which is just the next one for concrete truck) and I am absolutely sure Dan's idea is perfect but first of all it is not so easy for me to understand absolutely how to accomplish and now I am not quite well to work hard, but shall try and if don't manage myself shall turn to Dan for help.
Cordially thak you all for trying to help me.
Sanasta

 

[Sanasta]

Hi DDiamond,
Following your advice I have try step by step your code.
First in SQL view I have written:

SELECT Trips.KomNo, Trips.KomDate, Trips.RegNo, a.KomNo, b.KomNo, a.KmEnd AS CurrentMileage, b.KmEnd AS PreviousMileage
FROM Trips INNER JOIN (Trips AS a INNER JOIN Trips AS b ON a.RegNo = b.RegNo) ON Trips.KomNo = a.KomNo
WHERE (((a.KomNo)=[Trips].[KomNo]) AND ((b.KomNo)<[a].[KomNo]));

The number of records increases 10 times what is normal as not exists c between is not defined but when I try 
[Code]
SELECT RouteAvtovozi.KomNo, RouteAvtovozi.KomDate, RouteAvtovozi.RegNo, a.KomNo, b.KomNo, a.KmEnd AS CurrentMileage, b.KmEnd AS PreviousMileage
FROM RouteAvtovozi INNER JOIN (RouteAvtovozi AS a INNER JOIN RouteAvtovozi AS b ON a.RegNo = b.RegNo) ON RouteAvtovozi.KomNo = a.KomNo
WHERE (((a.KomNo)=[RouteAvtovozi].[KomNo]) AND ((b.KomNo)<[a].[KomNo]) AND ((Exists (select * from RouteAvtovozi c where [c].[RegNo] = [a].[RegNo] and c.KomNo between b.KomNo and a.KomNo))=False));
none records appear.
I continue with attempts.
Sanasta