Update Query Question

[Vince111]

Hi,

I'm pretty new to Access so I think this should be an easy question...

I have two tables that I have created in Access 2000. They are named "Members" and "Transactions". The members table contains a unique member number (primary key) and other info like name, address, email address, etc. This table also contains two fields called "Current Account A" and "Current Account B". Accounts A and B are two seperate accounts which each member owns. The "Transactions" table lists the transactions that are made. It contains the following fields: Code (Unique autonumber), member number, amount, and account type (A or B).

With this information, I would like to create a query that updates each members' current account A and B every time the query is run. I have tried using the DSum function but I have been unsuccessful. It seems to sum all the transactions no matter what criteria (third argument in the DSum function) I choose . After I make this query, I would like to add it to a Visual Basic 6.0 project.

Thanks
Vince

 

[shitalchristian]

Hello
You have to create two qureies for updation(i.e
account a and account b)

First query

UPDATE Members INNER JOIN Transactions ON Members.[Member Numbar] = Transactions.[member no] SET Members.[current account A] = [Transactions].[amount]
WHERE (((Transactions.[account type])="a&quot);
When you run this query it will update the member's
account of type A (i.e. Current Accoutn A)

Second Query

UPDATE Members INNER JOIN Transactions ON Members.[Member Numbar] = Transactions.[member no] SET Members.[current account A] = [Transactions].[amount]
WHERE (((Transactions.[account type])="b&quot);

When you run this query it will update the member's
account of type B(i.e. Current Accoutn B)

I am sure it will run correctly
All the best!!!

 

[Vince111]

Thanks,

I also had another question...

Suppose I had two more fields in my "Members" table called "CurrentAccountA" and "CurrentAccountB" and I wanted to make an update query to update each member's current balance on account A and B (The two fields mentioned above). How do I do it?

Thanks,
Vince