Results 1 to 6 of 6
  1. #1
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts

    Question Query Access to Table Not Included in Query

    I've built a query for my coin collection database to calculate the precious medal melt value of gold and silver coins {see below}. What I want to do is to pull the current price of gold and silver from another table CODES which is not included in the query. The reason it is not in the query is that it contains a list of country codes for the coins and is linked to the Code field as shown in the Currency table in the image. There are fake codes in the Codes table for Gold "XAU" and Silver "XAG" however these two codes do not appear in the Currency table as the codes in that table are for identifying the country that issued the coin. Is there a way to build the formula for Current Value so that it can pull the values from the Codes table or as an alternative prompt for the values, I really don't like editing the query every time I want to use it.

    Here's the SQL code attached to the query design below:
    Code:
    SELECT Currency.Comments, Currency.Year, Types.Description, Currency.Code, Currency.ASW, Currency.AGW, Round([AGW]*1392,2)+Round([ASW]*34.34,2) AS CurrentValue
    FROM [Currency] INNER JOIN Types ON Currency.Type = Types.Type
    WHERE (((Currency.Comments) Like "*gold*" And (Currency.Comments) Not Like "*Golden*") AND ((Types.Description)="Coin" Or (Types.Description)="Set"));
    Attached Images Attached Images
    Last edited by RetiredGeek; 2011-03-15 at 12:48. Reason: Add graphic.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    You can use a DLookup to find the price, or you could create a custom functions that retrieve the two prices using DLookup, then include these functions in the the query.
    These will be slower than joining the table, but you probably won't notice it unless there is lots of data.
    Regards
    John



  3. The Following User Says Thank You to johnhutchison For This Useful Post:

    RetiredGeek (2011-03-15)

  4. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    John,

    Thanks much! For the edification of you other loungers here's what I came up with thanks to John.

    Code:
    Public Function dGoldPrice() As Double
    
          dGoldPrice = DLookup("[USD/1 Unit]", "Current Rates", "Code = 'XAU' ")
          
    End Function
    
    Public Function dSilverPrice() As Double
    
          dSilverPrice = DLookup("[USD/1 Unit]", "Current Rates", "Code = 'XAG' ")
          
    End Function
    My query now reads:
    CurrentValue: Round([AGW]*dGoldPrice(),2)+Round([ASW]*dSilverPrice(),2)
    Last edited by johnhutchison; 2011-03-15 at 20:02. Reason: Correct problems with Code tag
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Glad it worked.

    I have just edited the post to fix problems with the Code tags. They did not work because there was also 'Code' inside square brackets within the code itself.
    Regards
    John



  6. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Quote Originally Posted by johnhutchison View Post
    Glad it worked.

    I have just edited the post to fix problems with the Code tags. They did not work because there was also 'Code' inside square brackets within the code itself.
    John,

    Thanks again. You fixed it while I was searching the forum for an escape character I could use since the square brackets around the Code field name are necessary for the code to be correct. Is there documentation somewhere on what could be done to overcome this problem?

    On second thought maybe they are not required as there is no space in the field name????
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    You only need square brackets around a field name if the name includes spaces, so I would expect the functions to work without square brackets.

    There probably is an Escape Character solution, but I don't know what it is.
    Regards
    John



Tags for this Thread

Posting Permissions

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