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.
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.
Code:
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;
Last edited: