Building a database, relationships and tables from a text file??

[GeekIslands]

Help!
I would like to make sense of some data getting dumped from our MRP system..,I’m new to this Access lark (and to this forum) but I’m hoping it will help me do what I want and some kind Access guru will guide me to the light that I know is at end of yonder tunnel..

Are you sitting comfortably….Here’s the story so far…

A text file is getting dumped, daily, containing product data. This currently contains about 18000 rows

The file is comma delimited currently containing 3 fields per row
Part ID,Proj,Parent

Part ID is a unique single alphanumeric identifier
Proj contains one or many entries delimited by a ^
Parent contains no, one or many entries delimited by a ^

A Part will be assigned to at least 1 Project (proj) but no known maximum
A Part could have no, one, or many Parent Parts (which also has a part ID)

Example Original .txt file

A,2001^2678,Z
B,6785,F
C,2078^7899^6733
D,2996,K^T
E,2113^3662
F,6671^6785^2888^2768

I want to be able to dynamically query this data to generate reports e.g
Show all parts on project 6785 (should return B and F)
Show assembly structure (i.e. build parent child links)

The file is refreshed daily so have created a linked table directly to the text file which looks something like this:

PartID    Proj                           Parent
A         2001^2678                      Z
B         6785                           F 
C         2078^7899^6733
D         2996                           K^T
E         2113^3662 
F         6671^6785^2888^2768

All is well so far and any edits or refresh to the source data dynamically updates in the access table.

Where do I start?? How can I dynamically split the data up into multiple records??
Using the example above is it possible to query partID F and get the Projects listed as 4 rows?

6671
6785
2888
2768

I have created a query to count the number of projects/parents for each PartID? what now? Do I have to resort to some VB code? If I then want to do parent child relationships do I have to create a selfjoin (whatever that is??)
Too many questions…..too long a post…too late at night….

 

[The_Doc_Man]

This is where you need to write some VBA code to do some elementary parsing.

The things you need to know include the Access Help for the InStr and Mid$ functions, which in combination would let you split out the parts of your text file. You might want to read the file using InputLine to get a single string, then repeatedly use InStr to find the comma delimiters and use Mid (knowing the position of the current and previoius markers) to split apart the three possible string segments. Then use the same concept where applicable to split apart the indefinite number of sub-segments to each segment based on the position of the hat (^).

Now as to how you store this... unless there is something missing from your description, I don't see ANYTHING that could even remotely resemble a unique ID, and your description of that parent field isn't helping either. With 18,000 records, your A-B-C...-X-Y-Z part numbers will probably get re-used as many as a few thousand times each, so that won't be unique. Where I'm having trouble is your business model that relates to these items.

You need to better identify the dependent and independent values here. What fields depends on what other fields? That dependency is crucial for proper storage. At the moment, I don't see enough information to make the call so I have to stop here.

 

[dsigner1]

You only have 26 parts max so how do they relate to 18000 lines?
Manipulating the data looks easy. Making it into sense now that's another story

 

[GeekIslands]

Sorry guys, I was trying to simpify the question with example of partid reduced to a single character - In reality it's usually an 8 character field like mab00456 sometims 11 characters..no duplicates...I didn't think this would matter as conceptually I am still faced with the same questions and was testing on a small amount of data?

The parent field is used to build assembly structure..

i.e a partid could have multiple parents as it could exist in multiple assemblies.

a parent is a partid
multiple parts can exist in a parent
a parent can have another parent - subassembly in an assembly

will vb code in access run if the database is used as a backend for a web application written in asp? I have used ado to query by sql and write back to web page. what would trigger the event to execute the vb code as there will be no access forms??

Thanks...