Trading journal db

[Captain]

Hi,

This is my first venture to Access, and although my sql is average my vb is non existent, so pls bear with me....

I wanted to create a trading journal where i can record my trades by uploading them via the broker statement (csv) and then attach my screen prints. I have attached a copy of an demo file from the broker, so you can see the problems i have.

Having trawled the forums for the past 2 weeks and tried lots of different methods, i think i now have a good idea of what to do, but i just need a few pointers to get me over the line.

My plan is to load the file to a temp table first so that i can then import the relevant data to the correct table. (i only need one table at this stage)

I have managed to create a form that has buttons and events to load the temp table (DoCmd.Transfer Text).
Then I need to update my table from here and then delete the temp table (the delete i can do)

I was hoping to use DoCmd.RunSQL, but i found i couldn't use if statements, so this is where i am stuck.

It seems i might need more than one sql to update, as different transaction types mean different fields are populated.(i found if i didn't populate all fields it ignored those records).
So one sql for Interest transactions.
And one for trades.

Does that sound right?

Also with the trades, the trade details are in several rows in the csv, but need to populate one record in the table where they are joined by the TranLink field.
eg the ticket number in row7 = tranlink in rows 8/9/10/11.
I guess i can achieve this by referring to my temp table twice and joining ticket and tranlink?

so how to combine these two sql strings in vb so they run and order by the ticket number upon insert?
I tried to declare both my sql statements and writing an if statement, but that's when i got runtime 3129 errors.



thanks

Simon

 

[DavidAtWork]

There's quite a bit of work involved here and would require quite a bit of vba to manage the csv datafile.
A few general points touching of what you've outlined so far:
I agree with your idea to import into a temp table first as a Step 1, although I wouldn't delete it, just clear it down each time before the import, that way you can set some Import Specifications and get all the data in with the correct data types. The date field in your csv looks iffy and would need some string functions to extract a date from it in Step 2. The combining of multiple records (with same Tran Link) could also be done in Step 2 using some vba Recordset code.
The sql updating using IF statements, you may be able to solve that by using IF statements against variable values and run different sql update strings according to results of the IF
Hope that's given you some ideas and also an idea of just how much work is involved here
David

 

[Captain]

thanks Davidatwork,

i like the idea of not deleting the temp table.
with the recordset idea though, is there not a way of achieving this via SQL? which means i can utilise the knowledge i have? otherwise i need to start learning all about recordsets. i started googling, and in it's simplest form it looks fine, but to achieve what i need, it looks like it can get very complex.

what i was trying to do was something like...

Dim strSQL_INT As String
Dim strSQL_TRD As String

strSQL_INT = "INSERT INTO ...;"
strSQL_TRD = "INSERT INTO ....;"

If [OandaTemp].Transaction='Interest'
then strSQL_INT Else strSQL_TRD
End If


(this wasn't my full code, i have been playing with lots of options and going round in circles)

 

[Captain]

here was my attempt with a recordset just to get the interest rows to work.
i get an error message on the select sql saying there are too few parameters configured?

i thought maybe if i split it up so open a recordset for the Interest first then do one for the trades hence rstInt and rstTRD

Private Sub Command22_Click()
Dim strSQL_INT As String
Dim strSQL_TRD As String
Dim strSQL_MAIN As String
Dim db As Database
Dim rstMAIN As Recordset
Dim rstInt As Recordset
Dim rstTRD As Recordset
Set db = CurrentDb()
Set rstInt = db.OpenRecordset("SELECT * FROM OandaTemp WHERE OandaTemp.Transactions = 'Interest';", dbOpenDynaset)
Set rstMAIN = db.OpenRecordset("OandaMain")
Do While Not rstInt.EOF
rstMAIN.AddNew
rstMAIN![Ticket] = rstInt![Ticket]
rstMAIN![OpenDate] = rstInt![Date]
rstMAIN![Type] = rstInt![Transaction]
rstMAIN![Interest] = rstInt![Interest]
rstMAIN![Balance] = rstInt![Balance]
rstMAIN.Update
rstMAIN.MoveNext
Loop

rstMAIN.Close
rstInt.Close

End Sub

 

[DavidAtWork]

The only reason I suggest using recordsets is because in cases where you have multiple records with the same tran link, you can query those and manipulate the records one by one before writing the result (INSERT INTO) to the main table.
BTW, opening recordsets, I think you only need to state:
Set rstInt = db.OpenRecordset("SELECT * FROM OandaTemp WHERE OandaTemp.Transactions = 'Interest'")
The dbOpenDynaset is an optional argument
Looking at your data, I would do this in 2 steps, those with a tran link and those without which appear to be all Interest transactions.
To handle those with a tran link, I would use a outer loop recordset which selects all the set outerRS = db.OpenRecordset("SELECT temp.[tran link] FROM temp GROUPBY [tran link]")
Do While NOT outerRS.EOF
varTranLink=outerRS.Fields([tran link])
set innerRS = db.OpenRecordset("SELECT * FROM temp WHERE [tran link] = " & varTranLink & ")
Do While NOT innerRS.EOF
here you need to do any manipulation before any Docmd.runsql("INSERT INTO sql")
innerRS.movenext
Loop
outerRS.movenext
Loop

Do you see what's happening, I hope this gives you an idea
David