The files in our database catalogs provide many opportunities to increase site speed, functionality and to use as a base for building unique hotel content.

Common uses of our database files include:

  • Building targeted lists of specific properties by location, star rating, price, geography, or other combined criteria
  • Creating local content for frequently-requested static info such as property policies, descriptions, and image data
  • Generating and adding to your own SEO-boosting unique content from amenity and location data

The following instructions are based on Microsoft Access 2007. Your interface may vary from the illustrations and steps provided, however all basic steps are the same.


Importing a Database File into Access

ms-access-import-text-file

1. Download the desired database file and unzip it to an appropriate directory.

2. Launch MS Access and open the desired existing Access database, or create and save a new one.

3. Click the "Import Text File" button. (Can also be found under the External Data Tab)


4. Click the "Browse" button in the resulting window and navigate to the location of the file that was unzipped in step 1.

5. Leave the default import option of "Import the source data into a new table in the current database". Click "OK".


6. Change the default data format from "Fixed Width to Delimited". Click "Next".


7. Click the "Other" delimiter option and enter a pipe character: | (Shift + \) It may be necessary to first delete a space character from the delimiter field that is present by default on some versions of Access.

Check the "First Row Contains Field Names Box". Click "Next" when done.

8. Skip the "Field Options" screen. (Advanced Users may want to designate certain import options, otherwise, default settings are fine.) Click "Next".


9. Choose the "No primary key" option. Click "Next".

10. Click "Finish" to complete the import. (This may take some time depending on the file being imported and the speed of the computer being used.) To complete the import process click "Close".

Example Basic Query: Find all active properties in New York City

This example uses the ActivePropertyList relational database, found in the Property Data section of the database catalog.

1. Import the ActivePropertyList.txt file.

2. Click the "Create" tab and choose "Query Design".

3. The "Show Table" dialog box will appear automatically.

Select the ActivePropertyList table from the list

Click "Add," then "Close". A small pane containing the ActivePropertyList's fields will appear in the main window.



4. In the new table pane, double-click the "City" field to add it to the query dialog.

5. Do the same for the asterisk (*) at the top of the field list to show all fields in the query results.


query3.png

6. Add a criteria of Like "New York*" to the City field.

This will make the query return any City value that starts with "New York," so both "New York City" or "New York" will be included in the results. When using a city name that can be found in multiple countries or states, add those fields to the criteria as well so the exact location you want will be in the result set.

7. Click "Run" to see the results of the query.

8. Click the "Save" icon to save and give the query a name.


Joining Tables

This example uses the ActivePropertyList relational database, found in the Property Data section of the database catalog.

1. Import the ActivePropertyList.txt and the PropertyDescriptionList.txt files.

2. Click the "Database Tools" tab, then click the "Relationships" button.


3. The "Design" tab will automatically be activated.
Click the "Show Table" button.

4. Select the ActivePropertyList.
Click "Add".
Click "Close".
Do the same for PropertyDescriptionList.


5. As detailed on the diagram on the Property Data database page, EANHotelID is a common field between these files.

To join the two separate tables via this field, simply drag and drop one EANHotelID field on top of the other.


6. The "Edit Relationships" dialog will appear.

Click the "Join Type" button.

Choose "Include ALL records from 'ActivePropertyList' and only those records from 'PropertyDescriptionList' where the joined fields are equal."

This will keep any properties missing from the PropertyDescriptionList from excluding properties in the master ActivePropertyList. We want all properties listed in our main list regardless of entries in the joined table.

Click "OK".
Click "Create".


7. An arrow link will appear between the tables to indicate that the EANHotelID fields are joined between these two tables and Access will automatically detect their relationships when using Query Design.

8. Click the "Save" icon to save the relationship and close the dialog.


Using Joined Tables to Create a List of Properties with Multiple Criteria

This example will demonstrate how to map the property descriptions to the respective EANHotelIDs for all active properties in New York with a star rating of 3 or above. This example uses the files and relationships created in the previous section.

1. Click the "Create" tab and choose "Query Design".

2. The "Show Table" dialog will appear automatically.

Select the ActivePropertyList table from the list.

Click "Add".

Do the same for the PropertyDescriptionList.

Click "Close".


3. Both tables should appear with the same join link for EANHotelID as seen in the "Relationships" tab from the join instructions.

If not, go back and join the EANHotelID fields properly.


4. In the ActivePropertyList table pane, double-click the "City" field to add it to the query design dialog.

Do the same for the "StarRating" and "EANHotelID" fields for this table.


5. Double-click the "PropertyDescription" field in the PropertyDescriptionList table pane.

Any additional fields you'd like to see in the final table such as name, address, etc. can be added as well. Only fields that are selected and "checked" will be included in the final table view. To include all fields, double-click the asterick (*) from the desired table.


6. Add the criteria of Like "New York*" to the "City" field to define the query with only properties in this location.
7. Add a criteria of >= 3.0 to the "Star Rating" field to return hotels with 3 stars or greater.
8. Click "Run" to see the results of the query.
9. Click the "Save" icon to save and give the query a name.


Alternative Method: The above queries can also be set up directly as a SQL query string.

Access this view by using either the "View" menu or the right-click menu from within the design window.

SELECT 

ActivePropertyList.StarRating, 
ActivePropertyList.EANHotelID, 
PropertyDescriptionList.PropertyDescription

FROM ActivePropertyList 

LEFT JOIN PropertyDescriptionList 

ON ActivePropertyList.EANHotelID = PropertyDescriptionList.EANHotelID

WHERE (((ActivePropertyList.City) Like "New York*") 

AND ((ActivePropertyList.StarRating)>=3));