We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Excel to Access Append Query

Medium Priority
511 Views
Last Modified: 2012-05-11
I have an excel file that has been converted into a table. This table has one row with 3 fields (account# [unique] UtilityCo1, UtilityCo2, UtilityCo3, CustNo1, CustNo2 & CustNo3. I need to append this to a table where each account # is a separate record with one UtilityCo and one CustNo. When I try to do an append query I get Duplicate Output Destination. I would like for the query to create a new record with the same account # for each different utility co.

Thanks
Comment
Watch Question

Simon BallChief information Officer

Commented:
you could copy and past the data in excel using paste special, transpose... then re-import so you have multiple records...

sounds like you need to write seperate queries for each of the 3 util/cust pairings?

insert into targettable
select UtilityCo1,  CustNo1
from importeddatatable

insert into targettable
select UtilityCo2,  CustNo2
from importeddatatable

insert into targettable
select UtilityCo3,  CustNo3
from importeddatatable


CERTIFIED EXPERT
Top Expert 2016

Commented:
you can use a union query

select A.* from
(
select accountNo, UtilityCo1,  CustNo1 from  temptable
union all
select accountNo, UtilityCo2,  CustNo2 from  temptable
union all
select accountNo, UtilityCo3,  CustNo3 from  temptable
) as A

you can make a new table out of the union query
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Commented:
Thanks, glad to help.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.