VB Question

[WayneRyan]

Hi Andy,

I think that introducing the Sites is a
logical next step, but you should now
change your table structures.

I've lost a little bit of familiarity with
this, but here's my take on it. We started
with a specific coding problem, more code
is being introduced now, because of the
limitations of the table definitions.

I see a need to identify the specific
entities in this app. It was OK, going
through the logic of packing the truck.
But now we are wanting to incorporate a
group of sites that will participate in
the process.

To avoid generating a lot of code to make
this happen I suggest we look at the tables
involved. If the data is laid out properly
then the status of a site, truck or product
is easy to ascertain with a single query.

The vehicle loading is unique and I see this
as the only place that there is a lot of
coding logic. The history of shipments,
site and product status can all be done with
various queries. Most of the complex coding
has been done.

You have a finite amount of products and they
all require a certain amount of shipping space.

tblProducts:
ProductID
ProductName
ProductDescription
Product"cubes" - Shipping space required

You have several sites that store the product.

tblSites:
SiteID
SiteName
SiteManager

You will ship from one of those sites.

tblShipping:
ShippingID
SiteID
ShipmentNumber
DateShipped

Each product being shipped is on one or more
trucks. This is where most of our earlier
efforts centered, but it kept no history.

tblShippingDetail:
ShippingDetailID
ShippingID
TruckNumber
ProductID
StartRow
EndRow
QuantityShipped

When you receive the product(s), they go to
a particular site:

tblReceiving:
ReceivingID
ReceivingNumber
SiteID
Source
DateReceived

tblReceivingDetail:
ReceivingID
ProductID
QuantityReceived

With the amount of time we've spent here, it'd
sure be nice to envision this as the complete
authority on your shipping environment.

***********************************

As to the last post:

If you table the data right, then when you
want to calculate the OnHand qty (and thus
where to ship from, you should be able to do
TOP 1 SiteID, (receipts - shipping) for any
product.

In code, the worst it would be is the difference
of a couple of DSum calls.

As it is now, you will need a lot of Case and
If -- ElseIf statements from having too many
columns for sites when they really should be rows.

***********************************

Just some thoughts,
Wayne

 

[Cosmos75]

Busy, busy!

spacepro & WayneRyan,
MY! MY!, you two have been BUSY! Will take me forever to get back into it again!

 

[spacepro]

Sucess!!

Cosmos,

Welcome Back...

Bet there are a few keywords here....

Problem Solved no thanks to you and wayne, working on the data import now and the db structure.

By doing this has reduced the time taken from 3-4 hours to do the exercise to 2-3 seconds by pressing a command button.

Excellent

Cheers Guys

Andy

 

[Cosmos75]

Re: Sucess!!

Problem Solved no thanks to you and wayne, working on the data import now and the db structure.

Was that supposed to be "Problem Solved now, Thanks to you and wayne, working on the data import now and the db structure."


IF not ->

(Just kidding! Not quite sure if we , or at least if I, did help. Looks like WayneRyan got you up and running!))

 

[spacepro]

Cosmos,

Yes it is me typing to fast. And Yes you did help me so much the code that I have running is your code which I have adapted it using some of wayne's code, combined together. Job Done.

Many Thanks


Andy

 

[spacepro]

Hi Guys!!

I am know looking at the products which can be put on top of one another.

Basically there are about four products that will go onto only four other products.

I want to match the qty's and set the rows value of the products which go on top to 0 which will assign them to the same vehicle.

For Example:

Product A,B,C,D are the products that sit on the floor.
Product E,F,G,H are the products on top of Products A,B,C,D.

So if qty of Product E,F,G,H matches products A,B,C,D then assign to that vehicle, this is to be done for each site.

Or better still to cycle through the recordset and add new records so that the qty's for them products match.

Bit unsure how to do this.

I am assuming a Select Case statement but don't know where to start.

If you guys can give me a pointer I am sure I can write the code to cycle the recordset as per code in previous posts.

If not I am still trying to figure it out. I know I have already asked to much of you.

Anyway Thanks in advance

Andy

 

[Cosmos75]

Re: Hi Guys!!

Basically there are about four products that will go onto only four other products.
...
For Example:

Product A,B,C,D are the products that sit on the floor.
Product E,F,G,H are the products on top of Products A,B,C,D.

Is it the case that

a) A,B,C,D - sit on floor & E,F,G,H are on top of product sitting on floor?
- i.e. Doesn't matter as long as A-D are on top and E-F are on the bottom.

OR

b) A must go on top of E only,
B must go on top of F only,
C must go on top of G only,
D must go on top of H only.
- i.e. There are certain rules as to what products (A-D) must sit on on top of the bottom products (E-F)

If it is a, then it shouldn't matter how things get assigned, you can just add a field tied to the product that designates it as a product that must be stacked on the bottom or the top. (At least insofar as I have thought this through)

If it is b, then I will have to think about that one some more.

 

[spacepro]

Cosmos,

Thanks for the reply, I know you are busy at the moment.

Have a look at this thread, that i recently posted may highlight my issue a bit.

http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=60898

Basically products A to D will sit on the floor and products E to H will go on top, it doesn't matter which of e-H sits on top.

Hope this helps

Andy

 

[Cosmos75]

Thought it through some more and I see the folly in my last post. Leaving it as is could produce a load that has 7 E, 7 A & 7 B which would cause either A to be loaded on top of B or vice versa.

Will need to think some more...

 

[spacepro]

Thanks Cosmos,

I look forward to your thoughts.

regards

Andy

 

[Cosmos75]

Andy,

I think you will need to really think this through and give us ALL the details. Lotsa of potential problems and scenarios.

Here what we do know (or think we do at least);
1) A row in a truck is limited to 21 units.
2) E-H go on top if A-D (I had this backwards in my earlier post)
3) You have 21 Units in a truck (stackable).

What we don't know;
- Since things can go on top of one another, I assume that a row made up of 21 stackable units (e.g 7 *3=21)
- What happens if your order is 24 A + 3 B? Can they all go into one row regardless of what is stacked on top of another?
- Are there certain restrictions or rules governing what can be stacked on top of what?

I am hoping that the rules are as follows
- Products E-H must never be stacked underneath products A-D
- Products A-D can be stacked on top of each other, likewise for E-H.

It might also be helpfull to know how those stackable units are arranged in a truck
- 7 units * 3 units (height)?
- 2 units (width) * 4 (length) * 2 (Height) gives 16 + and extra 5 on top makes 21?
We may have to instead of just saying the in 21 stackable units is made up of 10 A, 7 E and 4 H need to figure out what goes where in order to validate any rules.

Again, this is just my attempt to clarify what you are trying to do before I spend time trying to figure oput how to do it. Probably also a good exercise to run through for yourself to really follow through on your loading logic to test for any occasion it might fail to work as you want it to. Figure out procedurally what need to be accomplished. There's the saying I've read here somehere.

If you can't do it on paper, you can't do it in Access.

So, take a compliacted order and take it through the decision process (flowchart works best here) you want the database to take and the results and see if you have thought of everything in your decision logic.

I know this isn't much help but I can't help till I understand the entire process of loading. Doing it bit by bit may result is getting 25% to work correctly only to have to go back to the drawing board to get the other 75% when more details are known.

Cheers!

 

[spacepro]

Hi Cosmos,

Thank you for taking the time to explain.

I have done this exercise and the way we have written the code works excellent. Basically the same rules apply. The product cube is working on by how many products in a row on the vehicle. The only differrence being that four of the products are small units that could fit on top of another four products.

I currently have a yes/no field called Topped in my products table, which defines these four products.

I want to cycle through the records and do the same check as we have done in the code to make sure the products fit before we assign the truck.

For each Site if any of these 4 products appear for that site and there are the other four products on the same site then it sets the rows vaalue to 0, so that it maximises the vehicle and doesn't take into account the rows value for these four products.

But the qty's need to match then add new records for balance of qty if needed.

I can post an example if you like explaining it.

I understand what your saying, I'm sure it's a case of writing a select case using DAO but because I have only started with VBA and DAo I am struggling abit with the terminology of setting variables to return the results I require.

Please bear me with.

Let me know if I have clarified this any clearly and if you want me to post the example.

Just to clarify:

21 rows on a vehicle.
No of products in row depends on productcube

Most products are stacked, which is how the productcube is worked, but I need to do this on two different products, these are the only four products that need to be stacked now or in the forseeable future.


Thanks Cosmos

Andy

 

[Cosmos75]

Andy,
I reread your other post and took a closer look at it. You said

