double update query problem
Windows 7 64bit 
Access 2010 64bit
I am designing a database for a youth club. I have the tables - member details, outing details and attending outing.
In the outing details, i have fields such as - cost of deposit and total cost of trip
In the attending outing table i have fields such as - deposit paid (check box), amount paid, total paid, amount outstanding.
I have also added another update to that same query. I making the deposit paid (check box) equal the cost of deposit. That figure will then be added to amount paid and will update the total paid field
IIf([Attending Outing].[Deposit Paid]=True,[Outing Details].[Cost of Deposit]+[Attending Outing].[Amount Paid],[Attending Outing].[Amount Paid])
It works fine but is rather slow..
I have created an update query for amount outstanding.
[Outing Details].[Total Cost of Outing]-[Attending Outing].[Total Paid]
It works fine
When i run them both in the same update query it updates the total paid field. Then i have to press run again to update the amount outstanding field.
I only want to update that query once rather than update that same query twice.
SQL -
UPDATE [Outing Details] INNER JOIN ([Cadet Details] INNER JOIN [Attending Outing] ON [Cadet Details].[Member ID] = [Attending Outing].[Member ID]) ON [Outing Details].[Outing ID] = [Attending Outing].[Outing ID] SET [Attending Outing].[Total Paid] = IIf([Attending Outing].[Deposit Paid]=True,[Outing Details].[Cost of Deposit]+[Attending Outing].[Amount Paid],[Attending Outing].[Amount Paid]), [Attending Outing].[Amount Outstanding] = [Outing Details].[Total Cost of Outing]-[Attending Outing].[Total Paid];
thanks in advance
Reply With Quote
Originally Posted by orange
