PDA

View Full Version : Return multiple results if multiple strings



JGalper
03-21-2013, 05:34 AM
I am working to enhance a sheet for our production team and one of the requirements they provide is that job codes (always 8 characters, always 3 letters followed by 5 numbers) are put in a description field in Access (Linked Table to SQL, Text data type, 255 characters). I have a query in SQL now to extract the data from that field only if the exact description is 8 characters and matches up to the orders table.

They want to be able to put in multiple jobs to this description table (I told them right now to press ALT+ENTER in Access and this will do one job code per "line") and I want to be able to return multiple SQL results, with the only difference being the job code reference in the code.

For example, if the orders table has an order date of 1/1/2013, a PO number of 1234567, a line number of 1 (taken from a details table, inner joined) and the description is ABC12345 (ALT+ENTER) DEF67890, I want the results to be:

SQL Result Line | Order Date | PO Number | Line Number | Job Code
1 | 1/1/2013 | 1234567 | 1 | ABC12345
2 | 1/1/2013 | 1234567 | 1 | DEF67890


Edit: Fixed number of characters in explanation of job code

JGalper
03-21-2013, 10:28 AM
Maybe it would be better to tell them to put the job codes in separated by commas? How would I write the SQL query to separate those into separate results?