0

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];
1
Contributor
1
Reply
2
Views
7 Years
Discussion Span
Last Post by wchitamb
0

Well i have managed to achieve what i wanted to do tho i m not sure if thats trhe only way of doing it.

i have put down some Queries (Insert into x5) and then do one maro that picks all of them up at a go and wow! they all got split

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.