Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2012
    Posts
    3

    Unanswered: Field from Table 1, as reference for field in Table 2

    Hi Everyone,

    Still new-ish to Access, so please excuse me if this question is either often asked (and impossible), or if there's a simple answer.

    I have two tables, and I'm writing a query to output my data in my desired format.

    1st table has Dates in the first column, and Currency codes (USD, EUR, GBP, etc) as the column headings.

    2nd table has exercises of stock options. One of the fields is a date, that I have linked to the first table's column of dates. Another field in this second table has the Currency code for that particular stock exercise.

    Ideally, I'd be able to use the Currency field in Table 2 as a reference, to tell the query which Column heading to look in, as a vlookup would do. However, I know Access doesn't work like that. I just know there's got to be some workaround (using dlookup or referencing a textbox or a subquery or sum sql code) but I'm no Access wiz.

    I'm making a new field "Exchange Rate", and my first thought was this, even though it clearly doesn't work:
    Exchange Rate: [tbl_exchange_rates].([tbl_new_exercises].[Currency])


    Any thoughts much appreciated

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if you are beginning to use Access then I'd suggest you use the query designer to do waht you want

    first things first though have you defined a relationship between the two tables. select TOOLS | Relationships
    add the relevant tables then click on the child table and then the parent table (it might be click then drag. define the relationship as Relational Integrity.
    having done that when you open the query designer and add the same two tables you should see the link defined.

    you will need to work out how you plan oin handling the currency conversion rate. usually for an equity system you would record the exchange rate of the actual transaction (so there is no need store an exchange rate against a currency, but there is against the actual contract/bargain.

    you can pull a current ecchange rate form a third party source ont he fly, or enter a value.

    depending on your business requirements you may need to store exchange rates for specific dates in time,. this is a problem givvent that some currentcies are volatile and change frequently during the day. some financial houses use the exchange rate at a specific point in time (whether thats open or close of market, midday) or an average rate. these rates are stored and pulled as required for reporting purposes (eg a quartley report showing change in portfolio value
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Apr 2012
    Posts
    3
    Thanks for your reply Healdem.

    The two tables are linked and I have used the query design view to set up the remainder of the query; the only field I'm struggling with is the exchange rate.

    And the exchange rates table is linked and I keep it updated daily to reflect changes in the market.

    My problem is that I have a list of exercises to be converted to local currency, and the local currency varies for each exercise- let's say two that need to be converted to USD, one that needs to be converted to SGD, and two that need to be converted to EUR. I already have a field in the parent exercise table that says what currency it should be in, I just can't think of any way to tell access to use the Currency field in the parent table, to select the correct column in the child table.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    OK probably best to pull the single exchange rate as a sub select query. I don't think you can do these with the query designer easily, if at all.

    coulod I sugegst you post your db here (sanitiser the data so theree is nothing sensitive in it, compact and reapir the db and then zip it and post as an attachement .
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Apr 2012
    Posts
    3
    Thanks again!

    I just attached a sample of my problem, with the 2 tables and the sample query.

    I understand I'm looking at it from an excel point of view, and access can't take "Local Currency" and go column by column until it finds that particular heading, and then select the value in the specified row.

    But yes, sub query or other code/ideas would be much appreciated.
    Attached Files Attached Files

Posting Permissions

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