From one table to another?

[jackmac]

Hi all,

Hopefully someone can help. I've been at this all week and seem to be making little steps but have been against a brick wall since yesterday morning now.

I'm building a database for my price list. It is based on simply the products I have in my shop. I sell to the public and to (a few ) trade customers. My peice is based on buycost + 10% for public and buycost + 5% for trade customers.

I have a table with product info in it, another table with percentage values (5% in one record and 10% in the next record) and I have created a collective table for all info from both tables. (I'm not sure if that's the best way).

I have created an input form that I put the product data in. I want to be able to put the buy cost into the input form then it automatically calculates the trade price and public price for me. I have managed to do that using AfterUpdate on a form. The problem I have is that when I create a new record, it doesn't keep the percentage value, I have to put that in again which creates a new record in the percent table. The obvious danger would be if percentages change to say 4% and 9%, I would need to change all instances of 10% to 9% etc.

If I built in Excel obviously I could create a worksheet with percentages then put say $A$1 and that would work. Is there any way I can get a new product record to look in the percent table and use the value in the first record for trade percent then second record for public percent. ( I have tried creating a field for trade percent and another field for public price. I also tried creating a field called percent and record 1 was the trade percent, record 2 was the public price - not sure which is the best way to move forward)

I want to create a form which I can print which will show all trade prices then another form showing public prices.

Hopefully someone can shed some light on this for me.

TIA

Jack

 

[DCrake]

What you need is a table that will hold any system settings such as Trade Percent and Other Percent.

Then you need to create two public variables in a module, lets say

Public TradeOnCost As Double
Public OtherOnCost As Double

Then when you open the database in the first instance you would have a Module called, lets say, ModSettings. In that you would have a sub routine, called SetSettings

Public Sub SetSettings()

   TradeOnCost = DLookup("TradeOnCost","TblSettings")
   OtherOnCost = DLookup("OtherOnCost","TblSettings")

End Sub

So now when your startup form opens on the OnLoad event you would call the SetSettings routine.

Private Sub Startup_Form_OnLoad()
   Call SetSettings
End Sub

What we have now is two public variables that will remain constant thoughout the entire session that the application is open. In your forms you can include these variables in your calculations. Such as:

Me.TradePrice = Me.CostPrice + (Me.CostPrice * TradeOnCost)

There is allsorts of things you can do with public variables this is just a simple explanation of one method.

David

 

[jackmac]

Excellent!!! Thanks for the help David. Quick follow up... would you say that the table I use for the percentages should one record with two percentage fields in it or two records each with one field in it? Which is the most suitable way?

Thanks

Jack

 

[jackmac]

Re: From one table to another? - Woohoo!!

Hi David,

Just had a play around and seem to have cracked it! Your help definitely much appreciated.

I now have a Percent table with one record and two fields (TradeSell and PublicSell). I created the modules as suggested and set the OnLoad to use them.

I have another form now which, if I need to change the Percentages, it closes the input form and opens an input Percentage screen. When I press to 'Update', it closes the Percentage form and opens the original Input form, wherein the OnLoad kicks in again and updates the Percentage change.

I also set the value fields on the form to be 'Locked' so that I can't accidentally change a value - it's based purely on the buy price now.

Thank you very much for the pointer in the right direction - genius!!

Now I just have to get the form to make coffee and I'm sorted..lol

 

[jackmac]

OK, found another slight issue...

When I change the percentage value then go back to the product form, it updates the trade price and public price for the first record, but nothing after that. The second record only changes if I go in and manually retype the buy price.

I set record one and two to have a buy price of 100, trade became 105 and public 110. I went to the percent form and changed to 20% and 30% as a test. When I closed and returned to the form, it showed the first record now had a trade price of 120 and public became 130. Unfortunately the second record still showed the 105 and 110 values. When I retyped the buy cost as 100 on this form, the values changed (they obviously looked at the percentage form and took data from there).

Any ideas on how I can get it to update all trade and public prices when the form loads again?

TIA

Jack

 

[DCrake]

If you are going to be changing the trade and public rates intermittently what you need to do is to store the net cost of the product in your table plus the percentage rate applied to that sale. So when you revist a record it does not use the current rate it used the rate that was applied at the point of sale.

For new records there will be no rate so it uses the current rate.

David

 

[jackmac]

Hi David,

I originally went with that method but it proved unworkable after I figured it would mean a percent change would mean a change in every record rather than in one place.

I wanted to have a single point of change for the percent - to be honest, I may only change it once a year, if that - so that I could make a change and it would alter all of the public and trade prices once I made the change.

There was always the chance that if someone wanted to buy a large amount, the percentage would drop on those products so it would be easier to make a single change rather than on all the products they bought.

It works well if I add new products, it's only when I change a percentage that it won't update, other than the record the form opens on. (You suggested I change the OnLoad which worked fantastically well, but as I mentioned, it always goes for the record it opens on, which tends to be record 1).

I thought about trying to find a way to make an OnLoad-type sttement for each record, so that when I scroll through the records, it runs the process again, thus updating the prices but am not sure if that is possible.

Thanks

Jack

 

[DCrake]

Lets create a senario.

trade customer comes in and buys 200 widgets. Widgets have a net cost of £1, as this is a trade customer you add on the current %age (5%) so the total cost is £210. You then decide to change the default rate for trade customers to 7.5%. Now he comes back tomorrow and buys another 200 widgets, this time they cost him £215. All his purchases are added to his account. Now at the end of the month you print him a statement. In your invoice table you will have two transactions 1 for 200 on the 1st and another on for 200 on the second. His total bill will be £425.

Now if you only had just one place where the on cost is stored as it is now and you applied that rate to the invoice it would apply the latest rate to both transactions. This is historically incorrect. By storing the % rate in the transaction table you can calculate the gross cost based on the % rate at the time the transaction took place.

The table where we store the default rates is for future transactions not historic. When you bring up an earlier record it will calculate the gross based on the value in the table not the default value.

Hope that does not confuse the issue.

David

 

[jackmac]

Hi David,

That all makes sense, and I agree is the correct way to do things.

I think perhaps I was looking at it in a different way. I was thinking that in the next year, if prices rise by say 2%, I could apply a 2% rise across the prices, but the better way to look at it would be that my suppliers would raise their prices by 2%, so when I go through their products and change the price I buy from them, it will automatically change the price I sell at anyway.

I think if I do it this way it should work fine.

Hope that makes sense.

As always, clear and helpful support David, thanks again... very much appreciated!!