Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2004
    Posts
    28

    Arrow Unanswered: Resolved

    i have a table ..and having hard time ! because i am transferring file of text data into ....Database table...which was workign fine! but now i have a situation in which i have "-" symbol there instead of zero in some places....how can i make table recognize that its a zero....
    point noted*all my table is also on Total row in query design
    Last edited by njptown20; 10-18-04 at 12:45. Reason: RESOLVED

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by njptown20
    i have a table ..and having hard time ! because i am transferring file of text data into ....Database table...which was workign fine! but now i have a situation in which i have "-" symbol there instead of zero in some places....how can i make table recognize that its a zero....
    point noted*all my table is also on Total row in query design
    Let's see how you're currently processing the data ...
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Sep 2004
    Posts
    28

    here is document of whats going on

    • File is imported to Access.
    • An import specification is saved in “mlpro Import Specification”.
    • File imported to table name “temp_mlprime”.
    • “Transfer Text MLPRIME” macro is created with this specification to transfer the text.
    • In query “temp_mlprime” Query” is created, which is going to eliminate empty spaces and character.
    • Append query is created “MLPRIMEAppend”
    • “MLPRIMEAppend” will append data in new table “MLPRIME”.
    • “MLPRIME” has both trades and Transaction in its table.
    • Since we need two different tables of Trade and Transaction from Trade Volumes report.
    • “trade Query” is created from “temp_mlprimce”.
    • “trade Query” is just going to have trade fields with date.
    • “trade Append” is created where “trade Query” is appended in new table “Trade”.
    • Just like this procedure “Transaction” table is created also.
    • “MLPRIME File Loader” form is created where you can upload these text files and export it in any table.
    • Table is then exported into Excel in four different excel files.
    ADIL >>>>> NJ
    21 ....NY/NJ

  4. #4
    Join Date
    Sep 2004
    Posts
    28

    Raw Data And Table

    <------------------------- Trades ---------------------------> <------------------------- Trades --------------------------->
    Trade Date Trade Date Trade Date Trade Date Date Date Trade Date Trade Date
    Equities Options Bonds Total Equities Options Bonds Total
    10/11/2004 DPB* 492 - 3 495 38 3 - 41
    10/11/2004 GPB "1,009" 231 16 "1,256" "1,083" 231 33 "1,347"
    10/11/2004 IPB "14,171" 12 366 "14,549" "14,680" 12 405 "15,097"

    *Note: DPB Transaction date is based on settlement date.
    IPB and GPB is based on trade date.
    ------------------------------------------------------------------------------------------------------------------------------------------------------

    Date ML PRIME SumOFTR_EQ SumOfTR_OP SumOfTR_BD SumOfTR_TL SumOfTN_EQ SumOfTN_OP SumOfTN_BD SumOfTN_TL
    10/12/2004 Trade Volumes 20570 299 426 21295 22832 310 490 23632
    10/4/2004 Trade Volumes 27023 411 416 27850 28309 410 504 29223
    10/5/2004 Trade Volumes 26035 132 440 26607 29187 138 555 29880
    ADIL >>>>> NJ
    21 ....NY/NJ

  5. #5
    Join Date
    Sep 2004
    Posts
    28

    Arrow

    SELECT temp_mlprime.Date, "Trade Volumes" AS [ML PRIME], Sum(temp_mlprime.TR_EQ) AS SumOfTR_EQ, Sum(temp_mlprime.TR_OP) AS SumOfTR_OP, Sum(temp_mlprime.TR_BD) AS SumOfTR_BD, Sum(temp_mlprime.TR_TL) AS SumOfTR_TL, Sum(temp_mlprime.TN_EQ) AS SumOfTN_EQ, Sum(temp_mlprime.TN_OP) AS SumOfTN_OP, Sum(temp_mlprime.TN_BD) AS SumOfTN_BD, Sum(temp_mlprime.TN_TL) AS SumOfTN_TL
    FROM temp_mlprime
    GROUP BY temp_mlprime.Date
    HAVING (((temp_mlprime.Date)<>" "));
    ADIL >>>>> NJ
    21 ....NY/NJ

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •