join keeps cascading

[mg]

no idea what i'm doing wrong here.
my goal: keep all records from table-1, and append onto end of table-1 a
field from table-2....
current sql = select table1.*, table2.* from table1 left join table2 on
table1.code=table2.code

table1 has 3 records, table2 has 4 records
every combination of select or join i try, i always get 12 records
returned!!!
i even deleted the query, restarted access and tried again.

frustrated
thanks

 

[Jeff Boyce]

Please describe the actual data a bit more...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[mg]

while i was waiting, i opened a new database and created similar tables to
try it again.
i noticed that the field i was joining on was not unique in table2.
i had assumed that the LEFT JOIN would keep my original records, and if
there was more than one match in table2 that it would attach fields from the
record that was FIRST or LAST matched.
that turns out to be a wrong assumption.
i have since tried to be sure the ON clause is using a set of fields that
would create a unique key. seems to work ok now.
BUT, is there any way to assure that no matter what happens, that i always
get all records from table1 returned, and no extra records added-in???

 

[MGFoster]

no idea what i'm doing wrong here.
my goal: keep all records from table-1, and append onto end of table-1 a
field from table-2....
current sql = select table1.*, table2.* from table1 left join table2 on
table1.code=table2.code

table1 has 3 records, table2 has 4 records
every combination of select or join i try, i always get 12 records
returned!!!
i even deleted the query, restarted access and tried again.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

An append (INSERT INTO) query doesn't append just one column (field) it
appends an entire row (record). If you just want to append one row from
table2 you'd do it like this:

INSERT INTO table1 (col_name1, col_name2, col_name3)
SELECT col1, col2, col3
FROM table2
WHERE <criteria>

The criteria should only apply to table2.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSab7JYechKqOuFEgEQKqTgCg0HZ28vn+YbCg+XEPZrWZusku+7MAoIJF
BbDL09Lt30Aeq4t0Fhb5O1eW
=OpIW
-----END PGP SIGNATURE-----