table setup currencies and sales orders (1 Viewer)

Frederique

Registered User.
Local time
Today, 06:02
Joined
Sep 5, 2016
Messages
17
Evening chaps,

I've been learning about databases the pas few months and i've come a long way since the start. However i hit a wall as how to tackle the following problem. I read a lot on this forum but theres no clearsolution to my situation.

We have a company in the UK that produces stuff so the product cost is in £. We also sell in europe so i need a value in € for cost and selling price on time of the order input. When setting up my form+subform for order input i realised i offended the rules off normalisation. I thought i needed to input the currency and conversion on order level + the same on the orderlines level for safekeeping if theres an update/change.

This made me realise that this probably isn't the way forward since if i have to store the same thing multiple times.

So in short.
Where do i put the currencies/conversion rates? I have a seperate currencytable for lookup reasons. At first i figured i could put conversionrates in that table but if they change my orders change as well.

I've been trying so far:

*using nested iif functions to recalculate whatever currency is specified to €. But when going further adding discounts and tax and freight and so on it looks cumbersome so clearly i haven't the ideal solution.

*also used the double, standard format without any currency symbol hence the reason why i've used a lookuptable to specify the currency per order and product. This way i can add it later on in reports based on the currency selected.

Customers,suppliers,projects,shipping,products and so on all work together like a charm but i can't seem to overcome this important obstacle. If i can get a push in the right direction as where to store what, or how you pro's would tackle this, that would be helpfull. Thank you kindly in advance.
 

sneuberg

AWF VIP
Local time
Yesterday, 22:02
Joined
Oct 17, 2014
Messages
3,506
I'm curious about when a conversion becomes fixed in your system. When an order is placed, shipped, or even later? Also how do you get the conversion rates? Do you get them from someplace on line automatically?
 

Frederique

Registered User.
Local time
Today, 06:02
Joined
Sep 5, 2016
Messages
17
Thanks for your reply.
The conversion rates i get from XE.com. I have them running on my desktop so i only need to glance at them on time of the order input. So the value is saved at order input level.

When i deal with payments tracking the conversionrate will be stored again to know the actual margin/profit each project/product brings. there will allways be a variance between the conversion rate on time of the order and on time of the payment.

I just need some guidance about the way of doing it correctly. If i understand one part the other will be similar. Thanks for your help in advance
 

jdraw

Super Moderator
Staff member
Local time
Today, 01:02
Joined
Jan 23, 2006
Messages
15,364
Do you have a data model (relationship window) for your current set up? If so, please do a screen print to jpg then post it as a zip (to avoid the 10 post limit).

You will need a currency conversion(lookup table). BUT, you'll also need some way to keep it current.
In you OrderDetail (lineItem) record you should record the Price and Quantity(# of units) of Product. I would also suggest you record the £ and conversion rate at the time of the Order, OR keep both the Price in £ and in € in the record. By keeping the Price and Quantity in the OrderDetail, you will not be affected by changes in the currency conversion lookup. Depending on how accurate your conversion factors, you might want to consider an online interface to an Exchange site(some central banks...).

You could use a Dated Exchange Rate type table, but how you'd keep it current, and to what end, seems problematic.

Also you may get some ideas from this older link
http://www.access-programmers.co.uk/forums/showthread.php?p=1312650
and/or
http://www.accessforums.net/showthread.php?t=28974&highlight=exchange+rate

Good luck.
 
Last edited:

Frederique

Registered User.
Local time
Today, 06:02
Joined
Sep 5, 2016
Messages
17
Hi Jdraw,

Thanks for your reply.
I skimmed down some of the obsolete relationships so the view gets clearer.
Your suggestions are most welcome.
 

Attachments

  • data model 1.zip
    140.6 KB · Views: 139
Last edited:

Frederique

Registered User.
Local time
Today, 06:02
Joined
Sep 5, 2016
Messages
17
hmm, i've read through the suggested links.
What if i would use another form that pops up on the time i enter an order.
This form could be based on my currencytable but with the following fields added:
.CurrencyID (autonumber)
.Currency (£,€,$, ...)
.CurrencyName (USD,GBP,EUR,...)
.ConversionRate (1€ = 1€ ; 0.8968£ ; 1.1148$ currentvalues 05-09-2016)

Then in the Ordertable i can forget about storing the currency and conversionrate so that will be a bit tidier.(I only used them for a dlookup on the orderlines level so i didn't have to enter them all myself on each orderline.)

On the ordersform i can autofill the conversionrate for the selected currency using a dlookup on the currencytable and store it in each orderline.

Or am i better off adding conversion rates manually on each orderline and forget about the currencyform.

Easiest way would indeed be that the currencytable fills itself based on an online interface each time i want to enter a new order. Then perform a dlookup eachtime an order is entered. However i think my skills in access are a bit to low to pull that off.
 

sneuberg

AWF VIP
Local time
Yesterday, 22:02
Joined
Oct 17, 2014
Messages
3,506
This form could be based on my currencytable but with the following fields added:
.CurrencyID (autonumber)
.Currency (£,€,$, ...)
.CurrencyName (USD,GBP,EUR,...)
.ConversionRate (1€ = 1€ ; 0.8968£ ; 1.1148$ currentvalues 05-09-2016)

Wouldn't you want the date of conversion rate as a field?
 

Frederique

Registered User.
Local time
Today, 06:02
Joined
Sep 5, 2016
Messages
17
So you mean putting an extra datefield on which will add a new record each time new rates have been entered?
To what purpose? Maybe using a dlookup on the orderdate to match the conversiondate and bring in the correct value?

Would be good to have some level of tracking in the table when i need to adapt orders from the past.
 

sneuberg

AWF VIP
Local time
Yesterday, 22:02
Joined
Oct 17, 2014
Messages
3,506
I guess I don't understand how this table is to be used. Are you going get the current conversion rate and add a new record in this table for each order? In that case I guess the order date would indicate the date of the conversion rate. But in that case why not just leave it in the conversion rate in the order table?
 

Frederique

Registered User.
Local time
Today, 06:02
Joined
Sep 5, 2016
Messages
17
Hi. Please find a screenshot of the inputform attached.
that was my idea exactly sneuberg.

But i thought i was sinning against the normalisation rules.

I'm currently writing a totals query where i multiply the prices based on the conversion rates using nested Iif functions.

So you think the form as it is would be ok?
 

Attachments

  • screenshot order input.zip
    243.3 KB · Views: 146

sneuberg

AWF VIP
Local time
Yesterday, 22:02
Joined
Oct 17, 2014
Messages
3,506
But i thought i was sinning against the normalisation rules.

Yes. It's a sin against 3rd Normal Form and it's called a transitive dependency. A currency rate is determined by the currency type (USD,GBP,EUR,...) and a date/time. That could be a natural primary key for a conversion rate table. Since this exists within the orders table in should be split out if you want to stick to normal form. But the date would be needed in this currency table.

If you violate this normal form duplication could occur. For example:

Code:
Order No      Date/Time             Currency Type     Rate 
0020           05-09-2016 08:00      EUR                1.1148$
0021           05-09-2016 08:00      EUR                1.1148$
0022           05-09-2016 08:00      EUR                1.1148$

But do you care?

I hope JDraw comments. I don't want to say whether what you have is ok or not.
 

jdraw

Super Moderator
Staff member
Local time
Today, 01:02
Joined
Jan 23, 2006
Messages
15,364
My thought was to have the currency and the conversion rate in the OrderDetail record along with Quantity and Price for this Order line. You would record the currency and the conversion factor and the Order Date would represent the Date on which this Order, this line item, this price in this currency and the conversion factor on that date. That removes the currency table with conversion rates and Date.
I foresee a lot of maintenance if you were to maintain a table of currency/conversion rate and date?
And yes, as sneuberg said, you would need to record the date with currency and conversion if you plan to use a separate conversion table.

Storing the Price in the OrderDetail records identifies the Price for that Item on this Date for this Customer. Changes in the Currency table(if you have one) will not cause changes in the OrderDetail record. Some will say there is a normalization violation, but that is incorrect. The Product Price in the Product table identifies the current price for that Product. The product price can change with time. The Product Price recorded in the OrderDetail record is the Price charged to that Customer for that Product on that Order --it will not change with time.

The above is a fact and is similar to your concerns with Conversion rate. Store it with the OrderDetail record, and it will not change. if you rely on a Conversion table you will need Dates for any conversion factor changes. It can be done with such a table, but is considerably more work/effort in my view.

Either method will work. Both are used in many systems.

I prefer storing the AgreedTo Price in the OrderDetail record.This is the Price agreedTo for this Item on this Date.
Also it allows you to have loyalty program, clearance sales, liquidation events ....etc without messing with your Product Price in the Product table.

Following quote from Pat Hartman
This is not a violation. The key to understanding why is to understand the dependencies. Prices change over time and storing the price with the order fixes the price at that point in time. This method is also used because it allows for custom overrides. For example, you may offer a customer a one-time price. Storing the unit price with the order supports that. Using a pricing table to keep historical prices does not allow one-time overrides.

I have referenced Product Price here since that is a common question. The same approach can be used for conversion factors.

Good luck with your project.
 
Last edited:

Frederique

Registered User.
Local time
Today, 06:02
Joined
Sep 5, 2016
Messages
17
Absolutely brilliant guys. Thanks for confirming this.
That was the push i was looking for. So i'll keep my currency table just as a lookup table to select the appropriate currency symbol.
I'm glad i can avoid keeping track of all those currencyrates. It would indeed complicate adding orders and maintenance.

i'll probably need more guidance from you in the future being an access novice but for now i'm good to continue.
 

jdraw

Super Moderator
Staff member
Local time
Today, 01:02
Joined
Jan 23, 2006
Messages
15,364
How do you plan to find/get/confirm the conversion factor/exchange rate for your application?
 

Frederique

Registered User.
Local time
Today, 06:02
Joined
Sep 5, 2016
Messages
17
Since theres only three people that will be using this database its rather straightforward.
I'm using XE.com to get the rates. They provide an app for both windows and desktop so we can always see the rates immediatly. I've no clue how i would be able to link access to those rates dynamically.
Hence for each order in a certain currency the corresponding rate at that specific moment is added in the order manually. Its automatically copied into each orderline during input to avoid having to type the rate for every single product.

The prices are then determined using nested iif functions.
If you have a trick up your sleeve to do this i'm all ears.

For the time being i can continue setting up my queries and reports.
 

Users who are viewing this thread

Top Bottom