Transpose

[Wahab]

Please show me how to write recordset to convert Excel sheet
which i Linked in a table “CurRateSheet “ my table looks like:
Code Kwt Bhn Qtr
USD 0.268 0.377 3.643
BHD 0.710 1.000 9.654
DKK 0.053 0.073 0.717
I want to convert above one with access table (TblCurrency) in this way:
Country Code Rate
Kwt USD 0.268
Kwt BHD 0.710
Kwt DKK 0.053
Bhn USD 0.377
Bhn BHD 1.000
Bhn DKK 0.073
Qtr USD 3.643
Qtr BHD 9.654
Qtr DKK 0.717
Sometime countries are increasing or decreasing, I have more than 110
countries and currencies and want to compare the monthly rate form past to
current month and feed in system with
correct decimals, slight mistake in entry gives unbelievable difference as
transaction volume is too high. My idea is to compare both tables and mark
only fields which rates are changed, so I
can enter those rates only and minimise the entry mistakes. For this reason
I need to write code which will make my job automatically. I appreciate if
someone will give me how to write codes.
Thanks and regards

 

[Douglas J. Steele]

You can convert your current table to the desired table using a Union query:

SELECT "Kwt" AS Country, Code, Kwt As ExchangeRate
FROM CurRateSheet
UNION
SELECT "Bhn", Code, Bhn
FROM CurRateSheet
UNION
SELECT "Qtr", Code, Qtr
FROM CurRateSheet

Once you've got that query built (let's call it qryCurrentValues), you can
use it to update TblCurrent using SQL like:

UPDATE qryCurrentValues LEFT JOIN TblCurrent
ON TblCurrent.Country = qryCurrentValues.Country
AND TblCurrent.Code = qryCurrentValues.Code
SET TblCurrent.Country = qryCurrentValues.Country,
TblCurrent.Code = qryCurrentValues.Code,
TblCurrent.ExchangeRate = qryCurrentValues.ExchangeRate

(That query will update differences, and add missing ones. See my November,
2003 "Access Answers" column in Pinnacle Publication's "Smart Access" for
details. You can download the column and sample database for free at
http://www.accessmvp.com/DJSteele/SmartAccess.html )

Note that I've changed the name of the field Rate to ExchangeRate. Rate is a
reserved word, so you shouldn't use it for your own purposes. For a
comprehensive list of names to avoid, see what Allen Browne has at
http://www.allenbrowne.com/AppIssueBadWord.html