Query to create additional field in table

[leungyen]

Hi there, could you please help me to figure out how to create additional field from the table.




1. I exported from text file to the access table

It looks pretty much like this in the table

ID	Field1	Field2	Field3
1		random	random
2	random	random
3	CustID	123A
4	CName	Mr.xyx
5	random	random	random
6	01/01/15	Order	$150
7	01/02/15	Order	$200
8	random	random
9		random	random
10	CustID	234B
11	CName	Mr.abc
12	01/01/15	Order	$125
13	01/02/15	Order	$225

2. What is the best way to get the result like the table below??

CustID	Field1	Field2	Field3
123A	01/01/15	Order	$150
123A	01/02/15	Order	$200
234B	01/01/15	Order	$125
234B	01/02/15	Order	$225

Thanks in advance!!

Y

[orange]

??? I have no idea what you are trying to do.
Your imported data seems to be of unknown format/structure.

You need to structure your original text file into a meaningful, consistent list/format.

I think you should post the original text file to show users what you are dealing with.

[June7]

Is this a recurring process?

Since the text file is not structured where each line represents a record, would require VBA code that opens and manipulates the text file as an object to import data into correct fields. A regular topic in forum. Search on keywords "read write text file".

[leungyen]

Hi Orange, June, Thank you for your time to take a look at my problem.
My apology that I did not make my question’s clear. Let me try again.
This is the recurring process and has lots of data almost a million.
I received text file and after imported into access it looks pretty much like the first table.
I need the result to be like the second table, where each order, date, and amount purchased has the information of the customer (CustID)
I just figured out how to get the result that I want. I have to do query over query but it works. This might be not the best way to do it. If someone can share with me how to do it more efficiently I will really appreciate it.


Query1
SELECT Tbl.ID, (SELECT Field2 FROM [tbl] WHERE ID=T.tmpID) AS CustID, T.Field1, T.Field2, T.Field3
FROM (SELECT tbl.*, Nz((SELECT MAX(ID) FROM tbl AS tmp WHERE ID<[tbl].ID AND Field1="CustID"),[ID]) AS tmpID FROM tbl) AS T;

Query2
SELECT Query1.CustID, Query1.Field1, Query1.Field2, Query1.Field3
FROM Query1
WHERE (((Query1.Field2)="Order"));

[June7]

But that leaves the data in table in improper structure. You have dealt with that in query and if you want to continue to, that is a choice.

[leungyen]

Hi June,

I will take a look at your suggestion. I might need helps again letter. Thank you for your direction!

Y