Currently the records will display as follows:

Purchase Order  Product   Qty      Site       LoadNumber  Rows

12345           A         12       London     1           4
12345           B         100      London     1           17
12345           E         88       London     2           13

Now product A + B will be standing on the floor and product E will go on top. I want to assign the record with product E to Loadnumber 1, because they will go on top of product B.

So, if I understand correctly, each truck have 21 rows and these 21 rows are stacked one on top of another resulting in the possiblility of E being stacked on top of A & B therefore causing a viloation of the rule;
Products E-H must never be stacked underneath products A-D

Correct?

If so, how many rows can be stacked in a truck?

I currently have a yes/no field called Topped in my products table, which defines these four products.

Good idea.

I can post an example if you like explaining it.

Another good idea.

Please bear me with.

Will try.

Just to clarify:

21 rows on a vehicle.
No of products in row depends on productcube

Most products are stacked, which is how the productcube is worked, but I need to do this on two different products, these are the only four products that need to be stacked now or in the forseeable future.

I think we need to go through an example. Step by step
e.g. Orders = 30A, 40B, 16E. Then take 30A -21A = 9A. 9A goes to a new record resulting in 21A, 40B, 16E, 9A. After that .....

A simple way to show how things need to be stacked is like this

T = Top
B = Bottom

T - EBAAA
B - EEEAA

We can have row assignments that correspond to this.
1 - 1E, 1B, 3A
2- 3E, 2A

But then we can potentially have them stacked as so
T - BAAAE
B - EEEAA
Same number of products in each row but not stacked properly. So it looks to me like we not only need to assign products to rows properly (limted to 21 in a row). BUT!, also need to assign the position of how things are stacked. Unless we can come up with a way to ensure that all the products that belong on the bottom get assigned first and use the order of rows assigned as the first ones to be put in the truck -> thereby making sure that they are on the bottom.

So I see two possible ways of attacking this
- Assign rows and position of stacking (Harder?)
- Assign rows first to products that need to be on the bottom first, and then only assign rows to those that need to be on top.(Easier?)

What I gather so far is that
- A truck has 21 rows
- A truck can stack one row on top of another.
So, now we need to determine how many rows can be stacked one on top of the other? e.g. 1 truck has 3 stacks of 21 rows.
Is the number of stackable rows the same for every truck?
Could it change in the future?
If it can change, will it be different for all trucks or will it depend of each individual truck?
Are any rows stacked side by side in one truck? e.g. 1 truck has 3 stacks of 21 rows. Each truck has three of these stacked side by side.
- Products E-H must never be stacked underneath products A-D.

Remember, the devil is in the details!

 

[Cosmos75]

Just another thought & clarification

When I say row, I mean lane as it was originaly started out in this thread.

If 21 can't be met 20 or 19 is OK but much be round up to the nearest number.

Example:

Record 1 - No of Lanes = 9.22222
Record 2 - No of Lanes = 14.11112
Record 3 - No of Lanes = 12.333328
Record 4 - No of Lanes = 8.7888954

When you saw you have 21 in a "row"/"lane", is that 21 units in a line or 3 units by 7 units in one "row"/"lane"?

e.g. (As seen from above)
one row/lane = 1 Row of 21 "items"
1,2,3,4,5,6,7,...,18,19,20,21
OR
one row/lane = 3 Rows of 7 "items"
1,2,3,4,5,6,7
8,9,10,11,12,13,14
15,16,17,18,19,20,21

 

[spacepro]

Hi Cosmos,

Right the vehicle has 21 lanes effectively these are rows in the vehicle that are approx 2ft apart.

Each product's product cube is calculated based on how many of that product can fit into a lane(row). So for example :

Product Code    Description        Cube

AAAAAA             Fridge                   4
BBBBBB             Fridge                    4
CCCCCC           Cooker                   8
DDDDDD       Washing Machine       6

EEEEEE            Small Fridge             8
FFFFFFF            Dishwasher              6

Cosmos,
Fridges tend to stand upright with 4 in a lane(row).
Cookers has 8 in a lane(row) this is because 4 on the floor and 4 on top.

Washing Machines are the same 3 on the floor in a lane(row) and 3 on top.

Small fridges are 8 in a lane, but what I want to do is when the qty of small fridges and fridges match then 4 of the small fridges go on top of the fridges, which will maximise the vehicle.

