help with insert and disticnt query

[mcnews]

i need to append records from one table (X) into another main table
(A).
A is the master table.
X is a related table that may contain one or more records that have
one or more rows that include the foreign key.
so i only need to get one distinct row from table X into table A.

i can't seem to find a smooth way to do this with just one or two
queries.

any suggestions?

tia,
mcnewsxp

 

[Guest]

you should be able to do this with one append query by using "group by", as
long as you are appending BOTH fields which constitute your foreign key to
the appropriate fields in table (A).

jimaldo

 

[Guest]

ioops! misread your question and somehow assumed your foreign key comprised
two fields...either way, as as long as you use "group by" and ensure that the
full key is being appended to the appropriate field(s)...it should work fine

 

[mcnews]

ioops! misread your question and somehow assumed your foreign key comprised
two fields...either way, as as long as you use "group by" and ensure that the
full key is being appended to the appropriate field(s)...it should work fine

i don't get it, jimaldo.....?

 

[John Spencer]

One query should do it as long as you don't need to specify which of the
multiple records you want to add.

This query will work, but will give you errors for the duplicate key field.
It will add one of the duplicates and ignore the others. You just ignore
the error message
INSERT INTO A (PKField, FieldA, FieldB, FieldC)
SELECT FKField, Field1, Field2, Field3
FROM X

This query should also work and won't give you errors as long as the FKField
value doesn't already exist in Table A
INSERT INTO A (PKField, FieldA, FieldB, FieldC)
SELECT FKField, First(Field1), First(Field2), First(Field3)
FROM X
GROUP BY FKField

This query should work and not give you any errors. It will insert one
record for each FKField value that doesn't exist as a PKField value in table
A. Which one record of the duplicates it inserts is more or less random.

INSERT INTO A (PKField, FieldA, FieldB, FieldC)
SELECT FKField, First(Field1), First(Field2), First(Field3)
FROM X LEFT JOIN A
ON X.FKField = A.PKField
WHERE A.PKField is Null
GROUP BY FKField

BACK UP your database first, so if these don't work as you expect you can
restore the data.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[mcnews]

One query should do it as long as you don't need to specify which of the
multiple records you want to add.

This query will work, but will give you errors for the duplicate key field.
It will add one of the duplicates and ignore the others. You just ignore
the error message
INSERT INTO A (PKField, FieldA, FieldB, FieldC)
SELECT FKField, Field1, Field2, Field3
FROM X

This query should also work and won't give you errors as long as the FKField
value doesn't already exist in Table A
INSERT INTO A (PKField, FieldA, FieldB, FieldC)
SELECT FKField, First(Field1), First(Field2), First(Field3)
FROM X
GROUP BY FKField

This query should work and not give you any errors. It will insert one
record for each FKField value that doesn't exist as a PKField value in table
A. Which one record of the duplicates it inserts is more or less random.

INSERT INTO A (PKField, FieldA, FieldB, FieldC)
SELECT FKField, First(Field1), First(Field2), First(Field3)
FROM X LEFT JOIN A
ON X.FKField = A.PKField
WHERE A.PKField is Null
GROUP BY FKField

BACK UP your database first, so if these don't work as you expect you can
restore the data.

--

i neglected to mention that the backend is MS SQL.
i tried the first two ways and got SQL errors and rollback.
will try 3rd.
thanks much.