Removing Entries from One Table That Are in Another Table 1

[mcongdon]

Hi,
I’m new to programming and database design, and I got thrown into a very large project creating a system for a trading department at a broker to keep track of all bond trades that are made. Instead of physically writing out tickets and time stamping them, they want to have a digital ticket that accepts data, puts it in a database, the stores it so they can run reports off of it for years to come. This is as far as I have gotten.

They also want to be able to see what bonds are owned. To do this, they need to find all the entries that have the field “BUY/SELL” = “BUY” . I created a query to do this. Then, they need to have all the bonds that have “SELL” as the “BUY/SELL” field. I have created a query to do this.

The part that is killing me is that I have to take the bonds that have been sold and remove them from the ones that have been bought. If a certain account number has bought 50 NY Metro Municpal Bonds (identified by symbol), and then they sold 30 of the same bond, I need to be able to see that they now hold 20.

Any help would be greatly appreciated in this project, I’ve looked at add/delete queries and all kinds of books/forums and I cannot figure out how to do this.
Thanks!

 

[lespaul]

is there some field that is common to both the bought and sold bonds? Is this all stored in a single table?

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[mcongdon]

The bought and sold bonds are all stored in one table. The primary key is just an autonumber field for each trade entered. Ex: I buy one bond, that is trade number 1; I sell a different bond, that is trade number 2. They are stored in a single table, but that would not be hard to change if it would make this process easier.

 

[mcongdon]

I forgot the second part of that last post. The fields are all the same. The bought and sold bonds that I'm looking to compare are ones that are bought by a certain account number, say 43CXXXX. Then, I want to look for bonds that were sold by 43CXXXX. If they bought 30M of bond "BOND", then sold 20M of bond "BOND", then I need to be able to see that there is 10M left of bond "BOND" in that account. All fields are the same, except quantity and trade number (the autonumber field)

 

[lespaul]

So your table is like:
[tt]
AutoNumber AccountNumber Bond B/S Amount
1 43CXXXXX NYMMB B 30
2 43CXXXXX NYMMB S 20[/tt]

and you want to be able to say that there's only 10 left for that account and bond type....

Maybe something like:

SELECT AccountNumber, Bond, SUM(iif [B/S] = "B", Amount, (amount * -1)) FROM TableName
GROUP BY AccountNumber, Bond
ORDER BY AccountNumber, Bond

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com