Thread: Help with Mathematical Calculations

1. Registered User
Join Date
Jan 2005
Posts
144

Unanswered: Help with Mathematical Calculations

Given a set of containers (each a specific size), I'm trying to determine whether or not a set of numbers will "fit" into the containers. To complicate matters, the total of the numbers provided must fall between two predetermined limits. It's not easy to explain, so let me provide an example.

The standard container sizes are 3100, 1200, 1100, 1100, and 2800. The lower limiting value is 8500 and the upper limiting value is 8800. (The total of the provided numbers must be equal to or greater than 8500 and equal to or less than 8800) Given the numbers 5600, 1200, and 2000 the result should return "True" - meaning that the numbers will fit into the containers. The breakdown would be as follows:

5600 - Would fit in the 3100 and 2800 containers. This is due to the fact that the total of these containers is 5900, so they could in fact hold any number up to and including 5900 between the two of them.

1200 - Would fit in the 1200 container by itself and does not require being split with any other container.

2000 - Would fit in the two 1100 containers which have a combined total value of 2200 or less. The 2000 would fit between them and still leave room for 200, or 100 in each depending on how you look at it.

The "True" value would be returned because the numbers correctly fit into the containers and because the total of the numbers provided is 8800. 8800 satisfies both the lower and upper limit requirements. It's greater than 8500 and equal to or less than 8800.

Now if the numbers provided were something like 7400, 1000, and 400 the resulting value would be "False" as these numbers would not fit into the containers. The 7400 value would occupy the 3100, 1200, 1100, and 2800 containers leaving only the second 1100 container available. This 1100 container would not accomodate the 1000 and 400 values. It would actually only be able to hold one of them. Even though the total value of the numbers is 8800, which meets the limit requirements, they still fail the "container fit" test. Hopefully this makes sense.

And for those of you interested in what all of this insanity is about, it has to do with loading gasoline tankers. I'm a dispatcher for a gasoline company and I'm designing a program to assist in training new hires. The tankers have compartments that hold 3100, 1200, 1100, 1100, and 2800 gallons each. Different products are loaded into the compartments based on the needs of gas stations and customer orders. So if a customer requests 5900 gallons of regular gas, 1200 gallons of plus, and 1700 gallons of diesel - it would load as follows:

5900 Regular - 3100 and 2800 Compartments
1200 Plus - 1200 Compartment
1700 Diesel - 1100 and 1100 Compartments

The values would fit the containers and the total of 8800 gallons would satisfy the limit requirements. Hopefully all of this makes sense. I've been jotting down various ideas but haven't really found the right approach yet. Most of mine are very long (not unlike this post), complicated, and time consuming. Any ideas or suggestions would be appreciated.

2. Registered User
Join Date
Apr 2004
Location
outside the rim
Posts
1,011
This is definately going to be a little easier with code. I'm thinking a two dimensional array and a series of select clauses to work through the logic.

Too bad Access doesn't let you create recordsets in memory - that would be perfect.

The logic would closely follow how you explained it in your examples.

What I don't get is the limiting values: if I fill each container to capacity, I can hold 9300 gallons - or is that too much for the truck to haul? And why the minimum value - not efficient to haul less than 8500?

3. Registered User
Join Date
Jan 2005
Posts
144
The "Minimum" load limit of 8500 gallons is a nickle and dime matter. It's simply not profitable to haul anything under 8500 gallons. As for the "Maximum" load limit of 8800, this is a matter of weight. Gasoline has a specific weight and per DOT regulations, you can only haul but so much weight over US roadways. The weight of 8800 gallons of gasoline is quite close to the maximum federal DOT limit.

Just food for thought, the maximum load for gasoline is 8800 gallons, but the maximum load for diesel is 7500 gallons. Quite simply, diesel weighs more than gas by volume.

4. Registered User
Join Date
Apr 2004
Location
outside the rim
Posts
1,011
ok, I think i got it.

First, you need a table with all the possible combinations of sections and the corresponding volume (for 5 sections, this equates to 32 possiblities, but really only 31 because one of the possibilities is "all empty"). Sort this by total volume, smallest to largest. We'll call these the fill values. Each record on my fill value table contains 5 booleans (1 for each tank) showing which tanks combine to form that fill value. The code to populate this table is below - it's not pretty, but does the trick. You only need to run it once (unless you change your tanker size).

Then, you sort the inquiry largest to smallest. We'll call these the load values.

Starting with the first load value (the biggest), work your way up the above table (smallest to largest) and find the first fill value big enough to acocmodate your load value. On that line is the sections used (marked by the true's) - use that to go to your bucket table and mark each used bucket.

Next, take you second load value and repeat the process, only this time, when you find the first fill value large enough, check the bucket numbers on this table against the ones already used. If any of the buckets from the fill table match a bucket already used, it's no go, keep going up the list.

If you make it to the top of the fill list without finding a winning combination, then it fails the fill test.

If you find a winning combination of availble buckets, repeat for the next load value.

If all load values find a winning combination, then it works - return a "True".

This takes the largest load size and finds the most efficient way to split it, then moves on to the next largest value and so forth.

Obviously, you check the global requirements first: min and max load.

Now, to incorporate the difference between diesal and regular, you'll need a weight per gallon factor for each and as part of the global weight check, differentiate what type each each fill quantity is, multiply out the actual weight and check that against the limits.

If you decide to try this out, let me know how it works. Until then, I'm having another glass of wine.

Good Luck!!!

The fill table has a key field, 5 booleans and then an integer. The bucket table has a key (bucket number), an integer (bucket volume) and a boolean (open).

this code takes the bucket table and populates the fill table:
Code:
```Private Sub Command0_Click()

Dim rsDt As DAO.Recordset
Dim rsdat As DAO.Recordset

' The boolean loops
Dim iA As Integer
Dim iB As Integer
Dim iC As Integer
Dim iD As Integer
Dim iE As Integer

' The bucket values
Dim iV(1 To 5) As Integer

' Counter
Dim intC As Integer

' Total volume
Dim iT As Integer

' boolean array
Dim iX(1 To 5) As Integer

Set rsDt = CurrentDb.OpenRecordset("Select * From tblBuckets")
For intC = 1 To 5
iV(intC) = rsDt(1)
rsDt.MoveNext
Next
Set rsdat = CurrentDb.OpenRecordset("Select * from tblFills")

For iA = 1 To 2
For iB = 1 To 2
For iC = 1 To 2
For iD = 1 To 2
For iE = 1 To 2
iX(1) = iA
iX(2) = iB
iX(3) = iC
iX(4) = iD
iX(5) = iE
iT = 0
For intC = 1 To 5
If iX(intC) = 1 Then
rsdat(intC) = True
iT = iT + iV(intC)
Else
rsdat(intC) = False
End If
Next
rsdat("Volume") = iT
rsdat.Update
Next
Next
Next
Next
Next

Set rsDt = Nothing
Set rsdat = Nothing
End Sub```

5. Registered User
Join Date
Jan 2005
Posts
144
I will take a look at this tonight. I see a lot of potential here.

6. Registered User
Join Date
Jan 2005
Posts
144
Ok, I created the two tables - tblBuckets (for compartment numbers/corresponding volumes) and tblFills (for all possible combinations of compartments and the resulting volumes). I understand the logic behind what we're trying to do here: Take the list of attempted load volumes, sort highest to lowest volume, identify the appropriate compartment combination (from tblFills) and once done, set the necessary compartments to "Filled" via the Yes/No boolean in tblBuckets. Continue through the list of attempted load volumes and so long as we don't run into compartments that are already full in tblBuckets, the load will fit successfully.

I have been battling Access now for about 3 hours and I have come to a few dead ends. The logic is well... logical and therefore, I'm on the same page. What's getting me right now is the sequence of operations and coding. Here is what I've come up with:

1] I'd like the user to enter the attempted load values via unbound combo/text boxes on a form. I was planning on using the combo boxes to identify the product (unleaded, premium, diesel, kerosene, etc) and the text boxes to enter the gallons. After trying some things with tables and queries and a lot of trial and error, I'm right back to where I started. I have been unable to find a way to take the users unbound input and sort it (just the gallons mind you) in a descending order and still have it in a workable condition. I figured a query would be the way to go, but I couldn't get the unbound gallons into the query. I tried using some table/query combinations, but to no avail.

2] Once the gallons have been entered into the unbound controls and successfully sorted highest to lowest, how do I compare them against tblFills to identify the proper combination of compartments? Further more, after I have identified which compartments have been filled, how do I "check" the Yes boxes within tblBuckets to show that the compartment is occupied for subsequent comparison. I'm not very familiar with direct interaction with tables and fields. My approach would have been something along the lines of adding the boolean controls to a form, checking them on/off there, and then updating the record to save it back to the table. This is probably outlandish compared to the proper method, not to mention clunky and probably very much more time consuming.
Last edited by DaVinci; 04-08-06 at 06:42.

7. Registered User
Join Date
Jan 2005
Posts
144
Any suggestions?

8. Registered User
Join Date
Apr 2004
Location
outside the rim
Posts
1,011
I'll take a look. At first glance:

Instead of unbound controls, setup a table to hold the user's input. That way, you can use a query to do the sorting and remembering (the table will have a yes/no field to store if that quantity has been handled).

With this table, once you get the gallons working, you then can then add another field to the table to mark what type of fuel it is. You'll use this filed in your query to calculate a weight (the gallons multiplied by the density, which you can store on a seperate table).

You can put the code in a second form. Have the user enter the data on the form and when they are ready, they press a button that opens a second form. The second form does all the work and displays the result. (This part is just preference).

Do you know how to use recordsets? That will make this a lot easier.

tc

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•