Table Query Help

[kebab]

Here is a problem I am having with a query.

I have a table with the following data

Fields: "date" "operator"

12/12/2007 American Airlines
01/14/2006 American Airlines / Private
06/30/1956 American Airllines / United Air Lines
01/02/1930 USAir
02/02/2020 United Air Lines
04/04/1940 Northwest Airlines
05/05/1950 Air France

I want to make a query that will make a new table. I want the query to recognize the "/" character in the operator field and break that record into two seperate records so the table will look like:

Fields: "date" "operator"

12/12/2007 American Airlines
01/14/2006 American Airlines
01/14/2006 Private
06/30/1956 American Airllines
06/30/1956 United Air Llines
01/02/1930 USAir
02/02/2020 United Air Lines
04/04/1940 Northwest Airlines
05/05/1950 Air France

Maybe there is no way to do this. But if there is, would appreciate any help.

Richard

 

[ByteMyzer]

Try something like:

SELECT T1.* INTO MyTable2 FROM
 (
  SELECT T1.Date, T1.Operator
  FROM MyTable AS T1
  WHERE InStr(1, T1.Operator, '/') = 0

  UNION SELECT T1.Date, Trim(Left(T1.Operator, InStr(1,T1.Operator,'/') - 1))
  FROM MyTable AS T1
  WHERE InStr(1, T1.Operator, '/') > 0

  UNION SELECT T1.Date, Trim(Mid(T1.Operator, InStr(1,T1.Operator,'/') + 1))
  FROM MyTable AS T1
  WHERE InStr(1, T1.Operator, '/') > 0
 )
AS T1;

 

[kebab]

Unfortunately, I am somewhat new to Access. Can you direct me step by step where to put the code. Thanks.

 

[ByteMyzer]

* Click on the Queries tab.

* Click on the New button.

* Select Design View and click [OK].

* At the Show Table dialog, click on [Close].

* Click on the [SQL] view button.

* Copy the SQL statement from my previous post and paste it into the SQL view (make sure to change the table names and field names to the actual names).

* Save the query, then Run it.

 

[kebab]

I ran the query and received a compile error.
"compile error, in query expression InStr(1, T1.Operator, '/') = 0"

My starting table is called Original
My ending table is called Modified

The two Fields are "Operator" and "Date"

Here is the whole text I copied into the SQL

SELECT T1.* INTO Modified FROM
(
SELECT T1.Date, T1.Operator
FROM Original AS T1
WHERE InStr(1, T1.Operator, '/') = 0

UNION SELECT T1.Date, Trim(Left(T1.Operator, InStr(1,T1.Operator,'/') - 1))
FROM Original AS T1
WHERE InStr(1, T1.Operator, '/') > 0

UNION SELECT T1.Date, Trim(Mid(T1.Operator, InStr(1,T1.Operator,'/') + 1))
FROM Original AS T1
WHERE InStr(1, T1.Operator, '/') > 0
)
AS T1;

 

[ByteMyzer]

Look at your Visual Basic References, and uncheck any that are missing.

 

[kebab]

Would it be possible for me to send you the test Access file I am working on?

 

[ByteMyzer]

You can make a ZIP file of your test database, reply to this post and use the Manage Attachments feature.

 

[kebab]

OK. I have attached the zip file test.zip which contains test.mdb.

All I want to do is develop a query that will take "Original" table and make a new table that will be called and look like "Modified" table.

Thanks.

 

[ByteMyzer]

I checked the file, and obviously you did not follow my instructions.

I gave you instructions for copying the SQL statement and pasting it into the SQL view of a new Query, but instead, you created a new Module, and pasted it there.

* Modules are for Visual Basic code
* Queries are for SQL code

If you can't be bothered to follow instructions meant to help you, you can not reasonably expect the proposed solution to work. Try again, following the instructions this time, and see if it works for you.

 

[kebab]

Wow! Worked like a charm. Thank you for all your time and expertise.

 

[kebab]

I'm so sorry to bother you again.

As I wrote above, following your instructions, the query worked perfectly in test.mdb.

However, when I applied it to may real database aviation.mdb (changing the table names accordingly) it would not work. I must have worked on it for five hours trying to see why it would work on one database and not the other.

I have attached both the test.mdb that works and aviation.mdb that doesn't work. The Query in both are called MakeOperatorsList.

Could you please take a look and see what I am doing wrong.

 

[ByteMyzer]

You have a missing Visual Basic reference in your aviation.mdb file:

MISSING: .Swiftsoft CDReader ActiveX Library 2.0

Uncheck this reference and your query should work fine.

 

[kebab]

Thank you. That did the trick.