How do I extract data from 3 tables and put into a single table?

[jensen]

I need to extract data from 3 different tables and put it into a single table using the order no as the primary key. My SQL version is 6.5. Below is a brief description of what I'm trying to do:

Table A Table B Table C
Order no Name Order no Tel Order no Sex
0001 Bean 0001 1234 0001 M
0002 Sun 0003 3421 0002 F

Table D
Order no Name Tel Sex
0001 Bean 1234 M
0002 Sun F
0003 4321

[rekstout]

If you don't have You can use a UNION query to get all Order Numbers together. SELECT [Order no] FROM [Table A] UNION SELECT [Order no] FROM [Table B] UNION SELECT [Order no] FROM [Table C]; Call that qryOrderNos for now... Query against that using LEFT joins to the tables A to C SELECT qryOrderNos.[Order no], [Table A].Name, [Table B].Tel, [Table C].Sex FROM ((qryOrderNos LEFT JOIN [Table A] ON qryOrderNos.[Order no] = [Table A].[Order no]) LEFT JOIN [Table B] ON qryOrderNos.[Order no] = [Table B].[Order no]) LEFT JOIN [Table C] ON qryOrderNos.[Order no] = [Table C].[Order no]; That will return you a recordset equivalent to your Table D