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.
Issa S.Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pawan KumarDatabase ExpertCommented:
Yes we can do that, what the criteria for updating the records.

Sample update statement.

UPDATE table1
INNER JOIN table2 ON [table1].[someIdcolumn] = [table2].[SomeIdColumn]
SET [table1].[col1] = [table2].[Col1], [table1].[col2] = [table2].[col2]
WHERE ([SomeColumne] = 'Value')

Open in new window

0
PatHartmanCommented:
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 "<".
1
Olgierd UngehojerSenior Network AdministratorCommented:
You can do something like this way:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
IF EXIST (SELECT)
BEGIN
UPDATE
END
ELSE
BEGIN
INSERT
END
COMMIT TRANSACTION;

Open in new window

0
Issa S.Author Commented:
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
0
PatHartmanCommented:
No.  Once a db is split, changes to the BE will not propagate to objects in the FE.  You would need to use a find and replace utility at this point.

Access doesn't support updating tables with aggregated data because saving these values violates normal forms.  Unless you are creating a data warehouse, it is best to use queries to aggregate data on the fly rather than save it permanently.  As soon as aggregated data is saved it has the potential to be incorrect if one of the underlying records is updated.

Taking over other people's projects is a problem when they've used poor techniques.  You need to judge whether or not to leave everything alone if it isn't "broken" or to bring it up to standards if it looks like the project will grow and you'll be fighting with it long term.  At this point, you have both poor naming standards and poor design that stores aggregated data.  You have your work cut out for you.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.