Joining tables help

[grewpar]

Hey there,



I have multiple .txt files with columns that are separated by the character "|". I know I can import these delimited and separate them so that they appear in the access database, but that is only a part of what I'm trying to do.

All of the text files share a "report key" and using this report key, I want to link the .txt files into one table so that I can see the information for each report key consecutively, rather than having to look it up multiple times in different tables...

So once again, I know that I can do this by simply importing one table, then adding the rest of my imported tables to the initial table I create....HOWEVER, in one of the .txt files, I have some rows that share the same report key because there are multiple entries for one report key.

My issue is that I need to match all that data, so it can be easily viewed and searched for, can somebody please help me? I've been trying to do this for a very long time.




So I have A.txt B.txt and C.txt

Lets say A gives me an event that occurred, B gives me what it caused, and C gives me why it happened....
For C, I have multiple reasons for why the report key in A and B happened.

How can I make it so that I can see one report key, then see the information A,B, and C all at one place?

[ranman256]

Can you import the data,
then run an update query that updates the Table with the keys?

[grewpar]

I'm not exactly sure how to do that. I'll do some googling, but if you could provide a quick step by step explanation, or website link to an explanation, that would be great

[grewpar]

Also, I don't need to update the table with the keys, but rather match the info on each table with the corresponding keys

[June7]

If you want to match the tables then you do need the keys.

Sounds like A.txt and B.txt are 1-to-1 relationship and could be one table. C.txt should be a related 'many' table. Did you try doing a query that joins all 3?

You can link or import the txt files. Is this a one-time import? Or do you receive the txt files periodically with new data?

[grewpar]

If you want to match the tables then you do need the keys.

Sounds like A.txt and B.txt are 1-to-1 relationship and could be one table. C.txt should be a related 'many' table. Did you try doing a query that joins all 3?

You can link or import the txt files. Is this a one-time import? Or do you receive the txt files periodically with new data?

I didn't try any queries because it isn't exactly a 1 to -1 relationship...It's more of a 1 to 3 relationship.

Lets say there are just two .txt files. On A.txt, I have one report key telling me that an event occured, on B.txt, I have multiple causes for each event in A.txt, thus more rows as well. How do I join two tables like this, that is if A.txt has one report key, and B.txt, has the same report keys, but duplicates as well.

[June7]

That's basically what I said. A.txt and B.txt would really be one table for the 1 side of relationship. And C.txt would be related 'many' side.

Just join them on the common report key field. The AB.txt 'header' info will show with each detail record from C.txt. Then build a report object. Set report to not repeat the 'header' info from AB.txt. Or use report Sorting & Grouping features.

[grewpar]

That's basically what I said. A.txt and B.txt would really be one table for the 1 side of relationship. And C.txt would be related 'many' side.

Just join them on the common report key field. The AB.txt 'header' info will show with each detail record from C.txt. Then build a report object. Set report to not repeat the 'header' info from AB.txt. Or use report Sorting & Grouping features.

Thanks, I will try this ASAP!