Cleaning up table with SQL Query

[NikToo73]

One of my colleagues is having issues with the following bit of code. Not quite sure how he came up with is, but the problem is as follows:

In a table, there's a column, "Siebel or Sales Ref". Some of these will have a long sales reference number or an ID from a Siebel sales system (pants), in the format 1-XXXXXXX. The problem is that some have two IDs, separated by a forward slash (/), so like 1-ABC1234/1-DEF5678. He wants to go through the data, and where there's a double ID, split it out, and create a new row with the second ID.

I'm not sure this can be done in Access, I would have done a loop in Excel, but it's his database.

INSERT INTO ActualBaseData
SELECT *
FROM (SELECT 'ITQ ID', Deadline, Lot, 'Bid Progress', 'Framework Type', 'Tender Type', 'Siebel or Sales Ref'
  FROM TempBaseData
  WHERE InStr('Siebel or Sales Ref', '/') = 0
  UNION ALL
   SELECT Left('Siebel or Sales Ref', InStr('Siebel or Sales Ref',  '/') - 1), 'ITQ ID', Deadline, Lot, 'Bid Progress', 'Framework Type',  'Tender Type'
  FROM TempBaseData
  WHERE InStr('Siebel or Sales Ref', '/') > 0
  UNION ALL
   SELECT Mid('Siebel or Sales Ref',InStr('Siebel or Sales Ref', '/') + 1), 'ITQ ID',  Deadline, Lot, 'Bid Progress', 'Framework Type', 'Tender Type'
  FROM TempBaseData
  WHERE InStr('Siebel or Sales Ref', '/') > 0)  AS CleanedUp;

 

[namliam]

And what issues is he having?

Only thing I can see wrong is the fact that he is using ' around his field names, instead of [] around them
i.e.
SELECT 'ITQ ID', Deadline, Lot, 'Bid Progress'
Should be
SELECT [ITQ ID], Deadline, Lot, [Bid Progress]
or very strictly
SELECT [ITQ ID], [Deadline], [Lot], [Bid Progress]

Other than that, seems like it should work, might help if you upload some sample records to look at if you still have problems. Either in a text file or actual database.

 

[NikToo73]

The single quotes are from me, he didn't have any... Thanks, will try it.

 

[NikToo73]

Still get "Data type mismatch in criteria expression".

 

[namliam]

Hmz, didnt think to check that but he is abusing the Instr function... Its syntax is wrong.

It should be instr(1,Yourfield,"/")

Not sure if this is at the root of your trouble.... but its wrong none the less

Also it is EMPERATIVE that all queries in the Union all have the same amount of columns and the same column in the same place, which at the moment is not true either.

 

[NikToo73]

Thanks, that actually runs and makes the table. However... The second half of the ID (after the /) isn't pulled through and the ID field is blank for those. Almost there...

 

[NikToo73]

Solved it! Thanks again.