INSERT INTO

[afortney]

I am trying to get the following query to work in Access,
but errors occur, please help.

INSERT INTO Table1( Project_id, r.Page_id, Order_id)
SELECT Project_id, r.Page_id, Order_id

FROM RULES r, PROJECT p, Page
WHERE
p.Business_stream <> 'Upstream' or
p.project_id<=30 or
(p.Business_stream = 'Upstream' AND p.Upstream_wells = '' )

AND
r.Business_rule = 'NewDrilling'
UNION

SELECT Project_id, r.Page_id, Order_id
FROM RULES r, PROJECT p, Page
WHERE p.Partners_involved = 'No' AND
r.Business_rule = 'Partners_involved'
UNION
SELECT Project_id, r.Page_id, Order_id
FROM RULES r, PROJECT p, Page
WHERE p.Project_size = 'Small (<$5 million)' AND
r.Business_rule = 'less5mil'
UNION
SELECT Project_id, r.Page_id, Order_id
FROM RULES r, PROJECT p, Page
WHERE p.Business_stream = 'Upstream' AND
p.Upstream_wells = '' AND p.Upstream_reservoir = ''
AND
r.Business_rule = 'bsUp'
UNION
SELECT Project_id, r.Page_id, Order_id
FROM RULES r, PROJECT p, Page
WHERE p.Business_stream = 'Chemicals' AND
r.Business_rule = 'bsUpChemDown'
UNION
SELECT Project_id, r.Page_id, Order_id
FROM RULES r, PROJECT p, Page
WHERE p.Business_stream = 'Gas & Power' AND
r.Business_rule = 'bsUpChemDown'
UNION
SELECT Project_id, r.Page_id, Order_id
FROM RULES r, PROJECT p, Page
WHERE p.Business_stream = 'Solar' AND
r.Business_rule = 'bsUpChemDown'
UNION
SELECT Project_id, r.Page_id, Order_id
FROM RULES r, PROJECT p, Page
WHERE p.Business_stream = 'Downstream Oil' AND
p.Downstream_type <> '' AND
r.Business_rule = 'bsUpChemDown'
UNION
SELECT Project_id, r.Page_id, Order_id
FROM RULES r, PROJECT p, Page
WHERE p.Business_stream = 'Upstream' AND
p.Upstream_wells <> '' AND p.Upstream_reservoir = ''
AND
r.Business_rule = 'Up_wells'
UNION
SELECT Project_id, r.Page_id, Order_id
FROM RULES r, PROJECT p, Page
WHERE p.Business_stream = 'Upstream' AND
p.Upstream_wells = '' AND p.Upstream_reservoir <> ''
AND
r.Business_rule = 'Up_res'
UNION
SELECT Project_id, r.Page_id, Order_id
FROM RULES r, PROJECT p, Page
WHERE p.Business_stream = 'Upstream' AND
p.Upstream_wells <> '' AND p.Upstream_reservoir <> ''
AND
r.Business_rule = 'Up_wells_res'
UNION
SELECT Project_id, r.Page_id, Order_id
FROM RULES r, PROJECT p, Page
WHERE p.Country = 'UK' AND r.Business_rule = 'country'
UNION
SELECT Project_id, r.Page_id, Order_id
FROM RULES r, PROJECT p, Page
WHERE p.Country = 'North America' AND
r.Business_rule = 'country'
UNION
SELECT Project_id, r.Page_id, Order_id
FROM RULES r, PROJECT p, Page
WHERE p.Country = 'Western Europe' AND
r.Business_rule = 'country'
UNION SELECT Project_id, r.Page_id, Order_id
FROM RULES r, PROJECT p, Page
WHERE p.Country =

 

[Pat Hartman]

There are a number of problems with the query.
1. r.Page_id in the insert clause needs to be just Page_id
2. you can't use an embedded union query as the source for the append data. Create a separate query that does the union and use that new query as the source for the append data.
3. in the union query, you have not specified how to join the three tables in each select clause. This will cause Jet to produce a cartesian product. Probably NOT what you want.

 

[afortney]

Is there any way you could show me how to do this, give an example?

 

[Pat Hartman]

Without knowing what columns the tables contain and which are the foreign keys, I couldn't build a join for you.

Open the query designer in QBE view. Add the three tables you want to join, and draw lines connecting the columns to join on. Then switch to SQL view and you will see the correct syntax for your particular tables. You can then copy that into your Union query (which you can't build in QBE view).