[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Excel to Access Append Query

Posted on 2011-04-26
4
Medium Priority
?
497 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
0
Comment
Question by:ellisito
  • 2
4 Comments
 
LVL 15

Expert Comment

by:Simon Ball
ID: 35473737
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


0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35474716
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
0
 
LVL 44

Accepted Solution

by:
GRayL earned 2000 total points
ID: 35478121
If you remember a UNION query establishes the field names from the query you can get a more generalized field name with

SELECT AccountNo, UtilityCo1 AS UtilityCo, CustNo1 AS CustNo FROM myTable
UNION ALL
SELECT AccountNo, UtilityCo2, CustNo2 FROM myTable
UNION ALL
SELECT AccountNo, UtilityCo3, CustNo3 FROM myTable;

When you get it to give you the desired results then you can move the results into a new table:

SELECT * INTO newTable FROM (
SELECT AccountNo, UtilityCo1 AS UtilityCo, CustNo1 AS CustNo FROM myTable
UNION ALL
SELECT AccountNo, UtilityCo2, CustNo2 FROM myTable
UNION ALL
SELECT AccountNo, UtilityCo3, CustNo3 FROM myTable) AS newTable;
0
 
LVL 44

Expert Comment

by:GRayL
ID: 36197808
Thanks, glad to help.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question