Query Access to Table Not Included in Query

Topic

New Reply

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”));

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Replies

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.

Reply | Quote

John,

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

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[/Code]My query now reads:

CurrentValue: Round([AGW]*dGoldPrice(),2)+Round([ASW]*dSilverPrice(),2)

:cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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.

Reply | Quote

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!
Computer Specs

Reply | Quote

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.

Reply | Quote