Selecting to Access
The [highlight txt=’select to Access’] instruction allows a user to select data (via a query) in a Managed Table and copy the selected data to an Access database Table.[message type=”warning”]For the purposes of this article we are going to use a simple and fictitious Managed Table named “AirlineInfo”. You will want to replace this Table with one of your own.[/message]
Before we get started
Please further create a ‘Manipulate a file’ or ‘Multiple file manipulations’ action step so that you can add and use instructions.
The Managed Table has the following design:
The Managed Table contains the following data:
For this instruction to run successfully, the design of the Access Table must match the design of the Managed Table. That is, they must have an equal number of fields, and every field must share the same data type. The Access Table therefore must have the following design:
1. Create an ‘Select to Access’ instruction.
2. This instruction allows 2 methods of designing the query. Like the ‘Run query’ instruction, the Expert SQL Editor can be used. Alternatively, the Query Builder can be used. The Query Builder is a simple and intuitive tool which allows users who are unfamiliar with SQL language to design queries. Click the ‘Design query’ hyperlink to open the Query Builder.
3. Double-click the name of the Table from the ‘Available Tables’ pane. The fields from this table will appear in the Query Builder under the ‘Tables to work on’ section. All the fields will be selected by default.
4. Click the ‘+’ sign to the right of ‘And’ under the ‘Define selection conditions’ section.
5. You will now be able to specify a condition by adjusting 3 options. Click the first option (It will be populated by default with the name of the first field, which in this example is “[AirlineInfo].[Date]”. A dropdown which contains the names of other fields in this Table will appear. Select “[AirlineInfo].[Carrier_Name]”.
6. The second option is selected to ‘Is equal to’ by default. The dropdown contains many alternatives.
7. Type “British Airways” into the text field for the third option.
8. You have now designed the query. To illustrate this fact, click the ‘View/Edit SQL’ hyperlink to open up the Expert SQL Editor. You will see that the Query Builder offers a simple alternative to what would have been a relatively complex exercise in writing with SQL language.
9. Link the file named “Target” as the target file.
10. Use Table1 from the target file.
8. Save and run the step.
9. After the step finishes running, download and open the target file: