Combining fields from different rows?

[Guest]

I am importing data from Excel into a staging table. Then I want to append
the records in the staging table to my main table. However...
There are many records I am importing that have the same primary key. It
could be 2 records with the same primary key, or up to 10. The primary key
is called "Record ID" and the record data is the same on the duplicates
except for in 2 fields. What I would like to do is merge the duplicate rows
together and also merge the different data in the fields into one.

For example, here's what I start with:
1-22345A Oceanside CA 50% Apartments
1-22345A Oceanside CA 20% Condos
1-22345A Oceanside CA 30% Homes

Here's what I would like to end up with:
1-22345A Oceanside CA 50%/20%/30% Apartments/Condos/Homes

How can I do this? I have already built a query that finds all of the
duplicates. I have 500 records that have more than one instance of the same
primary key. How do I create a new table and merge them into one line like
my example above? Then I will want to delete the 500 records from my staging
table and then append the consolidated rows back into my staging table.

Thanks!!!!

 

[Guest]

A better table structure would be --
Code City State Apartments Condos Homes
1-22345A Oceanside CA 50% 20% 30%

Build an append query to create a record for each Code. Then run update
queries to fill in the other fields.

 

[Guest]

I can't change the table structure... the data I used was just made up to
illustrate the type of merge I need to do. There could be hundreds of
different items that could be put in the "Apartments, Condos, Homes" field.

Could you help me figure out how to make an append query to merge all of the
records to one line? I don't understand. Thanks!

 

[Guest]

Try using a crosstab query.

TRANSFORM Sum(Table15.Percent) AS SumOfPercent
SELECT Table15.Code, Table15.City, Table15.State
FROM Table15
GROUP BY Table15.Code, Table15.City, Table15.State
PIVOT Table15.Type;