Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Jan 2010
    Location
    Jacksonville, NC, USA
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I am creating a database that I would need to input/show multiple currency types. I have tried to do this in 2003 and 2007 and I am completely puzzled. Can anyone assist me with this?

  2. #2
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Do you mean show different currency formats,
    Or do you mean putting different currency entries into the same field?
    Can you provide an example of what you need to do.
    Andrew

  3. #3
    New Lounger
    Join Date
    Jan 2010
    Location
    Jacksonville, NC, USA
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I mean so that it can show different currency amounts. For example if I have a client in Japan that has paid me in Yen then I would like for my currency to reflect Yen, but if the next client is here state side and pays in dollars i need for that currency to reflect U. S. dollars. I do not have an example just something I am trying to build.

  4. #4
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    I think that you are not going to be able to do this in a single field.
    Especially if the data is in a continuous style form or datasheet, because
    unlike Excel all data in a field has the same format.
    You might be better off putting another field with a currency type as a choice

    e.g. Yen USD, GBP etc

    Then you can just enter the amount in the field and define the currrency in the adjacent field.
    This would have the benefit of then allowing currency conversion by formula
    for the sake of later reporting.
    Andrew

  5. #5
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    I agree with Andrew, but I wonder if there is another complication. Are all the currencies involved Decimal currencies?

    (Maybe all currencies are decimal now?) But if you had to deal with something like the old Pounds, Shillings and Pence that would complicate things.
    Regards
    John



  6. #6
    New Lounger
    Join Date
    Jan 2010
    Location
    Jacksonville, NC, USA
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Andrew and John thank you both for your quick responses and helpful insight. Yes all the currencies are decimal currencies. I don't know what the formula would be for JPY. Could you provide me the info on what that formula would be? Again thank you!

  7. #7
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    That depends on what you want to do and where and when you need to calculate it.
    I would suggest that against the currency entry you also need to store the exchange rate as well as the Currency Type

    eg Along with ay other fields you need you would need to store

    Currency Type, Amount, Exhange Rate


    Data Example
    [font="'Courier New"]GBP, 20.5, 0.62[/font]
    [font="'Courier New"]USD, 15, 1[/font]
    [font="'Courier New"]EURO 25, 0.70[/font]
    [font="'Courier New"]GPB 20, 0.62[/font]
    [font="'Courier New"]YEN 1245, 93.43[/font]

    This is because exchange rate is a dynamic quantity
    You could get it automatically picked up from a Currencies Table which would need say the following fields

    Currency Type (Primary Key), Exchange Rate (against a base currency say USD), Currency Symbol
    Exchange rate would need to be captured because it is like Price at point of sale.

    Data Example (Minus Headings)
    [font="'Courier New"]USD, 1, $[/font]
    [font="'Courier New"]GBP, 0.62, [/font]
    [font="'Courier New"]EURO, 0.70, [/font][font="'Courier New"][/font]
    [font="'Courier New"]YEN, 93.43, [/font]

    Then the Exchange rate can be picked up from this at time of entry
    and it is easier to change the current rates.

    Also the calculation of amounts can then be easily converted to the base currency for totalling etc

    Hopefully some of that will make sense to you.
    But not totally sure what the end purpose of the database is so difficult to be specific.

    Give a bit more info or Upload a Demo database and we can probably give more help.
    Andrew

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •