Export 3 tables into 1 text file

[mayonace]

Hi,

I'm new here. Any advice would be greatly appreciated. I have 3 tables that I have to extract into one text file. The required format is:

Recordtype0 ID Name <--this is coming from table 1
Recordtype1 ID Phone Number <--this is coming from table 2
Recordtype1 ID Phone Number <--this is coming from table 2
Recordtype2 ID Address <--this is coming from table 3

Thanks in advance,

mayonace

 

[formerTexan]

Hi,

I'll make a few of assumptions: please advise if they are incorrect.

1. Your tables have a relationship;
2. You want to combine data from three tables into one field;
3. This field is in a table seperate from the other three.

You will need to create an APPEND query. Open up a new query into design view, add your source tables and fields. Then concatenate the source fields and finally on the menu, select Query > Append. A wizard will guide you through the rest of the process.

You should end up with a query like the following:

INSERT INTO tblDestination ( DestinationField )
SELECT [table1].[fldName] & [table2].[fldPhoneNumber] AS aAlias
FROM table1 INNER JOIN table2 ON table1.fldID = table2.fldID;

And a last note: Access reserves certain words (such as NAME) and symbols for specific purposes. Using them as field, variable, object or control names may cause problems, so it is advisable to avoid using them.

Cheers
Bill

 

[PHV]

Create a query joining the 3 tables and returning the expected result in the Query Design Window, save it and then play with the DoCmd.TransferText method.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[mayonace]

Hi,

I'm sorry if I was not clear in my first post. What I am trying to do is, export data from 3 tables into 1 text file. The tables have a relationship.

Recordtype0 ID PName <--this is coming from table 1
Recordtype1 ID Phone Number <--this is coming from table 2
Recordtype1 ID Phone Number <--this is coming from table 2
Recordtype2 ID Address <--this is coming from table 3
Recordtype0 ID PName
Recordtype1 ID Phone Number
Recordtype2 ID Address

The above sample is how it should look in the text file. I need this in order to inload it into another program and this program requires these specs.

Thanks for the advice on the "name" formerTexan.

TIA,
mayonace

 

[PHV]

And what about a report ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[me2you]

Hi Mayonace,

Try using a Union query and then exporting your result into the text file.

Peace,
me2you

 

[MakeItSo]

How about

SELECT table1.PName, table2.[Phone Number], table3.Address
FROM (Table1 INNER JOIN Table2 ON Table1.ID = Table2.ID) INNER JOIN Table3 ON Table1.ID = Table3.ID;

And then export this query to a text file.
If the query output does not match the format you need for the text file, you might need some code which loops through the result set and writes the data to the text file programmatically.

Hope that helps,
Andy

[blue]The last voice we will hear before the world explodes will be that of an expert saying:
"This is technically impossible!" - Sir Peter Ustinov[/blue]
andreas.galambos@bowneglobal.de
HP:

http://andygalambos.de