As far as the rest of the products go the space is maximised it is just with the small fridges that I want to put on top of the fridges.

If there are more small fridges than fridges then they will go 8 in a row(lane).

I know what your saying but when I planned the db I took into account the best way to calculate the vehicle fill by product. Rather than using a cubic measure I deicided on what physically will fit in the hole and not what you can get in using cubic measures. As i know from experience cubic measures only work if they are calculated properly, and there are so many if's with cubic measure, and I know that the defined product cube I have set up in the db is correct and unlikely to change.

So it is only the small fridges that I need to put on top of the fridges.

There are about 4/5 products that are small fridges and
about 4/5/6 products that are fridges.

Does this explanation help?

Andy

 

[Cosmos75]

Andy,

I am afraid that I must admit that I am more confused that I was before.


I suggest we revamp our terminology for the sake of clarity and to use language that will be easier to follow (hopefully).

So, we have some established rules.

A vehicle has 21 lanes effectively these are rows in the vehicle that are approx 2ft apart

1) A truck is made up of 21 lanes/rows. Using these terms gets confusing (see quote above). So let’s use the word space.

So it is only the small fridges that I need to put on top of the fridges.

2) So the stacking rule can be summaried to be "Small products can be stacked on top either Small or Big Products. Big Products cannot be stacked."
So a space can be occupied by one of four possibilities
-- 1 Small item
-- 2 Small items (one stacked on top of another)
-- 1 Big item
-- 1 Big item and 1 Small item (Small item stacked on top of the Big item)
The only remaining option is empty

Is 1) and 2) correct? If not, please try to just state what the rule(s) should be.

Let's assume for now that I am correct, shall we?
Following the logic of 2), the maximum a truck can hold is;
a) To max out Big items on a truck, the load must be
--> 21 Big items + 21 Small Items (stacked on top of Big items)
b) To max out Small items, the load must be
--> 42 Small Items (21 small stacked on top of 21 small items on the bottom)
- Is this correct?

Let me give you some specific examples.

Scenario 1
Order is 20 Big items and 22 Small items.
- 1 space is occupied by 2 Small items (one stacked on top of another)
- Remaining 20 spaces are accupied by 20 Big items and 20 small items. Each space is made up of 1 Big item (on the bottom) and 1 small item (stacked on top the Big item)

Scenario 2
Order is 16 Big items and 25 Small items.
- 16 spaces are occupied by 16 Big items and 16 small items. Each space is made up of 1 Big item (on the bottom) and 1 small item (stacked on top the Big item)
- 4 spaces are occupied by 8 Small items (one stacked on top of another)
- 1 space is occupied by 1 Small item

Are both Scenario 1 & 2, correct? (fingers crossed)

If so, then the way to maximize usage of spaces' in a truck is to
- First, load all the Big items!
- Second, load as many of the small items on top of the Big items!
- Third, divide the remaing small items by 2 and round up to nearest integer. This will give us the number of spaces needed to load the remaining items which should all be Small items.

Of course, If I got any of the rules wrong, then it's back to the drawing board!!

 

[spacepro]

COsmos,

That's about right! This is roughly the way we should do it.

Does this mean all of the code needs to be re-written?

If you can give me some pointers I will have a go at writing the code to do this.

Many Thanks

Andy

 

[Cosmos75]

That's about right! This is roughly the way we should do it.

Andy,

Close but no cigar? What would make it totally correct? The reason I hesitate to move on to figuring code is because if out logic of what and how the code should work is flawed then our code will also be flawed.

 

[spacepro]

Cosmos,

Sorry It's exactly right. Your Smoking Now!!

Your thoughts

Andy

 

[Cosmos75]

I hope this is what you want...

OK, here is what I have. Play with it. I hope it does what you are looking for. You'll probably have to adapt it for your database design.

I know that there are some lines that are redundant and that I probably need to clean it up some more but who knows when I'll be able to get to that so I thought I should post what I have so far.

If there are any mistakes, let me know. Proabably mostly from forgeting to remove some variables or small silly mistakes (It is past 3 am here..), I did find a few and corrected them as I am writing now.

Me.Tired = True
Now() = 3:09 a.m.
Me.NeedSleep = True
:(

GOOD NIGHT!
zzzzzzzzzzzzzzzzzzzzz