Insert Query

I have two tables ....

I need to insert into tableB for any item that does not have a PartID that is in tableA

The tables are identical except that tableB has 2 additional fields ... DateUpdate and ReasonUpdated.  For date updated i need to enter todays date and time and for ReasonUpdated i  need to put in the text 'new record'
vbnetcoderAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Try this:

Currentdb.Execute "INSERT INTO TableB (Col1, Col2, Col3, Col4, Col5) SELECT Col1, Col2, Col3, Now, 'New Record' FROM TableA LEFT OUTER JOIN TableB ON PartID=PartID WHERE TableA.PartID IS NULL"

Obviously you'd have to change Col1, Col2, etc to match the columns in your table.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
vbnetcoderAuthor Commented:
it works except when i add in Now, 'New Record'

it's not happy because the number of query values and destination are not the same
0
Dale FyeCommented:
Actually, from the description, I think Scott has the where clause wrong.

INSERT INTO TableB (PartID, Col2, Col3, DateUpdated, ReasonUpdated)
SELECT A.PartID, A.Col1, A.Col2, A.Col3, Now(), "New Record"
FROM TableA as A LEFT OUTER JOIN TableB as BON A.PartID=B.PartID
WHERE B.PartID IS NULL

In this case you want to find records in TableA, not in TableB
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

vbnetcoderAuthor Commented:
Dale, i noticed that and made the needed update

Could anybody tell how to add Now, 'New Record'  (these are fields not in the source table but are in
the table being inserted into)
0
Dale FyeCommented:
The syntax that both Scott and I provided should insert the data into those fields in Table B.

Are you trying to do this in the query grid, or via VBA by writing the SQL string and then using the Execute method?
0
vbnetcoderAuthor Commented:
This is the basic syntax that i am using

Currentdb.Execute "INSERT INTO TableB (Col1, Col2, Col3, Col4, Col5) SELECT Col1, Col2, Col3, Now, 'New Record' FROM TableA LEFT OUTER JOIN TableB ON PartID=PartID WHERE TableA.PartID IS NULL"

All the fields Col1, Col2, Col3, Col4, Col5 ARE in my source table.  Also i am trying to insert ADDITIONAL RECORDS that are not in my source table the data being , Now AND 'New Record'

SO, i get and error because it's not happy because the number of query values and destination are not the same
0
Dale FyeCommented:
Try this:

Dim strSQL as string
strSQL = "INSERT INTO TableB (PartID, Col2, Col3, DateUpdated, ReasonUpdated) " _
             & "SELECT A.PartID, A.Col2, A.Col3, #" & Now() & "#, 'New Record' " _
             & "FROM TableA as A LEFT OUTER JOIN TableB as B ON A.PartID=B.PartID " _
             & "WHERE B.PartID IS NULL"
debug.print strsql
Currentdb.Execute strsql, dbfailonerror
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Can you show us the exact syntax you're using, with the fieldnames and all??

Thanks for picking that up Dale!!
0
vbnetcoderAuthor Commented:
ty
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.