Make-table issue - I have table A and table B, would like to have all data from both in Table C...

Cegan
Cegan used Ask the Experts™
on
Okay, it's hard to explain this thoroughly, but here goes.  When I do this:
SELECT civicno_xyposen.Easting, civicno_xyposen.Northing, civicno_xyposen.Latitude, civicno_xyposen.Longitude INTO QCN_Table
FROM civicno_xyposen INNER JOIN quiscivxref ON civicno_xyposen.maplink = quiscivxref.maplink
ORDER BY civicno_xyposen.maplink, quiscivxref.maplink;
I get the four columns of data I want to add to the new table, QCN_Table.

However, I would like to add these four columns to the QCN_Table, AND, I would like to also add ALL of the columns from table quiscivxref too!  And when I try to do this with:
SELECT civicno_xyposen.Easting, civicno_xyposen.Northing, civicno_xyposen.Latitude, civicno_xyposen.Longitude, quiscivxref.* INTO QCN_Table
FROM civicno_xyposen INNER JOIN quiscivxref ON civicno_xyposen.maplink = quiscivxref.maplink
ORDER BY civicno_xyposen.maplink, quiscivxref.maplink;
I get only the columns of data from quiscivxref.  I do get column headers from civicno_xyposen, but no data.  It's weird, eh?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
I believe part of the problem is "maplink" is a field that is common to both tables.  Therefore, trying to add all columns from quiscivxref via a wildcard will not work.  Try specifying these columns in your select explicitly:

SELECT civicno_xyposen.Easting, civicno_xyposen.Northing, civicno_xyposen.Latitude, civicno_xyposen.Longitude, quiscivxref.field1, quiscivxref.field2, ... INTO QCN_Table

Good luck,

Scott

Commented:
You need a UNION (and the ORDER BY is irrelevant):

SELECT * FROM
(
--first group of 4
SELECT civicno_xyposen.Easting, civicno_xyposen.Northing, civicno_xyposen.Latitude, civicno_xyposen.Longitude
FROM civicno_xyposen INNER JOIN quiscivxref ON civicno_xyposen.maplink = quiscivxref.maplink
UNION ALL
--everytthing from quscivxref, even if it duplicates first 4
SELECT quiscivxref .Easting, quiscivxref.Northing, quiscivxref Latitude, quiscivxref.Longitude
FROM quiscivxref
) INTO QCN_Table

Commented:
Let me try again:

SELECT INTO QCN_Table FROM
--first group of 4
SELECT civicno_xyposen.Easting, civicno_xyposen.Northing, civicno_xyposen.Latitude, civicno_xyposen.Longitude
FROM civicno_xyposen INNER JOIN quiscivxref ON civicno_xyposen.maplink = quiscivxref.maplink
UNION ALL
--everytthing from quscivxref, even if it duplicates first 4
SELECT quiscivxref.Easting, quiscivxref.Northing, quiscivxref Latitude, quiscivxref.Longitude
FROM quiscivxref


PS. If you get duplicates of the first 4 rows that you don't want, then use UNION instead of UNION ALL

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial