Insert Query with multiple SubQueries..

[jmlady]

Hey there,
I'm trying to write an insert query that takes two different queries, and inserts the values based on the year inputed by the user...

[sup]
insert into [pnltable] (totalsales, totalpurch, totalsupp, totaluniform, totalautoexp, totalofficeexp, totalphone) 
(Select [ADMIN TotalCost Annual Totals].[Sum Of TotalCost]  as [totalsales] FROM  [ADMIN TotalCost Annual Totals] where [InvoiceDate By] = [year])
(Select [ADMIN Receipts Annual Totals].[Sum Of rprice] as a FROM [ADMIN Receipts Annual Totals] where [rdate By Year] = [year] and [rcat] = 'Purchases')
(Select [ADMIN Receipts Annual Totals].[Sum Of rprice] as a FROM [ADMIN Receipts Annual Totals] where [rdate By Year] = [year] and [rcat] = 'Suppliers')
(Select [ADMIN Receipts Annual Totals].[Sum Of rprice] as a FROM [ADMIN Receipts Annual Totals] where [rdate By Year] = [year] and [rcat] = 'Uniform')
(Select [ADMIN Receipts Annual Totals].[Sum Of rprice] as a FROM [ADMIN Receipts Annual Totals] where [rdate By Year] = [year] and [rcat] = 'Auto Expenses')
(Select [ADMIN Receipts Annual Totals].[Sum Of rprice] as a FROM [ADMIN Receipts Annual Totals] where [rdate By Year] = [year] and [rcat] = 'Office Supplies & Expenses')
(Select [ADMIN Receipts Annual Totals].[Sum Of rprice] as a FROM [ADMIN Receipts Annual Totals] where [rdate By Year] = [year] and [rcat] = 'Phone');
[/sup]

what am I doing wrong? it keeps saying incorrect INSERT, but if i use "VALUE(...n)" it still doesn't work.

Thanks in advance,
John

 

[lespaul]

first you need to write a query that returns all the fields you are trying to insert:

totalsales totalpurch totalsupp totaluniform totalautoexp totalofficeexp totalphone

does this query:

(Select [ADMIN TotalCost Annual Totals].[Sum Of TotalCost]  as [totalsales] FROM  [ADMIN TotalCost Annual Totals] where [InvoiceDate By] = [year])
(Select [ADMIN Receipts Annual Totals].[Sum Of rprice] as a FROM [ADMIN Receipts Annual Totals] where [rdate By Year] = [year] and [rcat] = 'Purchases')
(Select [ADMIN Receipts Annual Totals].[Sum Of rprice] as a FROM [ADMIN Receipts Annual Totals] where [rdate By Year] = [year] and [rcat] = 'Suppliers')
(Select [ADMIN Receipts Annual Totals].[Sum Of rprice] as a FROM [ADMIN Receipts Annual Totals] where [rdate By Year] = [year] and [rcat] = 'Uniform')
(Select [ADMIN Receipts Annual Totals].[Sum Of rprice] as a FROM [ADMIN Receipts Annual Totals] where [rdate By Year] = [year] and [rcat] = 'Auto Expenses')
(Select [ADMIN Receipts Annual Totals].[Sum Of rprice] as a FROM [ADMIN Receipts Annual Totals] where [rdate By Year] = [year] and [rcat] = 'Office Supplies & Expenses')
(Select [ADMIN Receipts Annual Totals].[Sum Of rprice] as a FROM [ADMIN Receipts Annual Totals] where [rdate By Year] = [year] and [rcat] = 'Phone');

return a result set in the same format as above? It doesn't appear to.

You may get the results you are looking for by modifying this query to something like:

Select [ADMIN TotalCost Annual Totals].[Sum Of TotalCost]  as [totalsales], 
(Select [ADMIN Receipts Annual Totals].[Sum Of rprice] as a FROM [ADMIN Receipts Annual Totals] where [rdate By Year] = [year] and [rcat] = 'Purchases') As totalpurchase, 
(Select [ADMIN Receipts Annual Totals].[Sum Of rprice] as a FROM [ADMIN Receipts Annual Totals] where [rdate By Year] = [year] and [rcat] = 'Suppliers') As totalsupp, (Select [ADMIN Receipts Annual Totals].[Sum Of rprice] as a FROM [ADMIN Receipts Annual Totals] where [rdate By Year] = [year] and [rcat] = 'Uniform') As Totaluniform, (Select [ADMIN Receipts Annual Totals].[Sum Of rprice] as a FROM [ADMIN Receipts Annual Totals] where [rdate By Year] = [year] and [rcat] = 'Auto Expenses') as TotalAutoExp, 
(Select [ADMIN Receipts Annual Totals].[Sum Of rprice] as a FROM [ADMIN Receipts Annual Totals] where [rdate By Year] = [year] and [rcat] = 'Office Supplies & Expenses') as TotalOfficeExp, 
(Select [ADMIN Receipts Annual Totals].[Sum Of rprice] as a FROM [ADMIN Receipts Annual Totals] where [rdate By Year] = [year] and [rcat] = 'Phone') As TotalPhone 

FROM  [ADMIN TotalCost Annual Totals] where [InvoiceDate By] = [year]);

Leslie

 

[jmlady]

Thank you much! Had to make some edits and badabing! Works like a charm! Thanks again, Leslie.

John

 

[lespaul]

glad to help and glad it worked!!

les