1

I am looking to see if there is a way with VBA to take data from multiple columns and translate it into multiple rows instead. I am working with imported data from an Excel spreadsheet.

There is a group of 10 columns and several groups of 2 columns with the same (not duplicate) information; my end goal is to have a separate row for each record from each column.

Sample Data:

Company ID (Not the primary key) License 1 License 2 License 3 etc.
Company_1234 A B C-10
Company_2345 B
Company_3456 C-35 B C-05 D-39
Company ID (Not the primary key) First Name Last Name Office Number Alt. First Name Alt. Last Name Alt Phone Number
Company_1234 John Smith 909-555-1234 Joe Schmo 909-555-2345
Company_2345 Mary Jones 818-555-7894
Company_3456 Kevin Peters 626-555-9632 Steve Wonderful 626-555-7412

I tried an append query, but received the error "Duplicate Destination Output". Here is the SQL of the query built using the visual builder:

INSERT INTO tbl_NewContact ( [Company ID], [First Name], [Last Name], [First Name], [Last Name] )
SELECT TEST_qry_ContactList.[Company ID], TEST_qry_ContactList.[First Name],  TEST_qry_ContactList.[Last Name], TEST_qry_ContactList.[Alt First Name], TEST_qry_ContactList.[Alt Last Name]
FROM TEST_qry_ContactList;

The reason for doing this is to break apart the imported spreadsheet into multiple tables. I have a Company table (tbl_Company) with the Company Name and Company ID. I have another table for Contacts (tbl_Contacts) that joins with tbl_Company at the Company ID with a one-to-many relationship. I have another table (tbl_CoLic) that joins with tbl_Company at the Company ID with a one-to-many relationship.

I would like to see the sample data above broken apart in this manner:

Company ID License
Company_1234 A
Company_1234 B
Company_1234 C-10
Company_2345 B
Company_3456 C-35
Company_3456 B
Company_3456 C-05
Company_3456 D-39
Company ID First Name Last Name Phone Number
Company_1234 John Smith 909-555-1234
Company_1234 Joe Schmo 909-555-2345
Company_2345 Mary Jones 818-555-7894
Company_3456 Kevin Peters 626-555-9632
Company_3456 Steve Wonderful 626-555-7412

I would prefer not to build a bunch of separate append queries for each group of data points, but if that is what it will take, I will do it.

I also saw there could be a way with a SQL database and SQL coding, but at this point in time this database will reside solely within Access and will need to utilize code that can be written in Access.

Thank you for the assistance, please let me know if further clarifications are required.

Andre
  • 26,751
  • 7
  • 36
  • 80
E.Meier
  • 25
  • 2

1 Answers1

1

Use UNION query to rearrange fields to normalized structure. There is a limit of 50 SELECT lines. First SELECT line determines field names and data types. There is no designer for UNION - must type or copy/paste in SQLView.

For licenses:

SELECT [Company ID], License1 AS License FROM table
UNION SELECT [Company ID], License2 FROM table
UNION SELECT [Company ID], License3 FROM table;

For contacts:

SELECT [Company ID], [First Name], [Last Name], [Office Number] AS Phone, True AS Primary FROM table
UNION SELECT [Company ID], [Alt. First Name], [Alt. Last Name], [Alt. Phone Number], False FROM table;

Use those datasets in other queries or as sources for records inserted to new tables.

Strongly advise you eliminate spaces and punctuation from field naming convention.

June7
  • 19,874
  • 8
  • 24
  • 34