I have three really small tables, which gave me a lot of headache.
They reside in a MS Access database and a I am accessing them through
ADO.NET(VB).
Can't figure out how to make a "Select" query on them.
These are the tables:
1St - "Transactions"
TransactionID - primary key, autogen
TransactionNum - alphanumeric
TransactionDate - short date
Amount - number
CurrencyID - number - foreign key to Currencies
ForeginCurrencyAmount - number
CurrencyID - number - foreign key to Currencies
SenderName - text
SenderCountryID - number - foreign key to Countries
ReceiverName - text
ReceiverCountryID  - number - foreign key to Countries
2nd table - "Currencies":
CurrencyID - primary key, autogen
CurrencyName - text
3rd table - "Countries":
CountryID - primary key, autogen
CountryName - Text

I need a query which shows the following:
TransactionDate
Amount
CurrencyName(for Amount)
ForeginCurrencyAmount
CurrencyName (for Foregin Amount)
CountryName (for Sender)
CountryName (for Receiver)


Any help will be greatly appreciated

Someone will help you I'm sure, but in the meantime try
http://www.w3schools.com/sql/




Show quoteHide quote

You have a repetition:
CurrencyID - number - foreign key to Currencies -  [I assume:
ForeignCurrencyID]

In case you ask more questions on this, please provide a link where one
can download the access file.

Also, imho probably this is not the best group where to make this
question.

Anyway, I propose (whatch out line splitting):

----------------------------------------------------------------------------

SELECT
         T.TransactionDate AS [TransactionDate],
         T.Amount AS [Amount],
         C.CurrencyName AS [CurrencyName],
         T.ForeignCurrencyAmount AS [ForeignCurrencyAmount],
         FCurrencies.CurrencyName AS [ForeignCurrencyName],
         C1.CountryName AS [CountryName],
         FCountries.CountryName AS [ForeignCountryName]
FROM
         (((Countries FCountries
          INNER JOIN
         Transactions T
          ON T.ReceiverCountryID = FCountries.CountryID)
                   INNER JOIN
                  Currencies FCurrencies
                   ON T.ForeignCurrencyID = FCurrencies.CurrencyID)
                            INNER JOIN
                           Currencies C
                            ON T.CurrencyID = C.CurrencyID)
                                     INNER JOIN
                                    Countries C1
                                     ON T.SenderCountryID =
C1.CountryID

----------------------------------------------------------------------------

or, if you prefer the sql92 style:

----------------------------------------------------------------------------

SELECT
         T.TransactionDate AS [TransactionDate],
         T.Amount AS [Amount],
         C.CurrencyName AS [CurrencyName],
         T.ForeignCurrencyAmount AS [ForeignCurrencyAmount],
         FCurrencies.CurrencyName AS [ForeignCurrencyName],
         C1.CountryName AS [CountryName],
         FCountries.CountryName AS [ForeignCountryName]
FROM
         Countries FCountries,
         Transactions T,
         Currencies FCurrencies,
         Currencies C,
         Countries C1
WHERE
         T.ReceiverCountryID = FCountries.CountryID AND
         T.ForeignCurrencyID = FCurrencies.CurrencyID AND
         T.CurrencyID = C.CurrencyID AND
         T.SenderCountryID = C1.CountryID


----------------------------------------------------------------------------

I hope I got right what you had in mind...

-tom


Nikolay Petrov ha scritto:

Show quoteHide quote

Thanks Tom

PrintPreview problem
Ternary Operator?
static methods
Whats new in .NET 2.0 for VB?
Flashing when switching mdi forms
Page_Load vs Submit_Click - which one first?
rounding time
Single instance application
list box to combo box
Save text stream as binary in database