Append query

[Niniel]

Hello,

I have three related tables, A, B and C, with both A and B being in a
1-to-many relationship with C. A-> C <- B
I want to populate table C by taking one record [ID] from table A and many
records [IDs] from table B, and have in table C - which contains fields with
FKs from both table A and B's PK IDs - a number of records with one and the
same FK from table A, but all the different FKs from table C.

table A - PK ID A: 1

table B - PK ID B: 1/2/3

=> table C: FK A: 1/1/1
FK B: 1/2/3

In other words, if table B has 10 records, I need table C also to have 10
records, but each with the same FK from table A.

I think appending will do this, so I created an Append query. Unfortunately,
it only wants to append 0 records, so I must be missing something.

INSERT INTO tblC ( aID, bID )
SELECT tblA.aID, tblB.bID
FROM tblB INNER JOIN (tblA INNER JOIN tblC ON tblA.aID = tblC.aID) ON
tblB.bID = tblC.bID;

 

[Douglas J. Steele]

What happens if you strictly run

SELECT tblA.aID, tblB.bID
FROM tblB INNER JOIN (tblA INNER JOIN tblC ON tblA.aID = tblC.aID) ON
tblB.bID = tblC.bID;

I'm assuming 0 rows are returned.

Strikes me that you don't need tblC in that subquery at all:

SELECT tblA.aID, tblB.bID
FROM tblA, tblB

Including tblC means that the query is going to fail if aID doesn't already
exist in tblC.

 

[Niniel]

The code I posted was created by Access when I used the Query builder, I just
switched to SQL view, copied it and pasted it here hoping that it might help
illustrate my situation.
The reason tblC shows up in there is, I suppose, because of the
relationships between the tables.

What happens if you strictly run

SELECT tblA.aID, tblB.bID
FROM tblB INNER JOIN (tblA INNER JOIN tblC ON tblA.aID = tblC.aID) ON
tblB.bID = tblC.bID;

I'm assuming 0 rows are returned.

Strikes me that you don't need tblC in that subquery at all:

SELECT tblA.aID, tblB.bID
FROM tblA, tblB

Including tblC means that the query is going to fail if aID doesn't already
exist in tblC.



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hello,

I have three related tables, A, B and C, with both A and B being in a
1-to-many relationship with C. A-> C <- B
I want to populate table C by taking one record [ID] from table A and many
records [IDs] from table B, and have in table C - which contains fields
with
FKs from both table A and B's PK IDs - a number of records with one and
the
same FK from table A, but all the different FKs from table C.

table A - PK ID A: 1

table B - PK ID B: 1/2/3

=> table C: FK A: 1/1/1
FK B: 1/2/3

In other words, if table B has 10 records, I need table C also to have 10
records, but each with the same FK from table A.

I think appending will do this, so I created an Append query.
Unfortunately,
it only wants to append 0 records, so I must be missing something.

INSERT INTO tblC ( aID, bID )
SELECT tblA.aID, tblB.bID
FROM tblB INNER JOIN (tblA INNER JOIN tblC ON tblA.aID = tblC.aID) ON
tblB.bID = tblC.bID;

 

[Douglas J. Steele]

I suspect, then, that you didn't create the query correctly.

What you're trying to do is create a Cartesian Product between tblA and
tblB. To create a Cartesian Product, you do not create any joins between the
tables.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

The code I posted was created by Access when I used the Query builder, I
just
switched to SQL view, copied it and pasted it here hoping that it might
help
illustrate my situation.
The reason tblC shows up in there is, I suppose, because of the
relationships between the tables.

What happens if you strictly run

SELECT tblA.aID, tblB.bID
FROM tblB INNER JOIN (tblA INNER JOIN tblC ON tblA.aID = tblC.aID) ON
tblB.bID = tblC.bID;

I'm assuming 0 rows are returned.

Strikes me that you don't need tblC in that subquery at all:

SELECT tblA.aID, tblB.bID
FROM tblA, tblB

Including tblC means that the query is going to fail if aID doesn't
already
exist in tblC.



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hello,

I have three related tables, A, B and C, with both A and B being in a
1-to-many relationship with C. A-> C <- B
I want to populate table C by taking one record [ID] from table A and
many
records [IDs] from table B, and have in table C - which contains fields
with
FKs from both table A and B's PK IDs - a number of records with one and
the
same FK from table A, but all the different FKs from table C.

table A - PK ID A: 1

table B - PK ID B: 1/2/3

=> table C: FK A: 1/1/1
FK B: 1/2/3

In other words, if table B has 10 records, I need table C also to have
10
records, but each with the same FK from table A.

I think appending will do this, so I created an Append query.
Unfortunately,
it only wants to append 0 records, so I must be missing something.

INSERT INTO tblC ( aID, bID )
SELECT tblA.aID, tblB.bID
FROM tblB INNER JOIN (tblA INNER JOIN tblC ON tblA.aID = tblC.aID) ON
tblB.bID = tblC.bID;

 

[Niniel]

Wow, you were so right - I removed table C, and the append query worked.

But my troubles aren't over yet.

What I really need to do is have this work for the current record in table A
only.
I create this record with a form, where I put in some information, and then
need to take the record ID and do the Append operation.

