Major Challenge - Update a table based on calculation

[Chimp8471]

I am so interested to see a solution to this little problem…i think ithis is one of my more complicated questions!!!!

Problem:

Where I work we have a series of machines… named “ C1, H7, H8 etc”

Each line has 3 counters on it,

Impressions – records the number of cartons used on the line
Standard Pack – records the number of filled cartons with product
Output – records the number of cartons that complete the whole line process and are ready to be shipped to the customer.

Sounds straight forward enough….but the process gets a little more complicated.

Some lines are referred to as joint lines… this indicates that the product from each line the output figure is joined together and totalled. So it is not possible to calculate how many each line completed for the output figure. Note… it is only the Output figure that is joint, each line still has it’s own impressions and standard park counters

Grouped lines are given a group code – P8 in this case.

Example…

C1 not joined

H7 and H8 joined.

C1 –
Impressions –1000 Cartons
Standard Pack – 900 Cartons
Output – 800 Cartons

H7 - P8
Impressions –1600 Cartons
Standard Pack – 1500 Cartons

H8 – P8
Impressions –400 Cartons
Standard Pack – 300 Cartons

P8
Output – 1600 Cartons

The output counter is brought in to the database automatically via a linked spreadsheet and a macro, this data is then stored in a table called “DaycodeMachineShiftImpressions” you will see in this table that the grouping is referred to as Machine.

As previously stated we have no real way of working out the amount each line has output individually.. the way it is calculated at the moment, is to take the figure for P8 and split it into 2 equal figures and assign that to each line.

So in this case the figure assigned to each line would be 800, although you will see that this is not possible for H8 line as it only put 400 cartons on the line in the first place.

I now want to implement the following calculation to my DB,

(see Attached Spreadsheet)

the calculations a re stored in cells b12 & b13 in the spreadsheet.

so that the output figure is separated

in proportion to impressions made.

I then need the figure per line to be fed back to the “tblProduction” output field..

in the tblproduction the data for P8 is stored to line level so in this case as H7 and H8, hence why i want to use the calculation to work out the output figure per line...

Can this be done automatically, if so please help me…

Andy

 

[Chimp8471]

what a muppet!!!!

i have attached the wrong spreadsheet no wonder people cant see the calculation..

 

[ecniv]

Impressions – records the number of cartons used on the line
Standard Pack – records the number of filled cartons with product
Output – records the number of cartons that complete the whole line process and are ready to be shipped to the customer.
...<snip>...
I then need the figure per line to be fed back to the “tblProduction” output field..

What tables do you hold and the sorts of values associated in them? (test data)

I think you need to calculate the P8 (and any other joined ones) first then add the joined results to the list of outputs that aren't joined and heaven't been reported.

Example:
LineOutput
ID - Line Identifier - Amount - Actioned
1 - C1 - 600 - No
2 - H7 - 800 - No
3 - H8 - 400 - No

LineToLine
ID - Joint Identifier - Line ID 1 - Line ID 2 - Reported Amount
1 - P8 - 2(H7) - 3(H8) - 1000

Then you need to find the percentage of output used.
So in C1's case its 100%
but in H7 and H8 (because they are combined) you need to get the calculation (which you have via your spreadsheet): Percentage = Total Amount Reported / (H7 + H8 Actually made)
For this you can get a query.

I'll edit this post in a short while


Edit:

SELECT tblopLines.Ident, [tblopactual].[ActualOP]*nz([qrysubperc].[percentage],1) AS ReportedAmount
FROM 
((tblopLines LEFT JOIN tblopJoined ON (tblopLines.LineID = tblopJoined.LineID2) [i]OR[/i] (tblopLines.LineID = tblopJoined.LineID1)) 
LEFT JOIN (SELECT tblopJoined.JoinedID, tblopJoined.Ident, [tblopJoined].[Amount]/([tblopActual].[ActualOP]+[tblopActual_1].[ActualOP]) AS Percentage
FROM (tblopJoined LEFT JOIN tblopActual ON tblopJoined.LineID1 = tblopActual.LineID) LEFT JOIN tblopActual AS tblopActual_1 ON tblopJoined.LineID2 = tblopActual_1.LineID) 
AS qrySubPerc ON tblopJoined.JoinedID = qrySubPerc.JoinedID) 
LEFT JOIN tblopActual ON tblopLines.LineID = tblopActual.LineID;

Ok, the above cannot be viewed in the query builder, 'cause I changed the italics OR from an AND. Otherwise it would be more inefficient as it would need to run the sub query twice; which is pointless

Anyway, this may giive you an idea, I won't say its the best, because I don't think it is; its not based on real life data and the relationships of real life data.


Sub query is essentially:

SELECT 
  tblopJoined.JoinedID, 
  tblopJoined.Ident, 
  [tblopJoined].[Amount]/([tblopActual].[ActualOP]+[tblopActual_1].[ActualOP]) AS Percentage
FROM 
 (tblopJoined LEFT JOIN tblopActual ON tblopJoined.LineID1 = tblopActual.LineID) 
 LEFT JOIN tblopActual AS tblopActual_1 ON tblopJoined.LineID2 = tblopActual_1.LineID;

Tables:
tblopActual
ActualOPID - Auto - pk
LineID - number
ActualOP - number

tblopJoined
JoinedID - auto - pk
Ident - text
lineid1 - number
lineid2 - number
amount - number

tblopLines
LineID - auto - pk
Ident - text