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?
 
PatHartmanConnect With a Mentor Commented:
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
 
Pawan KumarConnect With a Mentor Database 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 UngehojerConnect With a Mentor Senior 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.