VB Question

[spacepro]

Cosmos,

WOW!!

You have put so much effort into this, I appreciate this very much.
I have a quick look and it looks OK, you know your stuff. I will have a look and adapt it to my db, maybe I will change the design to include the way you have had a go at this.

Once I have filtered it into my db I will let you know of the final result and if I have any questions I will post, hopefully though I am going to look at the code and try to understand what the code does, thus learning myself.

If there is anything I can do in the future, you know where I am.

Anyway Thanks for this, especially for stopping up to 3am. Get yourself to bed and I speak to you soon.

Cheers

Andy

 

[WayneRyan]

Andy & Cosmos,

I saw that this thread was revived, but I've been on a
crash course learning SQL Server during the last couple
of weeks.

Wow, Cosmos! That looks good, I had always hoped that
this would be able to track the history, etc. Lot of
work, you did a good job.

Well at least I ordered a couple of products. Glad to
help in any small way.

Is the thread over yet?

Wayne

 

[spacepro]

Andy & Cosmos,
Is the thread over yet?
Wayne

Afraid Not!!

Wayne, I agree with you that Cosmos has done an excellent job.

After looking at the db , the structure and it's code, I am still a little puzzled, but I am beginning to understand the reasoning behind the code.

To carry out the design structure that cosmos has done, it raising a couple of questions:

1) How would I import data from excel into the database,Would I need to import into a new table then using append/update queries to assign the values imported to the relevant tables/records?.

I know how to import into a table but not a table with related tables.

2) Because there is no data entry, it is purely imported in from excel, I would need to change the table structure a little to accommodate when the build loads button is pressed it should assign all orders at once.

The db certainly gives me a lot to think about and how to implement the structure to the db.

If you guys can give me any advise on the questions above, I would be most grateful.

Cosmos the db is really good. Many Thanks

Andy

 

[Cosmos75]

I saw that this thread was revived, but I've been on a crash course learning SQL Server during the last couple of weeks.

Cool! How is that going? I am interested in learning SQL Server as well. (But to be honest, I think I should at least master Access first! )

Wow, Cosmos! That looks good, I had always hoped that this would be able to track the history, etc. Lot of work, you did a good job.
Originally posted by spacepro
Wayne, I agree with you that Cosmos has done an excellent job.

Aw, shucks... Thank you!

There is more that I would like to add but I just don't have the time to do it. At least the logic for loading trucks is in the code. I probably didn't comment the code as good as I could have but a lot of the changes happened in the last hour or so and I was too tired to insert comments - was concentrating on getting it to work.

Well at least I ordered a couple of products. Glad to help in any small way.

Is the thread over yet?

Originally posted by spacepro
Afraid Not!!

1) How would I import data from excel into the database,Would I need to import into a new table then using append/update queries to assign the values imported to the relevant tables/records?.

I know how to import into a table but not a table with related tables.

I've never done that so I can't help you there. I have asked about this before but there may have been newer threads with more guidance. Did a quick search. Try this If not, then try a different search

2) Because there is no data entry, it is purely imported in from excel, I would need to change the table structure a little to accommodate when the build loads button is pressed it should assign all orders at once.

Does it HAVE to be imported from Excel? Why not do all the ordering in Access?

As to assigning all orders at once, I had considered incorporating that but figured it was too much coding right now.

My idea would have been to add a True/False field to the order table to designate if it was a New or Old order. Then if it was a New order, then use the logic in the code to build the loads for all Orders that were New. Once a build was complete, the New/Old field would automatically be changed to Old.

 

[spacepro]

Does it HAVE to be imported from Excel?

Afraid so Cosmos. Basically we have a customer, they have 18 locations and they send three spreadsheets over with what each site wants. We then get the stock in the right place and it's a case of assigning vehicles to each site.

So the data is just imported in, which is why I went with the original structure(not ideal) but becuase I didn't now how to import into related tables I did it this way, as I needed to do something quick as the process was taking several hours to complete.

Why not do all the ordering in Access?

Don't think this will happen! unfortunately.

Cosmos, the DB is excellent. I am hoping to set a meeting up with the customer and talk about the possibility of using a db their end then we can just use the data already in the db to assign the vehicles. Thus implementing your design of db.
This probably is being unrealistic for a while, but hopefully one day!

In the meantime I need to figure it out , with thanks to the incredible support from yourself and wayne.

What I also thought was to combine my structure and yours, as a design compromise, say using the structure I have then when the vehicles are assigned then the data gets put into tables that are related. So full traceability upon assignment.

Cosmos/Wayne I can send you a link to the db I currently use to give you an idea of the design if you like.
Any ideas or advice?

Andy

 

[spacepro]

Cosmos,

Right I had a rethink.

I am going to manupilate the data in excel, which means I will need to re-write the code in Excel to import directly into the tables, had help from Mile-O.

If your interested the post is in the General forum or click here .



Is there a way to run the code to assign the loads through a module, basically run through all orders that have not been assigned.?

Any ideas, as I am a bit baffled by some of the code. Just wondered? Anyway I going to have a think about how I can do this, if you can help...Excellent.

Cosmos ......DB is EXCELLENT!!!!

Thanks...Thanks..Thanks

Andy

 

[Cosmos75]

Basically we have a customer, they have 18 locations and they send three spreadsheets over with what each site wants.

Could you post an example of the spreadsheet either here or on the other post? Can't promise that I'll have time to look at it but I am curious.

 

[spacepro]

Could you post an example of the spreadsheet either here or on the other post? Can't promise that I'll have time to look at it but I am curious.

Cosmos,

Do you want an example of the new spreadsheet that I have created? As i am re-writing the code to assign the ID's to import straight into you database. It works excellently.

The data goes straight in , no problems after inital testing, and the db is spot on.

I just have a couple of questions if you could point me in the right direction. Sorry to keep dragging this on

Q1: How difficult would it be to assign all of the loads at once, am I looking at your previous suggestion of a field in the table for old/new record, just wondering how i would progress through the records to assign the loads.

And

Q2:

What I am thinking is that we are currently taking on a new business contract where the vehicles are different sizes.

The db that you wrote is for our primary contract, one size of vehicle, but the new contract are different sized vehicles, now I looking at the design and integrating another table:

tblVehicle

VehicleID
ClientID
VehicleType
VehicleCapacity

This table would be related to the Client table.

And then running the code initally based on the client??, then assigning the big and small stuff. So I am looking at this at the moment and trying to generate generic vba code or seperate code for each client, based on the code you have supplied.

I know this goes again the grain and the rules, and you have done an excellent job on the db, which has helped me out so much. I can't thank you enough.

If you can shed any hint/suggestions on my approach to this completely new and huge task that I have been given..Thanks

Just thought I would give you an overview has to where we are going with this.

Hopefully towards the end of this thread

Andy

 

[Cosmos75]

Q1: How difficult would it be to assign all of the loads at once, am I looking at your previous suggestion of a field in the table for old/new record, just wondering how i would progress through the records to assign the loads.

Not too difficult (if you understand how the code works), just time comsuming for me to add right now.

Here what I had thought to add but didn’t have the time to. Here was my plan of attack, feel free to try to add this yourself;
- Add a field to the tblOrders, let's call it New (Yes/No field) and set the default value to Yes. This way every new order entered via the database is a "New" order.
- Then create a query that selects all the "New" orders, call it qryOrdersNEW.
- Then you just have to loop through the existing code for each value of OrderID in this new query.
- The queries that select the Big/Small items order details will have to become parameter queries with the parameters set from code to select on OrderID
- After assigning truck space for an individual OrderID, set the value of “New” to No

Q2: What I am thinking is that we are currently taking on a new business contract where the vehicles are different sizes.

This can be accomplished if you change the code. Instead of using the number 21 in the code. Change the number 21 in the code (where appropriate – should be everywhere 21 occurs) to a variable (e.g. TruckSize). And then the size of the truck could then be a field in tblClient since it sounds like each client has their own size of trucks. Use the same query that pulls the "New" Orders to also grab the TruckSize.

The db that you wrote is for our primary contract, one size of vehicle, but the new contract are different sized vehicles, now I looking at the design and integrating another table:

But, what happens if a client has a mix of truck sizes? Not just a mix, but how about having only a certain number of each size available? As you can see, the more variables and limitations you add, the more tables, queries, lines of code need to be added to handle all the possibilities.

Lotsa of possibilities;
- What is clients share trucks?
- What if certain trucks can only go certain places and not others?
- What is a truck has the space but can only carry up to a certain weight? 21 of Fridge A might be OK, but 21 of Fridge B might not?

Incidentally, I had wondered if it was possible to have a table to store individual trucks, each with their own
- size
- marked as available or not available
- also somehow used to not schedule the truck more than once in a day. Unless it could carry two orders (or parts of them) for two different locations that were in the same directions (Very ambitious but since it was jsut a mental excercise)
That may or may not work here. Obviously, I ddin't add that as I did not know if that was a possibility based on all the previous posts.

 

[spacepro]

Lotsa of possibilities;
- What is clients share trucks?
- What if certain trucks can only go certain places and not others?
- What is a truck has the space but can only carry up to a certain weight? 21 of Fridge A might be OK, but 21 of Fridge B might not?

Incidentally, I had wondered if it was possible to have a table to store individual trucks, each with their own
- size
- marked as available or not available
- also somehow used to not schedule the truck more than once in a day. Unless it could carry two orders (or parts of them) for two different locations that were in the same directions (Very ambitious but since it was jsut a mental excercise)
That may or may not work here. Obviously, I ddin't add that as I did not know if that was a possibility based on all the previous posts.

Cosmos,

Thanks for the feedback. Some of the questions you have said, I have thought about it, and it's great that I am thinking down the same lines. I am going to have a think and get the wipeboard out and draw a process and scenario exercise to answer some of the questions and the way forward for the design.

Currently I am going to adapt your db to mine and have changed the spreadsheets to accomodate the import and this will be such a excellent step forward for our primary contract. I cannot thank you enough.

Anyway once I have a structure tree devised, I am going to spend some time in understanding fully the code and attempt to make any additions. I will only learn if I try..

I am going to spend some time on this now, as I develop this project I will keep you informed on the progress of it, if you like , has you have put so much into it.

If ever I can help....



Andy

 

[Cosmos75]

Cosmos,

Thanks for the feedback. Some of the questions you have said, I have thought about it, and it's great that I am thinking down the same lines. I am going to have a think and get the wipeboard out and draw a process and scenario exercise to answer some of the questions and the way forward for the design.

Great idea! It should be interesting to see what you would like the db to do.

Anyway once I have a structure tree devised, I am going to spend some time in understanding fully the code and attempt to make any additions. I will only learn if I try..

I don't have time to go back and add complete comments to the code (which I really should have done while writing the code. My bad!). But I think if you step the the code line by line and look at how the variables are changing you will get an idea of what it is doing. If not, feel free to post specific questions regarding the code (e.g. what is this lilne for?) and I will try to find time to answer them.

 

[spacepro]

Thanks!

Thanks Cosmos, appreciate your help.



Andy

 

[Cosmos75]

I've had to remove some lines of the code to make it fit in one poast but this should help you on your way to understanding the code. If I (or WayneRyan or SpacePro) find any mistakes I will just modifiy this post.

[color=green]'BIG Items Procedure starts here
'[b].RecordCount[/b] will [u]not[/u] give you an accurate count
'of records in your RecordSet UNLESS you use [b].MoveLast[/b] 
'to go to the last record in your RecordSet
'.RecordCount gives you the number of the [b]current[/b] record you are on.
'So if there are no records it will return a value of [b]0[/b].
[/color]
NumOfRecBig = rstDataBig.RecordCount

[color=green]'Check if there are any BIG items to load[/color]
If NumOfRecBig > 0 Then

    rstDataBig.MoveFirst

    TruckNum = 1

[color=green]    'Start assinging truck space for BIG items[/color]
    For iBig = 1 To NumOfRecBig

[color=green]        'Get information of currect Order record[/color]
        ProductID = rstDataBig.Fields("ProductID")
        ProductTypeID = rstDataBig.Fields("ProductTypeID")
        ProductAmount = rstDataBig.Fields("ProductAmount")

[color=green]        'Loop though loading process for current Order Detail
        'while there are still items to load[/color]
        Do While ProductAmount > 0
[color=green]            'Do Big Loads
            'Count Current Truck's Load'
            '(Use to determine next product loading)[/color]
            strSQLBig = "[color=red][b]...[/b][/color]"
            Set rstBig = myDB.OpenRecordset(strSQLBig, dbOpenSnapshot)
[color=green]            'Check if no records in tblLoad for current Order[/color]
            BigExists = rstBig.RecordCount
            If BigExists = 0 Then
                CountBIG = 0
            Else
                CountBIG = rstBig.Fields("SumOfProductAmount")
            End If

[color=green]            'Calculate available space on truck[/color]
            CurrentCapacity = 21 - CountBIG

[color=green]            'Calculate what can be loaded onto truck
            'for current Order Detail'[/color]
            If ProductAmount < CurrentCapacity Or CurrentCapacity = 0 Then
                If ProductAmount > 21 Then
                    LoadAmount = 21
                Else
                    LoadAmount = ProductAmount
                End If
            Else
                LoadAmount = CurrentCapacity
            End If

[color=green]            'Add new record to tblLoad[/color]
            With rstLoad
                .AddNew
                [color=red][b]...[/b][/color]
                .Fields("ProductAmount") = LoadAmount
                .Fields("TruckNum") = TruckNum
                .Fields("Bottom") = True [color=green]' Loaded on the bottom row[/color]
                .Update
            End With
[color=green]            'Calculate number of items left after loading[/color]
            ProductAmount = ProductAmount - LoadAmount
[color=green]            'Check if there is anything left to load[/color]
            If ProductAmount > 0 Then
[color=green]                'If so, then increment TruckNum by 1[/color]
                TruckNum = TruckNum + 1
            End If
        Loop

[color=green]       'Move to next record of Order Detail[/color]
        rstDataBig.MoveNext

    Next iBig

End If

[color=green]'Clear rstBig[/color]
Set rstBig = Nothing

[color=green]'Small Items procedure starts here[/color]
NumOfRecSmall = rstDataSmall.RecordCount
If NumOfRecSmall = 0 Then
    GoTo ExitSmall:
Else
    rstDataSmall.MoveLast
    NumOfRecSmall = rstDataSmall.RecordCount
    rstDataSmall.MoveFirst
End If

TruckNum = 1

[color=green]'Start assinging truck space for Small items[/color]
For iSmall = 1 To NumOfRecSmall

[color=green]    'Get info for current Order Detail[/color]
    ProductID = rstDataSmall.Fields("ProductID")
    ProductTypeID = rstDataSmall.Fields("ProductTypeID")
    ProductAmount = rstDataSmall.Fields("ProductAmount")

[color=green]    'Loop while there are still small items to load[/color]
    Do While ProductAmount > 0

[color=green]        'Set query to sum items on Bottom/Top
        'that are currently in tblLoad for current TruckNum[/color]
        Set qdfDataTopBottom = myDB.QueryDefs("qrySumTopBottom")
        qdfDataTopBottom![ctrOrderID] = OrderID
        qdfDataTopBottom![ctrTruckNum] = TruckNum

[color=green]        'Sum items on Bottom of current TruckNum[/color]
        qdfDataTopBottom![ctrBottom] = True
        Set rstDataTopBottom = qdfDataTopBottom.OpenRecordset()
        If rstDataTopBottom.RecordCount = 0 Then
            SumBOTTOM = 0
        Else
            SumBOTTOM = rstDataTopBottom.Fields("SumOfProductAmount")
        End If

[color=green]        'Sum items on Top of current TruckNum[/color]
        qdfDataTopBottom![ctrBottom] = False
        Set rstDataTopBottom = qdfDataTopBottom.OpenRecordset()
        If rstDataTopBottom.RecordCount = 0 Then
            SumTOP = 0
        Else
            SumTOP = rstDataTopBottom.Fields("SumOfProductAmount")
        End If

[color=green]        'Calculate space that is available to load on current TruckNum
        '(Both Top and Bottom)[/color]
        PossibleSMALL = (SumBOTTOM - SumTOP + _
        (2 * (21 - SumBOTTOM)))
[color=green]        'Check is Product that has not yet been loaded
        'is less than available space for currect TruckNum[/color]
        If ProductAmount <= PossibleSMALL Then
[color=green]            'If YES, then load all remaining ProductAmount[/color]
            LoadAmount = ProductAmount
            ProductAmount = 0    [color=green]'Set to zero as all of it will be loaded
            'Check if currect TruckNum will be COMPLETELY full or not[/color]
            If ProductAmount = PossibleSMALL Then
