relate fields below based on change in "FY20XX"

[John1Chr]

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.

A B C D E
"FY2012""Load_Actual""Prelim""ZA70010" "FY2012" "Load_Actual" "Prelim" "ZA70010"
"FY2012""Load_Actual""Prelim""ZA70010" "APZ" #Mi 1892.517 #Mi
"FY2012""Load_Actual""Prelim""ZA70010" "RFZOUT" #Mi 292.72942 #Mi
"FY2012""Load_Actual""Prelim""ZA70010" "51000" #Mi 1214.09399 #Mi
"FY2012""Load_Actual""Prelim""ZA70010" "51030" #Mi 124.18024 #Mi
"FY2012""Load_Actual""Prelim""ZA70010" "51050" #Mi 0.19638 #Mi
"FY2012""Load_Actual""Prelim""ZA70010" "FTE" #Mi 24.57461 #Mi
"FY2012""Load_Actual""Prelim""ZA70010" "Account" 7 7 7
"FY2013""Load_Actual""Final""ZA70010" "FY2013" "Load_Actual" "Final" "ZA70010"
"FY2013""Load_Actual""Final""ZA70010" "RFZIN" #Mi 293.15942
"FY2013""Load_Actual""Final""ZA70010" "APZ" #Mi 1892.517
"FY2013""Load_Actual""Final""ZA70010" "51000" #Mi 1603.24352
"FY2013""Load_Actual""Final""ZA70010" "CNL" #Mi 0.2114
"FY2013""Load_Actual""Final""ZA70010" "FTE" #Mi 17.2
"FY2013""Load_Budget""Prelim""ZA70010" "FY2013" "Load_Budget" "Prelim" "ZA70010"
"FY2013""Load_Budget""Prelim""ZA70010" "RFZIN" #Mi 292.72942 #Mi
"FY2013""Load_Budget""Prelim""ZA70010" "APZ" #Mi 1892.517 #Mi
"FY2013""Load_Budget""Prelim""ZA70010" "51000" #Mi 1301.70703 #Mi
"FY2013""Load_Budget""Prelim""ZA70010" "51030" #Mi 119.91203 #Mi

 

[SkipVought]

Hi,

A B C D E

Is this Excel?

In Access You'ld have headings, yes?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[John1Chr]

Yes, that is correct. It is in access but I just put it in excel to try to explain what i was facing. When B changes FY those records below it are related to that header. Basically, file is sorted so that headers have related data below but those rows do not have relational record.

 

[SkipVought]

Well what are the field names? Or are you looking for an Excel solution?

 

[John1Chr]

Hmmmm...maybe...2 million records though. I went into text file and deleted/find-replaced unnecessary spaces characters. Huge file...got it down from 88MB to 77MB. I have it set up / linked in access in format above.

Is there way to handle such large file in excel? Might be slick to see what function or code you have to solve in excel and see it work and maybe break it up.

Column A field I want to use to relate records below
Column B Fiscal Year
Column C Version
Column D Scenerio
Column E Dept

Thoughts?

 

[SkipVought]

Column A field I want to use to relate records below

Is your data not yet in Access? It seems that you have no field name for the first field.

You seem to persist on what appears to be an Excel solution, when your record count exceeds Excel's row limit!

 

[John1Chr]

Yes, I did not make it clear. I am working in access off of a text file - space is used to separate fields. I am linking to the text file and I have a query that I am running against the linked text file through access. I was thinking of using first field in query to relate data but am not sure if this is best. I could try to import file into excel but I have but too many records. I could import into access but haven't yet. I am just linking to the text file within access and looking at it. I copied the results of the query into excel and that is how i created the example above.

 

[SkipVought]

What do you mean by, "relate records below?"

Please use the data in the example you previously posted, to illustrate what you intend, along with a detailed explanation.

 

[John1Chr]

In nutshell want to relate a header to detail underneath it.

Header
FY2012 Load_Actual Prelim ZA70010

Detail
APZ #Mi 1892.517 #Mi
RFZOUT #Mi 292.72942 #Mi
51000 #Mi 1214.09399 #Mi
51030 #Mi 124.18024 #Mi
51050 #Mi 0.19638 #Mi
FTE #Mi 24.57461 #Mi

This info is related to the header and when next fiscal year comes up the info below it is related. The header columns are as I listed above. The detail actually is diff column type fields. So if I had a field in column A that relates the header to the detail then Column B detail is Account, Column C is Beginning balance, Column D is July and Column E is quarter 1. I only care about Column B and D because relevant data is entered in July only. If i can relate the row data to the header then I am good to go and I can worry about labeling columns later, I believe. Difficult to explain but 10 times harder for someone to understand what I am explaining.

I wanted new extract to work with but was told this is all we have to work with.

 

[SkipVought]

Ohhhhh. You're referring to a REPORT.

You should probably post in forum703 to get a better response.