How to enter this DoCmd.RunSQL

[Carlgardner]

I am trying to get access 2003 to accept a final DoCmd.RunSQL at the end of
this


Private Sub Form_Load()
DoCmd.SetWarnings False
'Build the 4 new tables
DoCmd.RunSQL "SELECT DISTINCT Lot1 AS Lot INTO LOT1 FROM BATCH"
DoCmd.RunSQL "SELECT DISTINCT Lot2 AS Lot INTO LOT2 FROM BATCH"
DoCmd.RunSQL "SELECT DISTINCT LotC AS Lot INTO LotC FROM BATCH"
DoCmd.RunSQL "SELECT DISTINCT LotD AS Lot INTO LotD FROM BATCH"
'Build the final table (Result of the union) for the CBO in frm_Select Drug
by LOT#

THIS is the command I cannot get it to accept: I believe it is formatting I
cannot do. Novice.
SELECT Lot INTO ALLLOT
FROM [Select Lot from Lot1 Union Select Lot from Lot2 Union Select Lot from
LotC Union Select Lot from LotD].
AS [%$##@_Alias];

 

[Rick Brandt]

I am trying to get access 2003 to accept a final DoCmd.RunSQL at the
end of this


Private Sub Form_Load()
DoCmd.SetWarnings False
'Build the 4 new tables
DoCmd.RunSQL "SELECT DISTINCT Lot1 AS Lot INTO LOT1 FROM BATCH"
DoCmd.RunSQL "SELECT DISTINCT Lot2 AS Lot INTO LOT2 FROM BATCH"
DoCmd.RunSQL "SELECT DISTINCT LotC AS Lot INTO LotC FROM BATCH"
DoCmd.RunSQL "SELECT DISTINCT LotD AS Lot INTO LotD FROM BATCH"
'Build the final table (Result of the union) for the CBO in
frm_Select Drug by LOT#

THIS is the command I cannot get it to accept: I believe it is
formatting I cannot do. Novice.
SELECT Lot INTO ALLLOT
FROM [Select Lot from Lot1 Union Select Lot from Lot2 Union Select
Lot from LotC Union Select Lot from LotD].
AS [%$##@_Alias];

Is there a reason you don't just use a series of append queries into ONE
table instead of building multiple tables and then trying to UNION them?

Make table queries are seldom a proper choice for anything in code.

 

[Carlgardner via AccessMonster.com]

This was the only way I knew how to do it. If there is an easier way that is
fine with me. Like I said, I am very new to this and was just trying to get
the SQL statement I had to fit at the end.
thanks

I am trying to get access 2003 to accept a final DoCmd.RunSQL at the
end of this

[quoted text clipped - 15 lines]

Lot from LotC Union Select Lot from LotD].
AS [%$##@_Alias];

Is there a reason you don't just use a series of append queries into ONE
table instead of building multiple tables and then trying to UNION them?

Make table queries are seldom a proper choice for anything in code.

 

[Carlgardner via AccessMonster.com]

After multiple attempts the make table query appears to be easiest for me if
someone would help me with the DoCmd.RunSQL statement because everyother
attempt has not updated the table correctly or the table caused an error
because it already existed. I couldnot find a way to delete the table from
the report or the form as it was always in use.
thanks
Carl

This was the only way I knew how to do it. If there is an easier way that is
fine with me. Like I said, I am very new to this and was just trying to get
the SQL statement I had to fit at the end.
thanks
[quoted text clipped - 6 lines]

Make table queries are seldom a proper choice for anything in code.

 

[Carlgardner via AccessMonster.com]

That is great.
Thank you
Carl

After multiple attempts the make table query appears to be easiest
for me if someone would help me with the DoCmd.RunSQL statement

[quoted text clipped - 4 lines]

thanks
Carl

If you just want to use this as the RowSource of a ComboBox you don't need
any new tables or MakeTable queries to create them. All you need to do is
directly use a UNION query as the RowSource.

SELECT Lot1 AS Lot FROM BATCH
UNION SELECT Lot2 FROM BATCH
UNION SELECT LotC FROM BATCH
UNION SELECT LotD FROM BATCH

DISTINCT is not required because a UNION automatically eliminates
duplicates.