Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2005
    Location
    Minnesota
    Posts
    42

    Unanswered: relate fields below based on change in "FY20XX"

    I have a table that is over 2 million records froem a text file and I think, but not sure that the best way to relate fields is to create column and insert the relating paramters in a row in a new column A.

    I am able to sort the file so that everything Below the FY20XX relates to the column with FY in it. So when the FY20XX changes then everything below that row relates to the FY20XX row. Is there a macro or visual basic or function that can be used to populate the column so that I can relate the fields below it using the text from B to E?

    I'm thinking of creating blank column and maybe run update query to populate that column with the relating text.

    See Column A jpg attached. Example is in excel but i am working in access
    Attached Thumbnails Attached Thumbnails Relate Fields.JPG  

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    probably
    but as far as I can see there is no relationship in your data between columns b & E

    Access is a database, which is great for storing and manipulating data. databases do not have any concept of storing data in a specific order, you need to specify that order.

    what I suspect you are trying to do is create a hierarchy of your data. where the value of column B is not "FY20xx" then create a value in column A which is a combination of columns B,C,D & E from the previous row whose ColumnB value was "FY20xx"

    there is no easy way to do this. the data has been mangled to make sense, for presumably human beings (and possibly Accountants)

    SO what could you do
    1) go back to the original data source and try to get a feed that does meet your requirements. bear in mind its easier to do this at source than trying to manipulate data that has been played around with for Accountants

    2) accept that its going to take too long cost too much and so on to get the IT department off their backsides to do something. so what you coudl do is recreate the data as a hierarchy.

    so import the CSV file (Im guessing its a CSV file), although at 1million+ rows Im not to sure how you get it into Excel. take note of the column names that Access assigns to the columns as they are imported

    when you import the file Access will try to add an ID column, make certain you do. the id column will be a serial number making each row unique,m you will need that later on.

    lets assume that data is imported in a table called mydata I use 'my....' for examples here.

    next create 5 other tables
    table 1 contains the main data (columns B,C,D & E) + links to other tables (leave defining table 5 for now...)
    table 2 - 5 are essentially the same, they contain key and a description.
    for each of tables 2-5 you need
    Item, type text. the size needs to be big enough to accomodate the biggest (highest) number of characters.
    Description, type text, suggested size 30 characters
    make item the primary key

    lets pretend we are Accountants and give some boring names to things
    table2,lets call it FinancialYears
    table3 lets call it ValueTypes
    table4 lets call it FantasyIsland (well it does refer to budgets )
    table5 lets call it CostCentre
    so we will have a column called item in each table

    returning to table 1
    we need a column for each of the actual data columns in your spreadsheet (B,C,D & E), give them meaningfull names and datatypes
    also add 4 columns
    FYear
    ValueType
    FantasyIsland
    CostCenter
    all of which must be the same type and size as declared in tables 2..5

    then set up relationships between tables 2 to 5 and table 1. use referential integrity (RI) and make certain the link goes from table 2 (item) to table 1 (Fyear), repeat for tables 3,4 & 5 referrign to the relevaant columns in table 1

    OK so thats set up the structure, next we have to stuff the data into the tables
    to do that we need to write 4 queries
    insert into FYear (item) select distinct(columnb) from mytable where columnB like "FY*"

    do the same for the other tables 3...5
    except disctinct(columnb) beciomes columnc,columnd,columne but the where bit remains
    so your parent tables are now populated

    populating table1 is going to be a pig with 1 million rows.
    you can either do it using a series of queries OR a VBA process looping through recordsets.

    right now you've got enough to be going on with. the main data import requires more thinking about
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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