Using Previous Records to Find Totals

[JPed]

Hi everyone,

Basically what I have is a form where operators can input certain information about a production process, in particular the kg input which has gone into the 'basket'. However this is divided by customer order number and sometimes more than one customers order is placed into the same basket. This means that the weight of the record is not representative of the weight in the basket, and it is the weight of the basket which determines efficiency in the process.

Each basket is designated a 'Disc Number' which is put in the basket so the parts contained can be identified after the process.

What I am trying to do is right a code, whether in my table or form (or somehow creating a query) which compares the disc number of the previous record (or previous 2 or 3) and if they are the same the totals are combined. Each record the user places has a hidden ID which keeps them in order, and the records with the same disc number will always be entered after one another in that sequence.

How would I be able to code something to deal with this and where would I put it?

The table in question which contains the records is called Thetatbl and then form it links to is Thetafrm. The weight of each record is contained in Orderkgtxt.

 

[CJ_London]

You really haven't provided enough information for anyone to help you. Please post your table design including the field names and types,some sample data plus the result you would expect to get from the sample data

 

[JPed]

You really haven't provided enough information for anyone to help you. Please post your table design including the field names and types,some sample data plus the result you would expect to get from the sample data

Apologies!

Basically the production process runs with orders being contained within baskets of a certain capacity. More often than not, 1 basket = part of 1 order and thats fine, however sometimes too smaller orders will be put in the same basket. In my form, these two orders are put through as separate records as they are different material types. I need to summarise this total pot weight though.

Each order is put through the form with an Order Number (OrderNumbertxt), a Disc number [which are not unique and are rotated] (DiscNumbertxt), a Sample weight of one item (SampleIntxt), and a Quantity (Quantitytxt). If the entire basket is only full of one order then the Basket weight is found by SampleIntxt multipled by Quantitytxt. This is then displayed in "Weighttxt"

e.g.
Order Number: AJ00001234
Quantity: 100
Sample Weight: 100
Disc Number: 722
Weight: 10,000
Total Basket Weight: 10,000

In this case it is easy to work out the basket weight and therefore the % weight capacity it is filled too. (max capacity is 14,000 and therefore would be worked out as 71.4%)

The problem I am having is when two or three order numbers are placed in the same basket. This would look something like this:

Order Number: BJ0001122
Quantity: 25
Sample Weight: 200
Disc Number: 37
Weight: 5,000
Total Basket Weight: _____

Order Number: BJ0007524
Quantity: 30
Sample Weight: 100
Disc Number: 37
Weight: 3,000
Total Basket Weight: _____


These two basket weights need to be added together to make the total basket weight = 8,000 and this is the problem I am trying to solve. This would then be displayed in another box called "TotalBaskettxt". I think it might be possible if you had a function which said that if Disc Number = Previous Disc Number then Weight + Weight, but I have never really coded before so don't know how to do this!

Also the disc numbers are not unique per basket; they are basically metal lumps with a number on which are rotated weekly so it cannot just be that all disc numbers with "xx" are added together (unfortunately, I have tried to change this without success).

 

[JPed]

Here are a few pictures from my database which hopefully make it easier to understand. I also released I forgot to mention each order submitted has an ID number (just +1 from the last record)

http://i1350.photobucket.com/albums/p777/JPed1/accessproblem1_zps736d7327.png

So in this small portion of data I would want 7512 and 7513 to be added together as they had the same disc number, whereas 7514 and 7515 would be seperate as they went in different baskets.

This is my form which links to the table above.

http://i1350.photobucket.com/albums/p777/JPed1/accessproblem2_zps6ce2716e.png
[The names of all the text boxes are just followed by txt]

I was thinking of something where there is an IF function where you could potentially say that IF DiscNumbertxt of ID-1 DiscNumbertxt of ID, then TotalInput of ID-1 + TotalInput of ID. And if not then just TotalInput of ID.

I don't know if this is possible but I am a complete novice when it comes to Access and coding of any kind so I have no idea what to do!

 

[CJ_London]

So if I have understood you correctly from this

Order Number: BJ0001122
Quantity: 25
Sample Weight: 200
Disc Number: 37
Weight: 5,000
Total Basket Weight: _____

Order Number: BJ0007524
Quantity: 30
Sample Weight: 100
Disc Number: 37
Weight: 3,000
Total Basket Weight: _____

You have a total weight of 8000 which represents 57% weight capacity and this is what you are trying to calculate.

If this is the case the query would be:

Select [Disc Number], sum(weight/14000) as WtCapacity
FROM [Theta Inputs]
GROUP BY [Disc Number]

 

[JPed]

So if I have understood you correctly from this

Order Number: BJ0001122
Quantity: 25
Sample Weight: 200
Disc Number: 37
Weight: 5,000
Total Basket Weight: _____

Order Number: BJ0007524
Quantity: 30
Sample Weight: 100
Disc Number: 37
Weight: 3,000
Total Basket Weight: _____

You have a total weight of 8000 which represents 57% weight capacity and this is what you are trying to calculate.

If this is the case the query would be:

Select [Disc Number], sum(weight/14000) as WtCapacity
FROM [Theta Inputs]
GROUP BY [Disc Number]

The aim would be to find the total weight of 8000 to use in a percentage formula yeah.

The problem is that the Disc Number is re-used in the production process [it's basically a piece of metal that doesn't melt so we know which basket contains who's stuff] roughly once a week, so with this would it be able to just group any disc numbers which are the same from say the past 24 hours or past 5 records submitted?

Is there a way of saying group by disc number within (Current Date - 48 hours) or something similar?

EDIT: Have been playing around trying to do what you suggested and at first I couldn't get it to group by day but now I think it has managed to separate disc numbers by day so as long as the same disc is not used twice in the same day (they won't be), this should work!

 

[JPed]

^ Would there be any way to get these values for the pot weight to update/appear in the table/on the form? The production manager asked if it was possible so that operators could see relatively up to date information on how close they were getting to the maximum efficiency.

 

[CJ_London]

Would there be any way to get these values for the pot weight to update/appear in the table/on the form? The production manager asked if it was possible so that operators could see relatively up to date information on how close they were getting to the maximum efficiency.

Yes.

Depending on how you want to display it you could use DSum for a text box - put the following in the controlsouce for an unbound textbox:

=DSum("[weight]/14000","[Theta Inputs]","[Disc Number]=" & txtDiscno)

where txtdiscno refers to a control on your form - plus modify the critiria to include your date filter

or you can just use the query as a rowsource to a listbox, combobox or subform - again modify the criteria as required