SQL Statement

[Mike]

I posted this is the Access ADAP SQL discussion but I think it is better
served for this forum:

I have 2 tables. Table A contains 2 fields which are not in Table B. The
records in Table A are also in Table B but without the two additional fields.
Table B contains all the records in Table A (minus the 2 addl fields) and
approximately 5K additional records. A unqiue key from Table A is also on
Table B for all matching records, just without the other 2 fields.

I want to combine both tables while being able to include the two additional
fields from Table A to Table B.

Any help would be appreciated.

Thanks,
Mike

 

[Marshall Barton]

I have 2 tables. Table A contains 2 fields which are not in Table B. The
records in Table A are also in Table B but without the two additional fields.
Table B contains all the records in Table A (minus the 2 addl fields) and
approximately 5K additional records. A unqiue key from Table A is also on
Table B for all matching records, just without the other 2 fields.

I want to combine both tables while being able to include the two additional
fields from Table A to Table B.

Does this do what you want?

SELECT TableB.*, TableA.fld1, TableA.fld2
FROM tableB LEFT JOIN tableA
ON tableB.uniquekey = tableA.uniquekey

 

[vanderghast]

SELECT * FROM tableA


since all records of tableA are in tableB and tableB does not contain any
information not in tableA... why tableB matter in anything?



Vanderghast, Access MVP

 

[Mike]

Table A contains 2 fields which are not in Table B. I want to include the two
fields from Table A into Table B. Can I accomplish this through a Left/Right
join?

 

[John Spencer MVP]

SELECT TableB.*, TableA.ExtraFieldOne, TableA.ExtraFieldTwo
FROM TableB LEFT JOIN TableA
ON TableB.Key = TableA.Key

IF you can only construct queries using Query Design view.
-- Add both tables
-- create a join on the Key field (drag from one to the other
-- double click on the join line and select option 2 or 3
the one that says all records in TableB and only matching in TableA
-- select all the fields you want to see from TableB
-- select just the two additional fields from tableA

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Mike]

Thanks!

SELECT TableB.*, TableA.ExtraFieldOne, TableA.ExtraFieldTwo
FROM TableB LEFT JOIN TableA
ON TableB.Key = TableA.Key

IF you can only construct queries using Query Design view.
-- Add both tables
-- create a join on the Key field (drag from one to the other
-- double click on the join line and select option 2 or 3
the one that says all records in TableB and only matching in TableA
-- select all the fields you want to see from TableB
-- select just the two additional fields from tableA

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County