The shipping costs should not be hard coded in. You should have a table to store the current cost amounts so that your calculation can use them. And yes, you will want to store the shipping cost unless you want to go through the extra hassle of storing the shipping cost effective dates and then calculating on the fly with that. I would think that wouldn't be the best way to do it, even though it is the more normalized way, it isn't the best in practice. Storing the cost in this case is good to do.
But as I said, the actual amounts to use in the calculation should be stored in a table and retrieved to calculate the amounts at the time you are calculating it. That way you don't need to change the programming to change shipping costs; only make record changes within a table.
no not as of right now but i'm sure it is something I will have to contend with once the wholesale aspect is up and running I only have 2 people who buy wholesale right now and their orders are minimal
The shipping costs should not be hard coded in. You should have a table to store the current cost amounts so that your calculation can use them. And yes, you will want to store the shipping cost unless you want to go through the extra hassle of storing the shipping cost effective dates and then calculating on the fly with that.
Yes, sort of like that. It is also like how you would store the price of an item because in two months the price might change and if you didn't store the price and then went to get the calculation, it would say one thing but wouldn't be the price you charged.
I have a sample database which shows how you can use a table with an "EFFECTIVE DATE" for storing prices, but in the long run it is much more of a pain than just storing the price to begin with.
Function GetRate(lngProdID As Long, lngQty As Quantity) As Currency
Dim strSQL As String
Dim rst As DAO.Recordset
Set strSQL = "Select ShippingRateBase, ShippingRateAdditional " & _
"FROM tblShippingRates " & _
"WHERE ProductID = " & lngProdID
Set rst = CurrentDb.OpenRecordset(strSQL)
GetRate = rst!ShippingRateBase + ((lngQty -1) * rst!ShippingRateAdditional
rst.Close
set rst = Nothing
End Function
That is good to know because as I expand my inventory to include other items they very well may need to be charged shipping differently mental note for the future
That is good to know because as I expand my inventory to include other items they very well may need to be charged shipping differently mental note for the future
I'm sure it isn't especially since I know some PHP... Yes i'm willing to learn I just worry that I come across as a complete idot and that I shouldn't even be messing around with it
I was thinking should I just implament the shipping that varies on the product now to save my self the hassel i'm sure it will cause it to have problems in the future...
I'm sure it isn't especially since I know some PHP... Yes i'm willing to learn I just worry that I come across as a complete idot and that I shouldn't even be messing around with it
Hey no worries - remember we all had to start somewhere. It didn't just automatically "happen" for us either.
So, to explain the function that I have built there, we first say that we are wanting to get a total shipping cost. This is just one way you can do it, however.
The function starts off by declaring a few things.
Function GetRate(lngProdID As Long, lngQty As Quantity) As Currency
So, the name of the function is GetRate and because it just says FUNCTION (instead of PRIVATE FUNCTION, it is a PUBLIC function. You can also use PUBLIC FUNCTION GetRate to declare as public.)
The lngProdID is declared as a long integer (which is the datatype that the product ID is (or should be) in our table. lngQty is declared as long but could probably have been just Integer but it is good to be consistent so we kept both integers to longs. And finally the As Currency means that we will be returning a value which will be returned as a Currency datatype.
We will declare the variables which we need to use in just a little bit:
Code:
Dim strSQL As String
Dim rst As DAO.Recordset
We will need a SQL string so we have that first declaration and then we want to use a DAO Recordset so we declare one. It is best to be explicit (saying ADODB or DAO) when declaring recordsets and Access 2007 and above is more picky about that than ever before.
Next, is just assigning the SQL query to the strSQL variable. If you notice, we have a few & _ at the end of the lines. This is to continue the lines and it can be done in several ways. You can use a line continuation _ without the ampersand (&) but you can't indent the rest like I have done if you don't "concatenate" the strings like I'm doing by using the ampersands. I like to indent it so it is easier to read. I also split them up by the Select, the From, the Where, the Group By, and the Order By on each line so that it is easier to read. I do have a typo in the original which I'll need to go fix. You don't use SET with a string variable so I'll need to change it to what I have below:
Also, note that the variable is OUTSIDE of the quotes in the string so that we can ensure that the VALUE of the string is used. It can be possible at times to leave it inside of the quotes but I never do just to ensure proper handling and passing of values.
Set rst = CurrentDb.OpenRecordset(strSQL)
GetRate = rst!ShippingRateBase + ((lngQty -1) * rst!ShippingRateAdditional
rst.Close
set rst = Nothing
We set the recordset variable by opening a recordset from the CurrentDb. We do that by passing the SQL string that we created and assigned to the strSQL variable. Once we have that (and we are expecting ONE record - and we could do a test to ensure that we have a record and only one, but we'll leave that for now) we can then read the values and use them in our calculation to get the total shipping cost.
We are doing the calculation AND assigning the total to the Function by using
Which it is assigned to the function by using the name of the function on the left of the equals sign and then we use the values. How we do that is by using the name of the recordset object (rst in this case) and the BANG operator.
You could also use rst.Fields("ShippingRateBase").Value
or
rst("ShippingRateBase").Value
as well. I just use the rst!ShippingRateBase as the one that I like.
So for the base we just need the base value and then we also need the quantity (which we've passed to us to use as lngQty) but minus one (-1) since it is for each additional item. And then that is multiplied by the ShippingRateAdditional which is gathered by again using rst!ShippingRateAdditional.
The last part is we close the recordset and then destroy the recordset variable (doing some "clean up work") by using Set rst = Nothing.
Ok I'm pretty sure I follow you in all that.... and you would call this function in the form?
also Would i need to build that actual query or is that what that function is doing is building the query
Ok I'm pretty sure I follow you in all that.... and you would call this function in the form?
also Would i need to build that actual query or is that what that function is doing is building the query
what would be the best way to identify the type of product?
Should I have another table with the types of products?
right now I sell itty bitty pieces of jewlery but i'm adding a makeup line so that would be 2 types of product
The tblInventory already has a type field that I was currently using to specify the type of charm the piece was but that isn't really important could I just use that same field and change the purpose? This would work by an ID number assigned to the product not the SKU for each product right?
I just want to make sure I completely understand this because I don't want to get lost in it and then spend 3 days trying to figure out where i got lost to spend another 3 days trying to get unlost
