Question SQL to retrieve data from one field / table based on value in another field / table (1 Viewer)

AOB

Registered User.
Local time
Today, 13:50
Joined
Sep 26, 2012
Messages
613
Hi,

Probably a bit of a dumb SQL question but as usual, if you don't know...

I have three tables in an Access database.

One holds transaction information (including date & currency)

Another holds exchange rates by date and currency (one record per date, each currency has its own field)

The third is a basic mapping table used to convert abstract date formats to a recognised format. Again, one record per date, just two fields (the text version mapped to the 'true' date stored as Date/Time)

(I have no control over the source data and this was just the easiest way for me to be able to query by date...)

I have the below SQL to retrieve data into Excel for pivotting etc. :

SELECT [TransactionTable].*, [DateConversionTable].TrueDate
FROM [TransactionTable]
INNER JOIN [DateConversionTable]
ON [DateConversionTable].TextDate = [TransactionTable].TextDate
WHERE [DateConversionTable].TrueDate Between #2012-08-01# And #2012-08-31#

Now I want to add to that SQL such that I can pull the exchange rate for each record's currency and date as well. But struggling with the syntax as the field to return from the ExchangeRate table is based on the currency field in the Transaction table (and therefore arbitrary)

Any pointers? Am assuming it's another INNER JOIN of sorts but not sure how to put it together?...

Thanks in advance!

AOB
 

Users who are viewing this thread

Top Bottom