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
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