Morning I have a question here
How do I go about it in Access say if I want to split a line (row) into different lines like these fields?
Drug name1 Drug name2 Drug name3 Drug name4 Drug name5 Drug name6
So what I want is to split the Drug Names into different lines but still maintain some of the common info say name of the person who has put that down etc I have omitted some fields but put a few 2 get the idea
The other one
Say when I just want to insert a role which is populated can I use OR and a condition 2 say where not null (picking just one selection – which ever is populated)
Job role 1 Job role 2 Job role 3 Job role 4 Job role 5 Job role 6
N:B all this will be in one query tho?? Not a separate one!! That’s where my dilemma kicks in, i tried to put role1 x 6 on the insert table and the 1- 6 on the from table but it wouldnt allow duplicates on the insert table.
here is my code for a single 1 to one which works fiine but now i hav additional fileds that i want to start on a new line (thats if they r populated)
INSERT INTO [INFORMATION FROM THE PORTAL] ( RespondentID, [Unique Drug Identifier], [Date], [Requesters Name], [Requesters Organisation], [Requesters e-mail address], [Requesters telephone number], [Requesters job role 1], [Add drug - Generic name of drug], [Add drug - Brand/Trade name of drug], [Add drug - UK Launch date (estimated)], [Add drug - Clinical indications (symptoms)], [Add drug - BNF Category (if known)], [Add drug - Estimated cost per patient per year], [Add drug - Any relevant guidance], [Add drug - Justification for adding the drug], [Add drug - Any other supporting information], [Remove drug - Generic name of drug], [Remove drug - Brand/Trade name of drug], [Remove drug - UK Launch date (estimated)], [Remove drug - Clinical indications (symptoms)], [Remove drug - BNF Category (if known)], [Remove drug - Estimated cost per patient per year], [Remove drug - Any relevant guidance], [Remove drug - Justification for removing the drug], [Remove drug - Any other supporting information] )
SELECT [TABLE (0) SURVEY MONKEY RAW DATA - HOLDING TABLE].RespondentID, [TABLE (0) SURVEY MONKEY RAW DATA - HOLDING TABLE].[Unique Drug Identifier], [TABLE (0) SURVEY MONKEY RAW DATA - HOLDING TABLE].Date, [TABLE (0) SURVEY MONKEY RAW DATA - HOLDING TABLE].[Requesters Name], [TABLE (0) SURVEY MONKEY RAW DATA - HOLDING TABLE].[Requesters Organisation], [TABLE (0) SURVEY MONKEY RAW DATA - HOLDING TABLE].[Requesters e-mail address], [TABLE (0) SURVEY MONKEY RAW DATA - HOLDING TABLE].[Requesters telephone number], [TABLE (0) SURVEY MONKEY RAW DATA - HOLDING TABLE].[Requesters job role 1], [TABLE (0) SURVEY MONKEY RAW DATA - HOLDING TABLE].[Add drug - Generic name of drug], [TABLE (0) SURVEY MONKEY RAW DATA - HOLDING TABLE].[Add drug - Brand/Trade name of drug], [TABLE (0) SURVEY MONKEY RAW DATA - HOLDING TABLE].[Add drug - UK Launch date (estimated)], [TABLE (0) SURVEY MONKEY RAW DATA - HOLDING TABLE].[Add drug - Clinical indications (symptoms)], [TABLE (0) SURVEY MONKEY RAW DATA - HOLDING TABLE].[Add drug - BNF Category (if known)], [TABLE (0) SURVEY MONKEY RAW DATA - HOLDING TABLE].[Add drug - Estimated cost per patient per year], [TABLE (0) SURVEY MONKEY RAW DATA - HOLDING TABLE].[Add drug - Any relevant guidance], [TABLE (0) SURVEY MONKEY RAW DATA - HOLDING TABLE].[Add drug - Justification for adding the drug], [TABLE (0) SURVEY MONKEY RAW DATA - HOLDING TABLE].[Add drug - Any other supporting information], [TABLE (0) SURVEY MONKEY RAW DATA - HOLDING TABLE].[Remove drug - Generic name of drug], [TABLE (0) SURVEY MONKEY RAW DATA - HOLDING TABLE].[Remove drug - Brand/Trade name of drug], [TABLE (0) SURVEY MONKEY RAW DATA - HOLDING TABLE].[Remove drug - UK Launch date (estimated)], [TABLE (0) SURVEY MONKEY RAW DATA - HOLDING TABLE].[Remove drug - Clinical indications (symptoms)], [TABLE (0) SURVEY MONKEY RAW DATA - HOLDING TABLE].[Remove drug - BNF Category (if known)], [TABLE (0) SURVEY MONKEY RAW DATA - HOLDING TABLE].[Remove drug - Estimated cost per patient per year], [TABLE (0) SURVEY MONKEY RAW DATA - HOLDING TABLE].[Remove drug - Any relevant guidance], [TABLE (0) SURVEY MONKEY RAW DATA - HOLDING TABLE].[Remove drug - Justification for removing the drug], [TABLE (0) SURVEY MONKEY RAW DATA - HOLDING TABLE].[Remove drug - Any other supporting information]
FROM [TABLE (0) SURVEY MONKEY RAW DATA - HOLDING TABLE];