• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 44
  • Last Modified:

2 Table T1 is one to many T2 i need to update every row in T1 when I change the percent value

3 Table   ( T1 is one to many T2 )  and  ( T2 is one to many T3 )

I need to update every row in table T2  using  VBA  SQL statement  

open the attach File to see the problem

Thanks a lot

0
ramyooooo
Asked:
ramyooooo
  • 4
  • 2
  • 2
4 Solutions
 
Fabrice LambertFabrice LambertCommented:
Please, disable all security and re-upload the file.
0
 
PatHartmanCommented:
We can't look at the data but I can tell you from your description that you have a design problem.  You are storing calculated data and you should not be.  It is far better to do the calculation in a query so that the data is always current and you don't have to run this type of update query.
0
 
ramyoooooSystem AnalasysAuthor Commented:
Here is the file again without  Shift+Enter

I know  that  It is better to do the calculation in a query ,But I need to make a form  that give the result In the same form

I do a lot of SQL statement before  but this one is complicated a little ,You have to make
Update statement with Select statement  while using Dsum statement
I hope I can find a good help

thanks a lot
N1.mdb
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
Fabrice LambertFabrice LambertCommented:
I know  that  It is better to do the calculation in a query ,But I need to make a form  that give the result In the same form
This is the goal of sub forms.

In short:
Instead of basing your middle form on a table, base it on a query.

Unless performances are an issue (in you case, I doubt), a database should not hold calculated data.
0
 
ramyoooooSystem AnalasysAuthor Commented:
I did it and it work fine with me
but to fix the problem, I had to split my original update Vba Code into two statements. The first one is a make-table query and the other one is a cross-table update query.

Thank you All
0
 
PatHartmanCommented:
You found the work around.  SQL doesn't support the action you originally attempted for a reason --- it violates second normal form.  Hopefully this won't cause your users a problem down the road.

Please accept your own answer and close the question.
0
 
ramyoooooSystem AnalasysAuthor Commented:
no form will violates ,I built this form for you so you can see all table in the same Time
but in real form it works individually fine
one of reason to do this Sql code
is to reduce the time while report query
and believe me it did a lot

Thanks a lot for all of you
0
 
ramyoooooSystem AnalasysAuthor Commented:
To fix this issue you have to work around, I split my original update Vba Code into two statements.
1 - The first one is a make-table query
2 - The other one is a cross-table update query.
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.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

  • 4
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now