Update field with previous record info 1

[barbola]

I have a table (tblProdn) that I append to daily. The original data is linked to an Excel Sheet. I have two new fields in the tblProdn table that I would like to update with two fields in the previous record as shown (last ticket and last time)

Customer Ticket Time LastTicket LastTime
ABC Co 8005 4/1/05 4:30 PM 8004 ...
DEF Co 8006 4/1/05 5:55 PM 8005 4/1/5 4:30 PM
ABC Co 8008 4/1/05 8:19 PM 8006 4/1/5 5:55 PM

There is no ID field, as the Ticket is a unique field. Also, after the data is appended, I have to delete some records that are invalid (ie. Null values or void invoices with null times, etc) which is why I do not have consecutive numbers.

I've followed some of the ideas on here, but nothing seems to work for me including joining the table to itself.

Any new ideas?

Thanks!
Barb E.

 

[PHV]

Something like this ?
SELECT A.Customer, A.Ticket, A.Time, Max(B.Ticket) AS LastTicket, Max(B.Time) AS LastTime
FROM tblProdn AS A LEFT JOIN tblProdn AS B ON A.Ticket > B.Ticket
GROUP BY A.Customer, A.Ticket, A.Time;

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

 

[Remou]

Hi
May be a bit off the wall, but have you considered a hidden form? It would have two textboxes, which could be updated in the after update event of your main form.

 

[barbola]

Thank you PHV! That worked PERFECTLY!!!!

Thanks!
Barb E.

 

[barbola]

Oops...How would I incorporate this into an update query?
I keep getting the error
"Operation Must Use An Updateable Query"

I tried saving it as a separate query, and updating the table from that and still get the error.


Thanks!
Barb E.

 

[PHV]

It's often a bad idea to store derived/calculated values in a database ...
Either convert the query I gave you to a maketable query
Or try something like this:
UPDATE tblProdn
SET LastTicket = DMax("Ticket", "tblProdn", "Ticket<" & [Ticket])
, LastTime = DMax("[Time]", "tblProdn", "Ticket<" & [Ticket]);

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

 

[barbola]

Thank you again. I created a make table query and it worked this time.


Thanks!
Barb E.