need append query SQL help

[Slez via AccessMonster.com]

I have posted a few times regarding this topic and received terrific help! I
am making progress, but am stuck on something once again.

I am creating an append query to import a recordset from another database via
linked tables. I think I have the SELECT, FROM, and WHERE statements pretty
much set, but am unsure how to structure the INSERT INTO statement. I'm
inserting into 3 different tables and have tried brackets, parentheses, comma,
semicolon, "AND", but keep getting a Syntax Error. Here is what I'm trying
to INSERT INTO:

INSERT INTO tblProject ( JobNumber, ProjectName, SiteStreetAddress, SiteCity,
SiteState, SiteZip, GCName, GCContact, GCStreetAddress, GCCity, GCState,
GCZip )
INSERT INTO tblItem ( RoomNumber, ItemNumber, RoomName, ElevationReference,
ProductSummary )
INSERT INTO tblItemDetail ( Quantity, ProductDescription, Markup, QuoteCost )

Any help with how this should be written is greatly appreciated!
Slez

 

[Jeff L]

You cannot insert into multiple table in one query. If you have 3
tables, then you must have 3 insert statements.

 

[Slez via AccessMonster.com]

Just to make sure I understand your reply...
To accomplish this, I would need to create one query for each table that I
wish to be part of the recordset. Assuming this is correct, how do I run all
queries? A macro?
I feel like there's still something I'm missing. Are there any examples of
importing a recordset that spans across multiple tables?
Thanks again for the help!

You cannot insert into multiple table in one query. If you have 3
tables, then you must have 3 insert statements.

I have posted a few times regarding this topic and received terrific help! I
am making progress, but am stuck on something once again.

[quoted text clipped - 19 lines]

Message posted via AccessMonster.com

 

[Jeff L]

Yes you need to create an append query for each table you wish to
append to. You can run them individually if you want or in code. The
code would be Docmd.OpenQuery "YourQueryName"

Just to make sure I understand your reply...
To accomplish this, I would need to create one query for each table that I
wish to be part of the recordset. Assuming this is correct, how do I run all
queries? A macro?
I feel like there's still something I'm missing. Are there any examples of
importing a recordset that spans across multiple tables?
Thanks again for the help!

You cannot insert into multiple table in one query. If you have 3
tables, then you must have 3 insert statements.

I have posted a few times regarding this topic and received terrific help! I
am making progress, but am stuck on something once again.

[quoted text clipped - 19 lines]

Message posted via AccessMonster.com

 

[Slez via AccessMonster.com]

Okay...
The first query works properly, so I created a second query to contend with
the next level of the recordset, and when I view the records in the query, it
returns the correct records (6 total)! I thought I was on my way to success
until I tried to actually Run the query. I then got an error message stating
there were 6 "key violations". All the fields' properties in both databases
are exactly the same. Here is the SQL from this query:

INSERT INTO tblItem ( RoomNumber, ItemNumber, RoomName, ElevationReference,
ProductSummary )
SELECT Item.RoomNumber, Item.ItemNumber, Item.RoomName, Item.
ElevationReference, Item.ProductSummary
FROM Project INNER JOIN (Bid INNER JOIN Item ON (Bid.ProjectID = Item.
ProjectID) AND (Bid.BidNumber = Item.BidNumber) AND (Bid.ProjectName = Item.
ProjectName)) ON (Project.ProjectName = Bid.ProjectName) AND (Project.
ProjectID = Bid.ProjectID)
WHERE (((Project.JobNumber)="065812"));

Here's something that I am thinking may be partially becoming an issue.
"ProjectID" is a unique idendifier in the source database, but once a
JobNumber is assigned and the project is moved to the destination database,
JobNumber becomes the unique identifier. ProjectID does not exist in tblItem.
Do you think that is contributing to my issue? Or do you think my JOIN is
causing it?
Again, thanks for your continued help!

Yes you need to create an append query for each table you wish to
append to. You can run them individually if you want or in code. The
code would be Docmd.OpenQuery "YourQueryName"

Just to make sure I understand your reply...
To accomplish this, I would need to create one query for each table that I

[quoted text clipped - 16 lines]

Message posted via AccessMonster.com

 

[Jeff L]

The key violation caused by:
1. You are not appending a value to the primary key value in the table
and thus the key is null, which you cannot do. The Primary Key of a
table MUST have a value.
2. You are appending records where the key value is duplicated. The
Primary Key MUST be unique.

My guess would be #1 since you are trying to append 6 records, and you
got 6 key violations.

Okay...
The first query works properly, so I created a second query to contend with
the next level of the recordset, and when I view the records in the query, it
returns the correct records (6 total)! I thought I was on my way to success
until I tried to actually Run the query. I then got an error message stating
there were 6 "key violations". All the fields' properties in both databases
are exactly the same. Here is the SQL from this query:

INSERT INTO tblItem ( RoomNumber, ItemNumber, RoomName, ElevationReference,
ProductSummary )
SELECT Item.RoomNumber, Item.ItemNumber, Item.RoomName, Item.
ElevationReference, Item.ProductSummary
FROM Project INNER JOIN (Bid INNER JOIN Item ON (Bid.ProjectID = Item.
ProjectID) AND (Bid.BidNumber = Item.BidNumber) AND (Bid.ProjectName = Item.
ProjectName)) ON (Project.ProjectName = Bid.ProjectName) AND (Project.
ProjectID = Bid.ProjectID)
WHERE (((Project.JobNumber)="065812"));

Here's something that I am thinking may be partially becoming an issue.
"ProjectID" is a unique idendifier in the source database, but once a
JobNumber is assigned and the project is moved to the destination database,
JobNumber becomes the unique identifier. ProjectID does not exist in tblItem.
Do you think that is contributing to my issue? Or do you think my JOIN is
causing it?
Again, thanks for your continued help!

Yes you need to create an append query for each table you wish to
append to. You can run them individually if you want or in code. The
code would be Docmd.OpenQuery "YourQueryName"

Just to make sure I understand your reply...
To accomplish this, I would need to create one query for each table that I

[quoted text clipped - 16 lines]

Message posted via AccessMonster.com

 

[Slez via AccessMonster.com]

#1 makes sense. Now that you said that, I realized that there was nothing in
the "Append To:" box in the query's design view. It now works! I can't
guarantee you won't hear from me again, because I do need to create another
query that appends one more table in the recordset, but I think it's finally
sinking in. The first try at something usually involves alot more head-
scratching.
Thank you for all your help!
Slez

The key violation caused by:
1. You are not appending a value to the primary key value in the table
and thus the key is null, which you cannot do. The Primary Key of a
table MUST have a value.
2. You are appending records where the key value is duplicated. The
Primary Key MUST be unique.

My guess would be #1 since you are trying to append 6 records, and you
got 6 key violations.

Okay...
The first query works properly, so I created a second query to contend with

[quoted text clipped - 35 lines]

Message posted via AccessMonster.com