Union Query Example One

Selecting Rows before and after a criteria result

I have a table with two fields (columns). The first field contains a unique ordinal number for each row. The second field contains some text.

I've created a query that shows only the rows that have the word "chair" in them (in the second field). Let's say there are only 2 such rows: row 4 and row 9.

How can I create such a query that would also show me the two previous rows and one after? In the example above it would be the following rows: 2, 3, 4, 5, 7, 8, 9, and 10.

Solution

To do what you ask try the following:

 Create a query with the above table, with the ordinal field, and the "chair" filtered field.

 In the third column type the following in the first row:

 PlusOne: [ID]+1

 In the next column, the following:

 MinusOne: [ID]-1

 Then, finally, in the next column:

 MinusTwo: [ID] - 2

 Exchange the [ID] field to the name of your ordinal field.

 Close and save the query.

 Create a new query - but do not follow the wizard. Choose to create the query in Design view. Add the above query when prompted to do so and close the add query/table window.

 On the toolbar, click on Query > SQL Specific > Union.

 Type the following in the blank window provided:

 SELECT ID FROM OneBeforeAfter1

UNION SELECT PlusOne FROM OneBeforeAfter1

UNION SELECT MinusOne FROM OneBeforeAfter1

UNION SELECT MinusTwo FROM OneBeforeAfter1

ORDER BY ID;

Once again, changing the ID field to your ordinal field.

Then, use the results of this query to build another query that will display other fields, if needed.

Do you like this tip? Subscribe to my free Newsletter to receive tips via email. Click Here to subscribe.