Best software to build a database

[andyfuller]

I seem to have finally figured out how to do the bit I was stuck on so now it seems I have completed the qryTrades.

In the Query Field Name I put in the following:

OpeningOrClosing: IIf([TradeType]=DLookUp("TradeType","qryOpeningTradeForATransaction","TransactionsInACompanyID=" & [TransactionsInACompanyID]),"Opening","Closing")

It seems to be working

qryTrades_Image_3.png

qryTrades_Image_4.png

I will move onto the Query qryTransactions tomorrow.

[Ethanol]

For now I have returned textual information i.e. "Long" and "Short" rather than boolean data and then using a lookup table, tblTradeType, as I find it easier to understand the textual information and am not sure of the advantage of returning boolean data as opposed to textual information.

It's more for database conformity. Data stored within a database generally isn't intended to be user-readable - typically that's the job of the application (e.g. the GUI within Access). Because booleans/integers are more natural to a database, they are processed more efficiently than strings. Also, if you were to build a query on top of the one which returns "Long"/"Short" strings, and the underlying query were to change, then the query on top might fail or act strangely. However, in this case, if you're happy with it working this way, then don't worry too much about it.

I took the TransactionInACompanyID from the Table TransactionsInACompany rather than the Trades Table. Though I am not sure if this is the right thing to do or not, or if it matters doing this as opposed to taking TransactionInACompanyID from the Trades Table as doing that also seems to return the same results.?

It should be returning the same results, as you're merely joining by this field. You should be able to remove the TransactionsInACompany table from this query, since it's kind of redundant.

I seem to have finally figured out how to do the bit I was stuck on so now it seems I have completed the qryTrades.

In the Query Field Name I put in the following:

OpeningOrClosing: IIf([TradeType]=DLookUp("TradeType","qryOpeningTradeForATransaction","TransactionsInACompanyID=" & [TransactionsInACompanyID]),"Opening","Closing")

It seems to be working

It's a slightly different implementation to what I had envisaged, but it performs the same function, so you're sorted!

I will move onto the Query qryTransactions tomorrow.

How is today's development going?

[andyfuller]

I will move onto the Query qryTransactions tomorrow.

How is today's development going?

You type faster than me! I was busy typing away to find you had replied lol. So here is what I have got on with today (though it doesn't feel like I have made much progress, especially given how long I have spent on this, though I think I am beginning to understand Access more, but can also see why people stick with Excel

qryTrades:

TradeID*
TransactionsInACompanyID*
Quantity*
Price*
TradeDate*
Long (1)
OpeningTrade (2)

*These are taken straight from the underlying Trades table.

(2) A boolean value for whether the trade is an opening or closing trade. The wizardry for this comes from self-linking this table back to itself in the query, by matching (joining by) the TransactionInACompanyID, and taking the minimum TradeID (the original opening trade). If the signs of the two Quantity fields match, it is an opening trade. Else, if the signs differ, it's a closing trade.

I have been going back over what I did yesterday, today. It seems that what I constructed yesterday, although it worked, was done slightly 'incorrectly' according to a poster on an Access Forum.

In order to establish if a trade was an OpeningTrade or a ClosingTrade I was using three further separate queries that I had constructed to the two you said to construct; qryTrades and qryTransactions.

I have got this down to one Query, qryOpeningTradeForATransaction, from the 3 I was using yesterday by inserting a Sub Query within qryOpeningTradeForATransaction (I guess you could still say I was using two but this is the way I was told I should be doing it, though I can't say I really understand the benefit as this is what I was in effect doing but using three separate queries, perhaps it is more efficient and thus quicker when there is lots of data in the Database?)(also I am still getting my head fully around Sub Queries )

The code for the Query, qryOpeningTradeForATransaction, is:

SELECT Subq.TransactionsInACompanyID, Subq.MinOfTradesID, qryTrades.TradeType
FROM (SELECT TransactionsInACompanyID, Min(TradesID) AS MinOfTradesID
FROM tblTrades
GROUP BY TransactionsInACompanyID)
AS Subq INNER JOIN qryTrades ON Subq.MinOfTradesID = qryTrades.TradesID;

qryOpeningTradeOfEachTransaction_1.png

qryOpeningTradeOfEachTransaction_2.png

And then within qryTrades I have the Field below which is the same as what I posted yesterday just with the updated Query name:

OpeningOrClosing: IIf([TradeType]=DLookUp("TradeType","qryOpeningTradeOfEachTransaction","TransactionsInACompanyID=" & [TransactionsInACompanyID]),"Opening","Closing")

But this is still a Query in addition to the two queries you said I should construct, qryTrades and qryTransactions. So am I still doing this incorrectly and should I not be creating this additional query at all, but instead be doing it from within qryTrades and/or qry Transactions?

I have only just begun the qryTransactions but can also already see a need for me to construct additional queries:

Eg:

TransactionActive - So I can sum the Quantity for each Transaction, calling it qrySumOfTransactionQuantity, then I can use an IIf statement within qryTransactions to say if the SumOfQuantity from qrySumOfTransactionQuantity isn't '0' return in qryTransactions 'Open', if it is return 'Closed'

ClosingTradeID - This would require a query the same as qryOpeningTradeForATransaction but rather than getting the MinTradeID getting the MaxTradeID and combining it with TransactionActive, where by if TransactionActive is 'Open' return Null but when it is 'Closed' return the MaxTradeID

OpeningTransactionDate - This can be taken from the Query qryOpeningTradeForATransaction by adding the TradeDate to the Query

So is there something I am missing and not doing which would allow me to get the information without creating these extra queries?

Thanks for your advice as ever!

[andyfuller]

It's more for database conformity. Data stored within a database generally isn't intended to be user-readable - typically that's the job of the application (e.g. the GUI within Access). Because booleans/integers are more natural to a database, they are processed more efficiently than strings. Also, if you were to build a query on top of the one which returns "Long"/"Short" strings, and the underlying query were to change, then the query on top might fail or act strangely. However, in this case, if you're happy with it working this way, then don't worry too much about it.

Thanks for the explanation, I get the logic behind this now and can see why I should implement it so it is something I shall go back and do.