Query to look for answer in different Row.

[OSU-BigToe]

I have a database that I am building to look at the run rates and machine efficiency. I have a table that has the following columns
-- "Main" Table --
"Line #"-which production line it was run on
"Sample #" -the production order for the day (i.e 1, 2, 3...)
"Time in" -time that the product started running
"Time Out" -time that the product finished running
"Date Code"
"Startup-Changeover Time"
--"Start Time" Table--
"Date Code"
"Line #" (linked to Line # in "Main" Table")
"Start time"

I need to do two things, both are update queries that will update the "Startup-Changeover Time" column
1. If "Main.sample #" = 1 then take the "Main.Time in" from the same row and subtract it from "Start Time.start time" from the same "date code" and "line #"
2. If "Main.sample #" > 1 then take the "Main.Time in" from the same row as "Main.sample #" and subtract it from the ""Main.Time Out" from "Main.sample #"-1 (i.e if the sample # is 3 then I want to look at the time out from sample # 2) from the same "date code" and "line #

I hope that this makes sense. If there are any questions or it would be easier to see the database let me know. Thanks for your help

 

[CJ_London]

Query 1 is fairly straightforward:

UPDATE Main SET Startup-ChangeoverTime= dlookup("starttime","StartTime","DateCode= #" & DateCode & " AND Line# = " & line#) - TimeIn WHERE SAMPLE#=1

Query 2 assumes there are no breaks in the sample numbers

UPDATE Main SET Startup-ChangeoverTime= dlookup("TimeOut","Main","DateCode= #" & DateCode & " AND Line# = " & line# & " AND Sample#=" & sample#-1) - TimeIn WHERE SAMPLE#>1

 

[OSU-BigToe]

Thanks for the help. I had to change a few things for spacing and removing the # from line # and such. Having some issue. Going to send you the database so you can see it.

 

[OSU-BigToe]

Let me know your thoughts

 

[CJ_London]

having spaces in field and table names is generally a bad thing - so if you take my sample code and substitute like for like and surround with square brackets - e.g. starttime becomes [start time]. With regards Line # that is what you called it?

 

[OSU-BigToe]

I changed all of the field and table names to not have any spaces or "#" in them. Both queries seem to find the correct number of fields to update but I am getting a "type coverstion failure" in both of them I have looked and here is how the fields are set up.

line, sample, DateCode are all Number fields
TimeIN, StartupChangeoverTime are date/time

If it would be easier to mail you the database send me a, email adamtohill At gmail.com

 

[CJ_London]

OK
I've, checked it over and:

Query 1 - you still have a space in the Start Time table! you also have a superfluous # after datecode =

In addition, I'd forgotten that when referring to fields in the update to part, they need to be in square brackets = so this is the revised code (I've left the space in the table name)

UPDATE Main SET Main.StartupChangeoverTime = DLookUp("starttime","Start Time","DateCode=" & [DateCode] & " AND Line =" & [line])-[TimeIn]
WHERE (((Main.[SAMPLE])=1))

Query 2 - has the # and square bracket issue so revised code is

UPDATE Main SET Main.StartupChangeoverTime = DLookUp("TimeOut","Main","DateCode= " & [DateCode] & " AND Line = " & [line] & " AND Sample=" & [sample]-1)-[TimeIn]

My apologies for forgetting about the square brackets

 

[OSU-BigToe]

Thanks for the reply. Sorry it took me so long to get back to you to let you know it worked. Next related question, can these be set up to run one after the other? When one is run it overwrites with either the value or blanks it out. Thanks again

 

[CJ_London]

not sure what you mean?

Just revisited the code I supplied and the second query is missing WHERE SAMPLE#>1 in post #7

Have you got this in.