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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

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
Dale FyeOwner, Developing Solutions LLCCommented:
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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)
Dale FyeOwner, Developing Solutions LLCCommented:
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?
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
Dale FyeOwner, Developing Solutions LLCCommented:
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
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!!
vbnetcoderAuthor Commented:
ty
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.