Member Avatar for wchitamb

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
  • 136 Views
  • 1 Week Discussion Span
  • Latest Post Latest Post by wchitamb

Dani AI

Generated

Nice progress, — running several append queries from a macro is a pragmatic fix. For a more robust and maintainable solution consider normalizing the repeating columns into a child table (one row per drug/role) linked to the original record by an ID. That avoids fighting unique-key constraints and makes queries, reporting and future changes much easier.

Recommended steps:

  • Create a child table with an AutoNumber primary key, a foreign-key to the source record (ParentID), and fields like ItemType and ItemValue. Do not make ParentID unique.
  • Populate the child table with either a single UNION ALL append query (fast for set-based imports) or a small VBA loop (easier if per-field logic/validation is needed).
  • Remove or adjust any unique index on the destination that prevents multiple child rows per parent. Back up the database before bulk operations.

Example: a set-based append using UNION ALL (replace names with real table/field names):

INSERT INTO TargetChild (ParentID, ItemValue)
SELECT SourceID, Col1 FROM SourceTable WHERE Len(Trim(Nz(Col1,"")))>0
UNION ALL
SELECT SourceID, Col2 FROM SourceTable WHERE Len(Trim(Nz(Col2,"")))>0
UNION ALL
SELECT SourceID, Col3 FROM SourceTable WHERE Len(Trim(Nz(Col3,"")))>0;

Example: a VBA approach for more control (pseudo-code):

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("SourceTable")
Do While Not rs.EOF
  If Len(Trim(Nz(rs!Col1,"")))>0 Then
    db.Execute "INSERT INTO TargetChild (ParentID,ItemValue) VALUES (" & rs!SourceID & ",'" & Replace(rs!Col1,"'","''") & "')", dbFailOnError
  End If
  rs.MoveNext
Loop

Notes: UNION ALL is generally faster for large datasets; VBA gives per-field validation and error handling. Test on a copy, verify indexes and referential integrity, and add appropriate indexes on the child table after import.

Member Avatar for wchitamb

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.