Results 1 to 11 of 11
  1. #1
    Join Date
    Sep 2010
    Posts
    8

    Unanswered: query that selects the table

    hi,
    i have identical separate tables with 2 different currencies (one table has USD transactions and the other has the AUD transactions) and would like to run queries and then reports by specific currencies. i would find it very ugly to replicate all the queries and reports and separate by currency so if i can select the currency/table then i would be a happy man.
    The tables have numberous dates throughout so a quick currency conversion wont really work here.
    Any suggestions would be appreciated.
    George

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Personally I think it's a mistake to have 2 tables (it is a normalization issue). I'd have 1 table with an additional field to indicate the currency. You can't have a dynamic table name in SQL, so you'd have to build SQL in VBA to do what you're contemplating. With the data in one table, you simply have currency as a criteria.
    Paul

  3. #3
    Join Date
    Sep 2010
    Posts
    8

    query that selects the table

    Thanks,
    My problem is that both tables have exactly the same transactions just that i need to report in both AUD and USD. it is a double up of transactions to have in the database - i know, but i cant think of another way.
    if i could load the fx conversion rates that would be ideal but that would need to be done on a daily basis - or loaded with a monthly file but i dont have this data just the transactions in both forms.
    George

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Why not just have fields for the AUD and USD values in one table? Or the value of one and the applicable fx rate so the other can be calculated? I can't imagine having 2 tables with the same transactions in them.
    Paul

  5. #5
    Join Date
    Sep 2010
    Posts
    8
    Thanks Paul,
    this may be the best option for me. the full details of the transactions in two tables does seem crazy and a massive waste of memory.
    George

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    store the data in just the one row

    store the forex rate at the time the event happened
    OR
    store the actual value of the transaction as it happened.


    so you would store the base value + the forex value.
    y'don't need to store the value and the forex rate (you can derive one from the other)

    personally I'd suggest you store the absolute value of the transaction rather than a forex rate.. the reason?.. probably laziness I don't want to do the conversion on the fly (having to deal with rounding issues). for reporting purposes, especially if you make your table write only is you have a definitive traceable value. nothing upsets bean counters more than if there is a discrepancy between the items displayed and the totals, converting n the fly may cause that. of course if you want to annoy your bean counters please feel free to do so, it should give them some light relief from trying to find which shade of grey is most 'fetching'.
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Sep 2010
    Posts
    8
    Thanks for the tip - i think i prefer the no conversion option.
    Cheers
    George

  8. #8
    Join Date
    Sep 2010
    Posts
    8
    I have the two values in the one row in a query. is there a way to choose which column i want to report? say select the USD column or AUD column or even report the two.
    thanks
    George

  9. #9
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Any number of ways, depending on the specifics of what you want to do. If you had a main menu form where the user selected currency, you could set textbox control sources in the open event of the report, so it displayed the appropriate one. If you want to show one, the other or both, you'd probably want to have textboxes for both on the report, and then display them according to the choice made. You can also have things like this:

    =IIf(Forms!FormName.ControlName = "USD", [USDField], [AUDField])
    Paul

  10. #10
    Join Date
    Sep 2010
    Posts
    8
    Thanks Paul,
    i guess this is where i start to lose you. i have no form to select the currency, was just hoping to add a pop up ? to the Criteria of the query under each column. i may be biting off more than i can chew.
    George

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    do you ever wnat to report both currencies at the same time?

    if not then I'd suggest you design a single report place the controls for one currency on top of the other, taking note of which is which.

    when you open the report hide the controls you don't want to see (in the forms on open event)
    something like

    if myparameter = "USD" then
    tbAUSDValue.visible = false
    tbAUSDTotal.visible = false 'or whatever you call the relevant columns.
    else
    tbUSDValue.visible = false
    tbUSDTotal.visible = false
    endif

    the parameter can be set when you call the report from the switchboard or menu.

    you can either have separate menu items OR a single call based on a parameter on a form.

    I forget the details of how to set a parameter when you call the report

    to pull a value from another form its soemthing like

    lets assume the swicthboard/menu is called frmMenu
    the control there is called lbCurrency

    if frmMenu!lbCurrency = "USD" then
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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