Counting Transactions

[JapanFreak]

Hi there,

I have one table, called tblTransactions, which contains records on security market transactions. For each buy and sell order, respectively it contains one dataset with columns Date, Ticker (i.e. the unique identifier of each security), Quantity (positive for buy, negative for sell orders) and Price (at which the trade was executed).

The following SQL query code gives me then the composition of my security portfolio as per any arbitrarily chosen date (here 1/10/2007):

SELECT [tblTransactions].[Ticker], sum([tblTransactions].[Quantity]) AS TotalQuantity
FROM tblTransactions
WHERE [tblTransactions].[Date]<=#1/10/2007#
GROUP BY [tblTransactions].[Ticker]
HAVING sum(tblTransactions.Quantity) >0
ORDER BY [tblTransactions].[Ticker];

For instance, for some trades like the following...


Data --- Ticker --- Quantity --- Price
01/01/07 --- AAA --- 50 --- $50
01/01/07 --- BBB --- 25 --- $75
01/15/07 --- AAA --- 30 --- $60

...it results in the following query results:

01/10/07:
Ticker --- TotalQuantity
AAA --- 50
BBB --- 25

01/20/07:
Ticker --- TotalQuantity
AAA --- 80
BBB --- 25

However, I would like to add a column to this query, which gives me the number of trades for each particular stock. For instance, the query result based on the sample data above should look like follows as per 01/20/07:

Ticker --- TotalQuantity --- NumberOfTrades
AAA --- 80 --- 2
BBB --- 25 --- 1

I have absolutely no idea how to achieve that. I would be very happy for any suggestions.

Best regards,
JapanFreak

 

[Kempes]

You might want to try adding a function using the DCount() formula.(As a new field within the query)

This allows you to specify what you are looking for, the criteria, and where to find it.

Look at the help option within access and it will explain this fully. Hopefully it should sort you out.

Regards
kempes

 

[JapanFreak]

Not yet there: Problem with DCount

Hi again,

thank you very much for your reply. Actually, I did not know the DCount function before. I have adjusted the SQL query as follows:

SELECT T.Ticker, DCount("Quantity","tblTransactions","Date<=#1/20/2007#") AS
NumberOfTrades
FROM tblTransactions AS T
WHERE T.Date<=#1/20/2007#
GROUP BY T.Ticker
HAVING sum(T.Qty)>0
ORDER BY T.Ticker;

However, so far it simply counts all the transactions recorded in the table "tblTransactions" (i.e. it does not differentiate by Ticker and does not care whether the Sum of the Quantities is 0 or >0). That is, using the sample data shown in my last posting this query results in the following output:

Ticker --- Quantity --- NumberOfTrades
AAA --- 80 --- 3
BBB --- 25 --- 3

From here the Access help section does not give any further hint with regards to how to adjust the criteria section of the DCount function. I would be happy for any advice that could lead to a solution. Once again thank you very much for your help.

Best regards,
JapanFreak

 

[Kempes]

Try this.

It now takes into account the Quantity, and the Ticker, and only adds the qty for these rows.

SELECT T.Ticker, Count(DCount("Quantity","tblTransactions","Quantity > 0" And "Date <=#20/01/2007#")) AS NumberOfTrades, Sum(T.Quantity) AS SumOfQuantity
FROM tblTransactions AS T
WHERE (((T.Date)<=#1/20/2007#) AND ((T.Quantity)>0))
GROUP BY T.Ticker
ORDER BY T.Ticker;

Regards
Kempes

 

[JapanFreak]

Problem solved.

Kempes,

thank you very much for your help. Your code works perfectly.

Best regards,
JapanFreak