Aceess 2002 SQL Query question... accumulation by group by sequence

Aceess 2002 SQL Query question.... putting together a scheduling module for an ERP system.... I have individual job orders and a stated amount of run time for each order... the user will then place each order on a production line (1-16) and then assign it a sequence on that line (1 through infinitiy) what I'd like to do is to create a column in the query that automatically updates the total accumulated time for each order on that line as a sequence is changed.... for example:

Orginal Seq

Order     Line    Seq     Time to Run    Accum Time
A            1         1           2                  2
B             1         2           3                 5
C             1         3          2                  7
D            2         1           4                 4
E            2         2           3                 7
F            2        3            6                13

Updated Seq

Order     Line    Seq     Time to Run    Accum Time
A            1        2           2                  5
B            1         1           3                 3
C            1         3          2                  7
D            2         3           4                 13
E            2         2           3                  9
F            2        1            6                  6

The orders may not be currently sorted in sequence or line order for that matter.
zrlittleAsked:
Who is Participating?
 
aesmikeCommented:
I see.  So does the user have a Form to do his updates or does he open this Query, datasheet view opens, and he just starts typing?
0
 
aesmikeCommented:
Ok, let's assum your table is called "ERP".  
I also assume that Accum Time is not a field in the table but you want it derived.
Try this:
select a.*, (select sum(timetorun) from erp b where b.line=a.line and b.seq <= a.seq) as [Accum Time]
 from erp a
0
 
zrlittleAuthor Commented:
This works excepting for the fact that i can't seem to update the sequence in the query??? Any ideas as to why that would be??
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
aesmikeCommented:
Can you explain in detail the sequence update?  Are you saying you want to update the actual sequence field?  or do you want to update some accumulated time field for that particular sequence?
0
 
zrlittleAuthor Commented:
An overview of the process.... We import order records from an existing SQL table (from my ERP)...
Those records come in with no line or sequence data associated into a holding table called orders... we then use a select query to maipulate sum of the data and create a table called open work orders this table still has no line or sequence info for each work order.... Then we use another select query that sorts the data by three different fields... it's in this select query where the scheduler will update the line and sequence information... What I need to have happen is that as he updates the line and sequence info the accumulated time.. also needs to be updated... I will then take the accumulated time add it to a start date and this will give us the scheduled end date which will be compared to the end date that got imported with the open order... Make sense???
0
 
aesmikeCommented:
Yes.  What wasn't clear was that a user updates this field by hand.  
So, the issue for this question is that there is an acutal accumulated time field that needs to be updated, not the actual sequence number.  That being the case, you can't make an updatable query out of that.  You have to change the query so that it dumps into a table.  Then use that table to update your ERP.
This will dump the query results into a table called FOOBAR:
SELECT a.*, (select sum(timetorun) from erp b where b.line=a.line and b.seq <= a.seq) AS [Accum Time] INTO foobar
FROM erp AS a;

Now you do a 2nd query that updates your ERP table from this.  Hopefully you have a primary key on ERP.
0
 
zrlittleAuthor Commented:
No the actual sequence number does get updated as well as the line number... this the drives the change in the accumulated hour field... Are we saying the same thing???
0
 
aesmikeCommented:
I think we are.  
You threw me off when you said:
>>> This works excepting for the fact that i can't seem to update the sequence in the query???
That sounded like you were asking me how to update the sequence number-not- the accumulated time.
by "sequence",I think you were refering to the row, itself.
0
 
zrlittleAuthor Commented:
No by sequence I was referring to the value in the field seq... what I'd like is the user to look at the record in a query and see no line and no sequence currently assigned... he then keys a line number and a sequence number into the fields line and seq respectively.... this then changes the accumulated hrs field automatically.... currently the sql select statement that we use in our scheduling orders query looks like this....

SELECT [WORK ORDER].Line, [WORK ORDER].Sequence, [WORK ORDER].Changeover, [WORK ORDER].[Order status], (([Length (customer)]*[QtyToManufacture]/(12*[ActualBoardPerSizer]))/([feet/min]*60)+0.12*(([Length (customer)]*[QtyToManufacture]/(12*[ActualBoardPerSizer]))/([feet/min]*60)))+[changeover] AS [Duration(hrs)], [Duration(hrs)]/24 AS Days, Profiles.Profile, ConfigurationID.Configuration1, Color.Color, Length.[Length (customer)], [WORK ORDER].[Due date], [WORK ORDER].[Promise Date], IIf([Promise Date] Is Not Null,[Promise Date],[Due Date]) AS RevisedShipDate, Profiles.[Boards/sizer std], [WORK ORDER].[Boards per sizer], IIf([Boards per sizer] Is Not Null,[Boards per sizer],[Boards/sizer std]) AS ActualBoardPerSizer, [WORK ORDER].QtyToManufacture, [WORK ORDER].FinishedBoards, [WORK ORDER].AtThisMoment, [AtThisMoment]+(((([QtyToManufacture]-[FinishedBoards])*([Duration(hrs)]/[QtyToManufacture]))/24)+(2/24)) AS UpdatedEndDate, [WORK ORDER].Order, [WORK ORDER].[Pallet#], [WORK ORDER].[#ofSkids], [WORK ORDER].Pieces_skid, [WORK ORDER].PiecesFractionalSkid, [lbs/ft]*([length (customer)]/12)*([Pieces_skid]) AS Std_Skid_Weight, Customer.Customer, [WORK ORDER].[Order Date], [WORK ORDER].QtyOrdered, [WORK ORDER].QtyAllocatedFromStock, Profiles.[Feet/min], [WORK ORDER].[Start Date], [WORK ORDER].[End Date], [End Date]-[Start Date] AS [Actual Days], IIf([End Date]>[Due Date],[End Date]-[Due Date]," ") AS [Actual Days late], IIf([End Date] Is Not Null," ",IIf((Date()-[Due date])<0," ",Date()-[Due Date])) AS DaysLateAtThisMoment, IIf([End Date] Is Not Null,[Actual Days],IIf([AtThisMoment] Is Not Null,[ActualDaysInProcess],[Days])) AS [Actual Duration], IIf((([CalEndDate])-[RevisedShipDate])>0,[RevisedShipDate]-[CalEndDate],[RevisedShipDate]-[CalEndDate]) AS Days_Early_Late, ([AtThisMoment]-[Start Date])+(((([QtyToManufacture]-[FinishedBoards])*([Duration(hrs)]/[QtyToManufacture]))/24)+(2/24)) AS ActualDaysInProcess, [WORK ORDER].CalStartDate, [WORK ORDER].CalEndDate, [WORK ORDER].SizerConflict
FROM Profiles INNER JOIN (Length INNER JOIN ((ConfigurationID INNER JOIN (Color INNER JOIN [Items Syteline] ON Color.ColorID = [Items Syteline].ColorID) ON ConfigurationID.ConfigurationID = [Items Syteline].ConfigurationID) INNER JOIN (Customer INNER JOIN [WORK ORDER] ON Customer.CustomerID = [WORK ORDER].CustomerID) ON [Items Syteline].Item = [WORK ORDER].Item) ON Length.LengthID = [Items Syteline].LengthID) ON Profiles.ProfileID = [Items Syteline].ProfileID
WHERE ((([WORK ORDER].[Order status])="1" Or ([WORK ORDER].[Order status])="2" Or ([WORK ORDER].[Order status])="3" Or ([WORK ORDER].[Order status])="4" Or ([WORK ORDER].[Order status])="5" Or ([WORK ORDER].[Order status])="6" Or ([WORK ORDER].[Order status])="7" Or ([WORK ORDER].[Order status])="8") AND (([WORK ORDER].CanceledOrder)=False))
ORDER BY Profiles.Profile, ConfigurationID.Configuration1, Color.Color, Length.[Length (customer)] DESC , IIf([Promise Date] Is Not Null,[Promise Date],[Due Date]), [WORK ORDER].[Due date];

This query allows us to update both the sequence and the line..
0
 
zrlittleAuthor Commented:
he opens this query in a datasheet view and just starts typing....
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.