Calculating Transport Costs through Access 2007 Form

[Qpido]

Dear Forum Members,

I'm working for a manufacturing company and we have an on-going problem with the consistent arduousness of trying to find the best price for our transporting costs.

We have 2 suppliers both with their own prices, switching between these each time takes a lot of time and can lead to mistakes sometimes.

I want to create a form in access that contains the data of which countries we ship to (and have data for from the transport companies), the weight you want to ship it at, the postal codes that you want to ship it to and then it should display both prices.

I have assembled all the data in the database with the prices of each and such, but I am getting in a huge mess not knowing where to start and how to link everything.

I was rethinking the concept and might want to use access in combination with excel sheets that have all the data, because excel is easier to edit.

Qpido

 

[Montez659]

Qpido,

Can you give me an idea of each of your tables and the information contained therein? If I can get a handle on a sample of the data, I might be able to help give you some direction. Also, how often do the suppliers change their prices, and are you importing this directly into Access?

 

[Qpido]

Dear Montez,

Suppliers change their prices each year.

I have divided the following tables with the following data.

I have the first table with this (Countries : Table):
Country ID | Country (Name)
1-15

Next one (Weight Class : Table):
Weight ID | Weight Class (Description) | Weight #
1-48 Just text with "up to 50/25000kg" and the last one just the number (50/25000, etc) was hoping to use this as a possibility for others to paste the actual amount they have in kg and it would automatically understand that it was between these values

Next (Postal Codes : Table):
Postal ID | Country ID (from first table) | Post Code (Description) | Bosman Zone ID | Rotra Zone ID

Bosman and rotra are the companies and assign different zones with different postal codes. I have made it so that they all coincide well.

I could e-mail you the database if you PM me your e-mail.

Thanks in advance,

Jerome

 

[Montez659]

So, you are looking to create a form where the user can enter the info and get shipping rates from both providers?

How proficient in VBA are you (none, beginner, working, crack-that-whip)? And also, what is your timetable, and are you willing to spend some time learning or do you just want it done?

I prefer to teach you the process of creating the form with what you need as opposed to just doing it for you as you can then use that knowledge for future projects to let your supervisors know that you are the man!

 

[Qpido]

Montez,

I think you have a great attitude towards educating others and I do the same in consulting.

Anyway, I have worked with beginner stuff of Visual Basic. Making a whole program 4 years ago and not having worked with it since has made me incredibly rusty.

I am very willing to learn and would love an enjoyable approach to learning. (I have had many tutorials that could not define simple terms and have been stuck on them)

You are correct, a form where someone can enter certain information but basically they are also selecting options mostly. (Weight = selection + input, Country = Selection, Postal code = selection out of list just like country)

Crack your whip,

Jerome

 

[Montez659]

Sounds good. One thing that I don't see is prices. If the purpose of the form is to find out who has the best price, that is a key piece of info. I know that tthe prices will be based on Country, Zone, and Weight - is that correct? Or is Zone already taking which Country they are in into effect?

I'll let you get back to me on that question. I think that your structure is pretty good so far, but depending on how Country, Zone, and Weight relate to each other, it could get a little tricky.

 

[Qpido]

Yes, I have 2 extra tables with price.

Bosman Prijzen
Bosman Zone ID | Weight ID | Bosman Prijs (Price) | MAUT DE | MAUT AT (Diesel costs for germany and austria)

Ideally I'd rather have the bosman price and the mauts be taken out of excel as these are super easily changeable. (Maybe link with Excel sheet? I've played around with this and it keeps it updated in Acces)


Rotra Prijzen
Rotra Zone ID | Weight ID | Rotra Prijs | MAUT DE | MAUT AT

Jerome

 

[Montez659]

Jerome,

Well, the process of creating the form should be failry easy. I guess the harder part is going to be the VBA, but we'll take it step by step. So first things first, create a blank form. There is probably no need to use the wizard since nothing that you have stated so far leads me to believe that the entire form needs to be bound. On your form, begin adding the control types that you want and give them some meaningful names. You will want to add text boxes for anything that needs to be manually entered and comboboxes for anything that you want them to pick from a list (I think you knew that). Report back how this phase goes and then we will start the process of doing the underlying coding.

Also, you mentioned linking Excel and Access together for your prices. Sounds good, but I have no experience in that area at all, but it seems like you have a handle on that.

 

[Qpido]

Dear Montez,

I have begun placing Listboxes(more efficient than comboboxes) and Textboxes. However it asks me a specific question of 1) Do I want to return a value in a table or query? 2) Type in the values that I want (not this one obviously) 3) Find a record on my form based on the value I selected in my list box.

Which do I choose?

For the purpose of design I will continue making the layout I want it to be in.

Awaiting your reply,

Jerome

 

[Montez659]

You are wanting to return values from a table or query. After you select the table/query option, you will then be prompted to choose which table, and probably which items off of that table. I would probably select the Primary Key (PK) which it might do automatically anyway, and then select one or two values that would be meaningful to the user. In the case of the countries, you would have the PK and the Country.

If you were to actually look at this list box in form view, you would notice that it shows both the CountryID and the Country. You can easily hide this first column by setting the column count to 2 and making the width of the first column be zero. Then you would only be showing the Country and the PK would be hidden, but the info is still there.

Go ahead and work up all of your listboxes/comboboxes with above info and we'll see how it goes. If you got questions, let me know.