Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    URGENT HELP (Access2000)

    Hi guys,
    Have a look at the attachment, Im importing the data in the excel sheet into an access table to do calculations and to store the data in another table after the calculations. As you can see in the attachment, columns A:E are shared between column F:M.

    When I do the calculations I need to separate the data for ex
    Rec1
    FX:USD, type: div_inc, date: 09-Nov-01, tkr: dge ln, Comment: , Fund: Deaville, Quantity: 6.91
    Rec2
    FX:GBP, type: div_inc, date: 09-Nov-01, tkr: dge ln, Comment: , Fund: QP01, Quantity: 3.33
    and so on..
    Can you see the pattern? The column name F:M should fall under a column called Fund in access and the value in the feilds should fall under another column called quantity.
    The problem is, I don't know how I could do this, Can someone please tell me how???
    <img src=/S/help.gif border=0 alt=help width=23 height=15>
    Attached Files Attached Files

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: URGENT HELP (Access2000)

    Hi,
    You've got two choices that I can see:
    1. Write code to do this, which shouldn't be a complete nightmare but won't be overly simple.
    2. As far as I can see, this was produced via some sort of crosstab query (or pivot table). Any chance you can get hold of the raw data and use that instead?
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: URGENT HELP (Access2000)

    That is the raw data rory.
    You know as well as me that I am no good at VB, any other suggestions? if no PLEASE tell me where to look for similar codes..
    <img src=/S/bow.gif border=0 alt=bow width=15 height=15>

  4. #4
    Lounger
    Join Date
    Jan 2002
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: URGENT HELP (Access2000)

    I don't know if you have do it just once or it is a routine data import.
    if its not routine then you can do something like this
    give all the columns some appropriate name and Import it to access.
    in access you can make some queries to separate the data as required.

  5. #5
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: URGENT HELP (Access2000)

    Thank you.
    Its a routine data but if I could separate it using queries then I'd do that and I open the queries using code everytime I import data.
    That should work shouldn't it?
    I'll give it a try.
    <img src=/S/bow.gif border=0 alt=bow width=15 height=15>

Posting Permissions

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