[color=green]                'If YES, then the next Order Detail will need
                'to go on a new truck[/color]
                NewTruck = True
            Else
[color=green]                'If NO, then AT LEAST some Order Detail
                'can go on the currecnt TruckNum[/color]
                NewTruck = False
            End If
        Else
[color=green]            'Set amount to be loaded to equal
            'the amount of available space[/color]
            LoadAmount = PossibleSMALL
[color=green]            'Remainder left to load[/color]
            ProductAmount = ProductAmount - LoadAmount
[color=green]            'A new truck will be needed to load the rest.
            'Don't worry if more than one truck is needed,
            'as the loop will continue while there's still product to load[/color]
            NewTruck = True
        End If

[color=green]        'See what is already loaded on the bottom[/color]
        Select Case SumBOTTOM

[color=green]        'Bottom spaces of truck is FULL
        '(and possibly some on the TOP).
        'The amount that is loaded on top has already
        'been taken into account
        'when we calculated LoadAmount (using PossibleSMALL)[/color]
        Case Is = 21
[color=green]            'Amount of SMALL items to load on TOP of BIG Items[/color]
            Tb = LoadAmount     
[color=green]            'Amount of SMALL items to load on TOP of SMALL Items[/color]
            Ts = 0
[color=green]            'Amount of SMALL items to load on BOTTOM[/color]
            Bs = 0
[color=green]            'There are items to load on TOP[/color]
            LoadTOP = True
[color=green]            'There are NO items to load on BOTTOM[/color]
            LoadBOTTOM = False

[color=green]        'Truck is EMPTY[/color]
        Case Is = 0
[color=green]            'This loading loads as many small items
            'on top of each other as possible.
            '(e.g.21 items - 11 Bottom, 10 Top)[/color]
            Tb = 0
            Ts = Int((LoadAmount) / 2)
            Bs = LoadAmount - Ts
            LoadTOP = True
            LoadBOTTOM = True

            
[color=green]        'Only SOME of the BOTTOM spaces are full
        '(and possibly some on the TOP).
        'The amount that is loaded on top has already
        'been taken into account
        'when we calculated LoadAmount (using PossibleSMALL)
        'I think you only have to use this for ALL cases
        'but I did used the 3 cases as I think it is clearer this way
        'as to how the code works and
        'how one might see the problem in real life.[/color]
        Case Is < 21
            Tb = SumBOTTOM - -SumTOP
            Ts = Int((LoadAmount - Tb) / 2)
            Bs = LoadAmount - Tb - Ts
            LoadTOP = True
            LoadBOTTOM = True

        End Select

        If LoadTOP Then
[color=green]            'Load TOP items[/color]
            With rstLoad
                .AddNew
                [color=red][b]...[/b][/color]
                .Fields("ProductAmount") = Tb + Ts
                .Fields("TruckNum") = TruckNum
                .Fields("Bottom") = False
                .Update
            End With
        End If

        If LoadBOTTOM Then
[color=green]            'Load BOTTOM items[/color]
            With rstLoad
                .AddNew
                [color=red][b]...[/b][/color]
                .Fields("ProductAmount") = Bs
                .Fields("TruckNum") = TruckNum
                .Fields("Bottom") = True
                .Update
            End With
        End If

        [color=green]'Increment TruckNum if a new truck is required[/color]
        [color=green]'for the next assigning of truck space.[/color]
        If NewTruck Then
            TruckNum = TruckNum + 1
        End If

    Loop

    [color=green]'Move to next Order Detail for [b]small[/b] items[/color]
    rstDataSmall.MoveNext

Next iSmall

 

[spacepro]

Cheers

I've had to remove some lines of the code to make it fit in one poast

Do you mean post ?

Thanks cosmos really appreciate, I haven't had much chance to look at the code lately as the boss if off sick at the moment, but when I get chance i will post if I have any queries. But looks good and I thank you for your continued support .

Take Care

Andy

 

[Cosmos75]

I've had to remove some lines of the code to make it fit in one poast

Do you mean post?

At least they are phonetically the same!

I haven't had much chance to look at the code lately as the boss if off sick at the moment,

Don't you meen "is off sick"?

But looks good and I thank you for your continued support .

No problem. Glad to be of help!