How can make sure it does that vs. a global Append of all the records in
table A?

What happens if you strictly run

SELECT tblA.aID, tblB.bID
FROM tblB INNER JOIN (tblA INNER JOIN tblC ON tblA.aID = tblC.aID) ON
tblB.bID = tblC.bID;

I'm assuming 0 rows are returned.

Strikes me that you don't need tblC in that subquery at all:

SELECT tblA.aID, tblB.bID
FROM tblA, tblB

Including tblC means that the query is going to fail if aID doesn't already
exist in tblC.



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hello,

I have three related tables, A, B and C, with both A and B being in a
1-to-many relationship with C. A-> C <- B
I want to populate table C by taking one record [ID] from table A and many
records [IDs] from table B, and have in table C - which contains fields
with
FKs from both table A and B's PK IDs - a number of records with one and
the
same FK from table A, but all the different FKs from table C.

table A - PK ID A: 1

table B - PK ID B: 1/2/3

=> table C: FK A: 1/1/1
FK B: 1/2/3

In other words, if table B has 10 records, I need table C also to have 10
records, but each with the same FK from table A.

I think appending will do this, so I created an Append query.
Unfortunately,
it only wants to append 0 records, so I must be missing something.

INSERT INTO tblC ( aID, bID )
SELECT tblA.aID, tblB.bID
FROM tblB INNER JOIN (tblA INNER JOIN tblC ON tblA.aID = tblC.aID) ON
tblB.bID = tblC.bID;

 

[Douglas J. Steele]

All you should have to do is put a criteria limiting tblA.aID to the desired
value.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Wow, you were so right - I removed table C, and the append query worked.

But my troubles aren't over yet.

What I really need to do is have this work for the current record in table
A
only.
I create this record with a form, where I put in some information, and
then
need to take the record ID and do the Append operation.

How can make sure it does that vs. a global Append of all the records in
table A?

What happens if you strictly run

SELECT tblA.aID, tblB.bID
FROM tblB INNER JOIN (tblA INNER JOIN tblC ON tblA.aID = tblC.aID) ON
tblB.bID = tblC.bID;

I'm assuming 0 rows are returned.

Strikes me that you don't need tblC in that subquery at all:

SELECT tblA.aID, tblB.bID
FROM tblA, tblB

Including tblC means that the query is going to fail if aID doesn't
already
exist in tblC.



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hello,

I have three related tables, A, B and C, with both A and B being in a
1-to-many relationship with C. A-> C <- B
I want to populate table C by taking one record [ID] from table A and
many
records [IDs] from table B, and have in table C - which contains fields
with
FKs from both table A and B's PK IDs - a number of records with one and
the
same FK from table A, but all the different FKs from table C.

table A - PK ID A: 1

table B - PK ID B: 1/2/3

=> table C: FK A: 1/1/1
FK B: 1/2/3

In other words, if table B has 10 records, I need table C also to have
10
records, but each with the same FK from table A.

I think appending will do this, so I created an Append query.
Unfortunately,
it only wants to append 0 records, so I must be missing something.

INSERT INTO tblC ( aID, bID )
SELECT tblA.aID, tblB.bID
FROM tblB INNER JOIN (tblA INNER JOIN tblC ON tblA.aID = tblC.aID) ON
tblB.bID = tblC.bID;

 

[Niniel]

Oh man, you are fantastic.
You can't begin to imagine how that's helped me.
Thanks a lot!

All you should have to do is put a criteria limiting tblA.aID to the desired
value.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Wow, you were so right - I removed table C, and the append query worked.

But my troubles aren't over yet.

What I really need to do is have this work for the current record in table
A
only.
I create this record with a form, where I put in some information, and
then
need to take the record ID and do the Append operation.

How can make sure it does that vs. a global Append of all the records in
table A?

What happens if you strictly run

SELECT tblA.aID, tblB.bID
FROM tblB INNER JOIN (tblA INNER JOIN tblC ON tblA.aID = tblC.aID) ON
tblB.bID = tblC.bID;

I'm assuming 0 rows are returned.

Strikes me that you don't need tblC in that subquery at all:

SELECT tblA.aID, tblB.bID
FROM tblA, tblB

Including tblC means that the query is going to fail if aID doesn't
already
exist in tblC.



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hello,

I have three related tables, A, B and C, with both A and B being in a
1-to-many relationship with C. A-> C <- B
I want to populate table C by taking one record [ID] from table A and
many
records [IDs] from table B, and have in table C - which contains fields
with
FKs from both table A and B's PK IDs - a number of records with one and
the
same FK from table A, but all the different FKs from table C.

table A - PK ID A: 1

table B - PK ID B: 1/2/3

=> table C: FK A: 1/1/1
FK B: 1/2/3

In other words, if table B has 10 records, I need table C also to have
10
records, but each with the same FK from table A.

I think appending will do this, so I created an Append query.
Unfortunately,
it only wants to append 0 records, so I must be missing something.

INSERT INTO tblC ( aID, bID )
SELECT tblA.aID, tblB.bID
FROM tblB INNER JOIN (tblA INNER JOIN tblC ON tblA.aID = tblC.aID) ON
tblB.bID = tblC.bID;