SQL Append Query in Access Help Needed

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

Other Forums

MS Access

SQL Append Query in Access Help Needed

Author

Topic

tjbishop2
Starting Member

5 Posts

Posted - 2009-03-15 : 20:37:18

I'm trying to insert X # of records in a table based on a field in the table.

This is what I have so far, but need it to insert 6 records in table two for every one record from table 1 where the state = "PA".

Any suggestions would be welcomed! Thank you!

INSERT INTO [Table 2] ( Contract, Unit, [Address 1], [Address 2], City, State, Zip )

SELECT [Table 1].Contract, [Table 1].Unit, [Table 1].[Address 1], [Table 1].[Address 2], [Table 1].City, IIf([State]="PA" ,[State]) AS State_, [Table 1].Zip

FROM [Table 1]

WHERE (((IIf([State]="PA",[State])) Is Not Null));

matty
Posting Yak Master

161 Posts

Posted - 2009-03-16 : 01:18:46

INSERT INTO [Table 2] ( Contract, Unit, [Address 1], [Address 2], City, State, Zip )
SELECT [Table 1].Contract, [Table 1].Unit, [Table 1].[Address 1], [Table 1].[Address 2],[Table 1].City, [Table 1].[State], [Table 1].Zip
FROM [Table 1]
WHERE State = 'PA'

tjbishop2
Starting Member

5 Posts

Posted - 2009-03-16 : 10:27:53

I must be missing it, but what in this code makes it insert 6 duplicate records?

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-16 : 11:27:41

You could run it 6 times, Or..


INSERT INTO [Table 2] ( Contract, Unit, [Address 1], [Address 2], City, State, Zip )

SELECT 
	[Table 1].Contract, 
	[Table 1].Unit, 
	[Table 1].[Address 1], 
	[Table 1].[Address 2], 
	[Table 1].City, 
	IIf([State]="PA" ,[State]) AS State_, [Table 1].Zip
FROM 
	[Table 1] 
	CROSS JOIN (select 1 col union select 2 union select 3 union select 4 union select 5 union select 6)abc

WHERE State = 'PA'

tjbishop2
Starting Member

5 Posts

Posted - 2009-03-16 : 13:03:05

I cannot get this to work. I get an error that states there is a syntax error in the from clause. What does the )abc signify? Please bear with me as I learn how to do this.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-16 : 13:56:47

Its a derived table. Are you running this on sql server?

tjbishop2
Starting Member

5 Posts

Posted - 2009-03-16 : 18:21:05

I'm running this in Access. Am I in the wrong place for help?

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-16 : 18:24:56

yes. You might want to post it at http://www.sqlteam.com/forums/forum.asp?FORUM_ID=3 for faster and correct replies.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2009-03-17 : 05:59:44

moved from new to sql server forum

___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.5 out!

tjbishop2
Starting Member

5 Posts

Posted - 2009-03-17 : 19:18:29

Thank you! I'm sorry for posting in the wrong place. With that said, does anyone have an idea?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources