Combine Multiple Rows into One Row

Question

I have an Excel spreadsheet with multiple records for a company.  The name, address, etc is all the same.  The difference is an expired date.  Each individual row has owner expired date then manager expired date then business expired date then insurance expired date.  

Company Name	Address	City	State	Zip	Type	Expired
Comp A	123 St	Best	NY	12345	Owner	5/1/2016
Comp A	123 St	Best	NY	12345	Manager	5/1/2016
Comp A	123 St	Best	NY	12345	Business	5/23/2016
Comp A	123 St	Best	NY	12345	Insurance	12/31/2016

I imported the data into a database table with hopes of being able to have it changed to the table below, but can't get it to work.

Company Name	Address	City	State	Zip	Owner Expired	Manager Expired	Business Expired	Insurance Expired
Comp A	123 St	Best	NY	12345	5/1/2016	5/1/2016	5/23/2016	5/23/2016

Answer 1

You could try this cross tab query:

Create a new query using Query design mode. Then switch to SQL view and paste the following query into the SQL window.

Replace "YourTable" with the actual table name:

---

TRANSFORM Last(YourTable.[Expired]) AS LastOfExpired

SELECT YourTable.[Company Name], YourTable.[Address], YourTable.[City], YourTable.State, YourTable.Zip

FROM YourTable

GROUP BY YourTable.[Company Name], YourTable.[Address], YourTable.[City], YourTable.State, YourTable.Zip

PIVOT YourTable.[Type];

---

This will return the last instance of the expired dates. I f you want the first set change the last function to First. (You can also use Min or Max.

Comp A	123 St	Best	NY	12345	5/23/2016	12/31/2016	5/1/2016	5/1/2016

Answer 2

While AHR's solution works, it doesn't address the proper structure for your tables. 

Please note that you should have three tables here:

tblCompany

CompanyID Primary Key

CompanyName

StreetAddress

City

State

ZIP

tblExpirations

ExpirationID (PK Autonumber)

CompanyID (FK)

ExpTypeID (FK)

ExpDate

tluExpType

ExpTypeID (PK autonumber)

ExpType

You would then create a query to join the last two tables on ExpTypeID and pulling CompanyID, ExpType and ExpDate. You would then use that query in a Crosstab query to get the ExpDates per company and ExpType. Finally, join your Crosstab query to tblcompany to pull the company name and address info.

The point here is the proper way to store the expiration dates is in a separate table. where each record represents a date defined by company and type. You can then use a crosstab query to DISPLAY the data in the way you want it.

Answer 3

THANKS AHG!

This is spot on and works great!!!! :)

Answer 4

Unfortunately, the information in the link above does not do what I have asked to get done.

Still need help on this.

Answer 5

See here

http://allenbrowne.com/func-concat.html