[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2007-08-09
10
Medium Priority
?
714 Views
Last Modified: 2008-02-01
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.
0
Comment
Question by:zrlittle
  • 5
  • 5
10 Comments
 
LVL 10

Expert Comment

by:aesmike
ID: 19664062
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
 

Author Comment

by:zrlittle
ID: 19664988
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
 
LVL 10

Expert Comment

by:aesmike
ID: 19665022
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

by:zrlittle
ID: 19665230
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
 
LVL 10

Expert Comment

by:aesmike
ID: 19665312
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
 

Author Comment

by:zrlittle
ID: 19665671
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
 
LVL 10

Expert Comment

by:aesmike
ID: 19665711
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
 

Author Comment

by:zrlittle
ID: 19665963
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
 
LVL 10

Accepted Solution

by:
aesmike earned 2000 total points
ID: 19666092
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
 

Author Comment

by:zrlittle
ID: 19670383
he opens this query in a datasheet view and just starts typing....
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

868 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question