Syntax problem with multiple statement query

  • Thread starter Thread starter mxtreme
  • Start date Start date
M

mxtreme

Is it possible to do a multiple statement query in Access?

This is the query I am working with...

It is essentially taking one table and converting it into two tables.
The parent table will include one product per tuple with the product
description and the child table will include multiple tuples per
product including zoned pricing information. It will be used as an
update query as the tables will be cleared before each insert.

Query as follows...


DELETE *
FROM tblProducts, tblProduct_type_price;

INSERT INTO tblProducts ([tblProducts].intMFA_code,
[tblProducts].chrDescription,
[tblProducts].intUnits_per_case,[tblProducts].intChannel_id)
SELECT DISTINCT [qProduct].STOCK_UNIT_CODE, [qProduct].STOCK_UNIT_DESC,
[qProduct].UNITS_PER_CASE, [qProduct].CHANNEL_CODE
FROM qProduct;

INSERT INTO tblProduct_type_price ([tblProduct_type_price].intMFA_code,
[tblProduct_type_price].chrPrice_class_code,
[tblProduct_type_price].curCase_cost)
SELECT [qProduct].STOCK_UNIT_CODE, [qProduct].PRICING_CLASS_CODE,
[qProduct].STOCK_UNIT_CODE, [qProduct].LIST_PRICE
FROM qProduct;

The error i am seeing is the common syntax error... "Characters found
after end of SQL statement".

Any help appreciated.

Shaun.
 
Is it possible to do a multiple statement query in Access?

This is the query I am working with...

It is essentially taking one table and converting it into two tables.
The parent table will include one product per tuple with the product
description and the child table will include multiple tuples per
product including zoned pricing information. It will be used as an
update query as the tables will be cleared before each insert.

Query as follows...


DELETE *
FROM tblProducts, tblProduct_type_price;

INSERT INTO tblProducts ([tblProducts].intMFA_code,
[tblProducts].chrDescription,
[tblProducts].intUnits_per_case,[tblProducts].intChannel_id)
SELECT DISTINCT [qProduct].STOCK_UNIT_CODE, [qProduct].STOCK_UNIT_DESC,
[qProduct].UNITS_PER_CASE, [qProduct].CHANNEL_CODE
FROM qProduct;

INSERT INTO tblProduct_type_price ([tblProduct_type_price].intMFA_code,
[tblProduct_type_price].chrPrice_class_code,
[tblProduct_type_price].curCase_cost)
SELECT [qProduct].STOCK_UNIT_CODE, [qProduct].PRICING_CLASS_CODE,
[qProduct].STOCK_UNIT_CODE, [qProduct].LIST_PRICE
FROM qProduct;

The error i am seeing is the common syntax error... "Characters found
after end of SQL statement".

Any help appreciated.

Shaun.
Click to expand...

AFAIK Access does not support multiple statements, at least not for
the Jet engine. You will have to run this as three different queries.
It may be possible with pass-trough queries to a SQL server that
supports this.

HTH
Matthias Kläy
 
Back
Top