Link to home
Avatar of Issa S.
Issa S.

asked on

Can I convert an INSERT query which is based on a SELECT query to become an UPDATE query.

Hi All,

I have made an APPEND (Insert)  query to update 3 values in a one record file named:  [Sanad Record-Copy]
Its SQL is:

INSERT INTO [Sanad Record-Copy] ( SumOfDrs, SumOfCrs, [Date] )
SELECT Sum([BBashit < Start Date].amount) AS SumOfDrs, Sum([BBashit < Start Date].payment) AS SumOfCrs, Max([BBashit < Start Date].Date) AS MaxOfDate
FROM [BBashit < Start Date];


This INSERT is based on the following Select SQL:

SELECT cushist.voucher, cushist.Date, cushist.caccount, cushist.daccount, cushist.amount, cushist.payment, cushist.cname, cushist.salesm, cushist.accno, cushist.remarks, cushist.pnacc, cushist.dname, cushist.ID
FROM cushist
WHERE (((cushist.Date)<[Forms]![Bashit 4 PRINTing]![StartDate]) AND ((cushist.caccount)=[Forms]![Bashit 4 PRINTing]![MFcaccount])) OR (((cushist.Date)<[Forms]![Bashit 4 PRINTing]![StartDate]) AND ((cushist.daccount)=[Forms]![Bashit 4 PRINTing]![MFcaccount]));


My Question is : Can I convert  an INSERT query which is based on a SELECT query  to become an UPDATE query to update the above 3 fields?

Thanks.
SOLUTION
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of PatHartman
No.  Access will mark the query as not updateable because it contains aggregated data.

PS- you might want to consider adopting professional naming standards.  Column names should never be reserved words and function names and properties such as Date and Name are especially problematic.  Names should also never include embedded spaces or special characters such ad "<".
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Avatar of Issa S.
Issa S.

ASKER

Hi Pat,,,,

I wish it was my DB, I never like to use reserved words,  special characters, or Function names. I took it over some body else and it was working fine with bracketing and double quotes ".

The big question now is: if I rename names like this on their source tables, will the auto rename publish the change globally  ?!

Thanks
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.