Create new table from two other tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Create new table from two other tables

Author

Topic

rob41
Yak Posting Veteran

67 Posts

Posted - 2009-12-17 : 13:06:04

I am trying to run a query that was created in access 07, it works in access and i'm trying to get it to work in SQL Management Studio 05. I get the following error code:
Msg 2705, Level 16, State 3, Line 2
Column names in each table must be unique. Column name 'Load ID' in table 'tblGL Codes (Determine GL 2)' is specified more than once.

-sql-

SELECT [tblGL code percent cost allocation].[Load ID],
Min([tblGL code percent cost allocation].[GL Code])AS [GL 2],
Min([tblGL code percent cost allocation].[sumofGL Perc of Cost]) AS [GL 2 perc of cost],
[tblGL Codes (Determine GL 1)].[Load ID],
[tblGL Codes (Determine GL 1)].[GL 1],
[tblGL Codes (Determine GL 1)].[GL 1 perc of cost]
INTO [tblGL Codes (Determine GL 2)]
FROM [tblGL code percent cost allocation]
LEFT JOIN [tblGL Codes (Determine GL 1)]
ON ([tblGL code percent cost allocation].[Load ID] = [tblGL Codes (Determine GL 1)].[Load ID])
AND ([tblGL code percent cost allocation].[GL Code] = [tblGL Codes (Determine GL 1)].[GL 1])
GROUP BY [tblGL code percent cost allocation].[Load ID],
[tblGL Codes (Determine GL 1)].[Load ID],
[tblGL Codes (Determine GL 1)].[GL 1],
[tblGL Codes (Determine GL 1)].[GL 1 perc of cost]
HAVING ((([tblGL Codes (Determine GL 1)].[Load ID]) IS NULL)
AND (([tblGL Codes (Determine GL 1)].[GL 1]) IS NULL)
AND (([tblGL Codes (Determine GL 1)].[GL 1 perc of cost]) IS NULL))
ORDER BY [tblGL code percent cost allocation].[Load ID],
Min([tblGL code percent cost allocation].[GL Code]);

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2009-12-17 : 13:13:15

SELECT [tblGL code percent cost allocation].[Load ID],
Min([tblGL code percent cost allocation].[GL Code])AS [GL 2],
Min([tblGL code percent cost allocation].[sumofGL Perc of Cost]) AS [GL 2 perc of cost],
[tblGL Codes (Determine GL 1)].[Load ID] as [Some other name],
[tblGL Codes (Determine GL 1)].[GL 1],
[tblGL Codes (Determine GL 1)].[GL 1 perc of cost]
INTO [tblGL Codes (Determine GL 2)]
FROM [tblGL code percent cost allocation]
LEFT JOIN [tblGL Codes (Determine GL 1)]
ON ([tblGL code percent cost allocation].[Load ID] = [tblGL Codes (Determine GL 1)].[Load ID])
AND ([tblGL code percent cost allocation].[GL Code] = [tblGL Codes (Determine GL 1)].[GL 1])
GROUP BY [tblGL code percent cost allocation].[Load ID],
[tblGL Codes (Determine GL 1)].[Load ID],
[tblGL Codes (Determine GL 1)].[GL 1],
[tblGL Codes (Determine GL 1)].[GL 1 perc of cost]
HAVING ((([tblGL Codes (Determine GL 1)].[Load ID]) IS NULL)
AND (([tblGL Codes (Determine GL 1)].[GL 1]) IS NULL)
AND (([tblGL Codes (Determine GL 1)].[GL 1 perc of cost]) IS NULL))
ORDER BY [tblGL code percent cost allocation].[Load ID],
Min([tblGL code percent cost allocation].[GL Code]);

Ryan Randall - Yak of all trades
Solutions are easy. Understanding the problem, now, that's the hard part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources