I'm currently organizing an MS Access database and setting up some SQL queries that export data into CSV files for Yahoo Store. One of the things I had to do was to create fields in the CSV file that are compound of other existing fields in the database, and some that even depended on the existence of other physical fields in the Access database.
Suppose you had an Access database with an "Item" table that contained the following fields:
Code, Name, Description, VenderName, VenderId
And say you wanted to create a CSV file that contains the following fields:
Title: A combination of Code and Name fields in the database.
Manufacturer: A combination of VenderId and VenderName in the database.
The following SQL query can be added to your Access Database to make the Title field:
SELECT [Item.Code] & " " & [Item.Name] As Title FROM Item;
To make the Manufacturer field, modify the query above as follows:
SELECT [Item.Code] & " " & [Item.Name] As Title, [Item.VenderId] & " " & [Item.VenderName] As Manufacturer FROM Item;
The next challenge I had was to create a field whose value depended on the existence of another field.
For example, the CSV file contained an additional field named Caption, whose value is determined as follows:
If Description value exists, then Caption=Description
else then Caption=Code + " " + Name + " " + VenderName
How do you translate that in SQL?
The answer is to use the IIF statement, something from VB coding I believe...
The following SQL will generate the Caption field as described above:
SELECT IIF( ([Description] Is Null), ([Item.Code] & " " & [Item.Name] & " " & [Item.VenderName]), [Description]) As Caption FROM Item;