UNION Error

[DS]

I'm getting an operator Error on this, any help is appreciated.
Thanks
DS

DoCmd.SetWarnings False
RefDetSQL = "INSERT INTO tblVoidDetails " & _
"SELECT * FROM tblCheckDetails " & _
"WHERE tblCheckDetails.CDCheckID = Forms!frmFXVoidSelect!TxtCheckID " & _
"UNION " & _
"SELECT * FROM tblCheckDetailsTMP " & _
"WHERE tblCheckDetailsTMP.CDCheckID = Forms!frmFXVoidSelect!TxtCheckID;"
DoCmd.RunSQL (RefDetSQL)
DoCmd.SetWarnings True

 

[Douglas J. Steele]

Does it work if you split it into two separate INSERT INTO queries (i.e. if
you eliminate the UNION query)?

 

[John W. Vinson]

I'm getting an operator Error on this, any help is appreciated.
Thanks
DS

DoCmd.SetWarnings False
RefDetSQL = "INSERT INTO tblVoidDetails " & _
"SELECT * FROM tblCheckDetails " & _
"WHERE tblCheckDetails.CDCheckID = Forms!frmFXVoidSelect!TxtCheckID " & _
"UNION " & _
"SELECT * FROM tblCheckDetailsTMP " & _
"WHERE tblCheckDetailsTMP.CDCheckID = Forms!frmFXVoidSelect!TxtCheckID;"
DoCmd.RunSQL (RefDetSQL)
DoCmd.SetWarnings True

I think Douglas is correct - an individual query can be a UNION query or an
INSERT INTO query but it can't be both. You may be able to use the union query
as a Subquery:

RefDetSQL = "INSERT INTO tblVoidDetails " & _
"SELECT * FROM ( SELECT * FROM tblCheckDetails " & _
"WHERE tblCheckDetails.CDCheckID = Forms!frmFXVoidSelect!TxtCheckID " & _
"UNION " & _
"SELECT * FROM tblCheckDetailsTMP " & _
"WHERE tblCheckDetailsTMP.CDCheckID = Forms!frmFXVoidSelect!TxtCheckID);"


I would recommend typing out all the fieldnames in order, both in the INSERT
and the UNION query, just to be sure that the fields match up correctly.

 

[DS]

Yes it Does.
Thanks
DS

 

[DS]

Maybe...

SELECT *
INTO tblVoidDetails
FROM tblCheckDetails
WHERE tblCheckDetails.CDCheckID = Forms!frmFXVoid!TxtCheckID
UNION
SELECT *
INTO tblVoidDetails
FROM tblCheckDetailsTMP
WHERE tblCheckDetailsTMP.CDCheckID = Forms!frmFXVoid!TxtCheckID

Is there another way to select info from two exact same tables with
different names and insert it into a third tble that is eaxactly the same
with a different name?
Thanks
DS

 

[Douglas J. Steele]

That definitely will not work.

First of all, SELECT .... INTO is a make-table query, so your query would be
attempting to create tblCheckDetails twice.

More importantly, though, is that you cannot use the UNION keyword with
Action queries.

I also would strongly recommend that you never use SELECT * with a UNION
query. Spell out the individual fields, just to guarantee that they're lined
up correctly. (I'd also never use INSERT INTO without an explicit field
list)

John Vinson gave you the SQL for selecting info from two tables and
inserting into a third.

 

[John W. Vinson]

Maybe...

SELECT *
INTO tblVoidDetails
FROM tblCheckDetails
WHERE tblCheckDetails.CDCheckID = Forms!frmFXVoid!TxtCheckID
UNION
SELECT *
INTO tblVoidDetails
FROM tblCheckDetailsTMP
WHERE tblCheckDetailsTMP.CDCheckID = Forms!frmFXVoid!TxtCheckID

No, that will certainly NOT work. The components of a UNION query must be
SELECT queries - not action queries.

Is there another way to select info from two exact same tables with
different names and insert it into a third tble that is eaxactly the same
with a different name?

There are two ways: use two Append queries as Douglas suggested (which *will*
work and is probably the best bet); or base an Append query upon a Union
query, either as a subquery per my suggestion or by saving the union query and
creating a new append query based on it.

In any case, having three different tables storing the "same" data is of very
questionable design. If you had just *ONE* table with one or more fields
indicating that this record was a "TMP" record or a "VOID" record, you would
not need to use any data migration at all, just change the value of the
field(s).

 

[DS]

Thanks Doug,
I see Johns suggestion, I didn't look close enough the first time.
DS

 

[DS]

I know it sounds weird, but it works and it keeps things seperate for
reports and speed, as I'll be adding a number of records.
Thanks
DS

 

[John W. Vinson]

I know it sounds weird, but it works and it keeps things seperate for
reports and speed, as I'll be adding a number of records.

If "a number of records" exceeds a million then you should probably be using
SQL/Server as the data storage medium. If it's fewer and if you have indexes
on the fields which identify the subsets, having one table would be simpler
and I'd bet just as fast. Your choice though!